Solved

Select titles with newest date when grouping reults by author id

Posted on 2013-06-27
39
271 Views
Last Modified: 2013-06-27
Hello Experts,

I have a a table named titles. Each title can have multiple authors so I store the author ids and the corresponding title ids in look up table: lu_titles_authors.

I  want to display a list of the newest titles on a webpage but also make sure that each author gets shown only once in this row of titles and MOST IMPORTANTLY that the selected titles for each author is the title with the highest/newest date.

I started with:

SELECT * FROM titels INNER JOIN lu_titles_authors ON T_Id = LUTITAU_T_Id ORDER BY T_Date DESC

Open in new window


This gets me half way.

Then I made in to:
SELECT * FROM titels INNER JOIN lu_titles_authors ON T_Id = LUTITAU_T_Id GROUP BY LUTITAU_A_Id ORDER BY T_Date DESC

Open in new window


This does seem to filter out duplicate authors in the list of results, but the chosen titles that remain are not the latest titles of this author...

Can someone tell me how I need to change the query in order to achieve this?

If anything is not clear enough let me know and I will try to explain it better...

Summarized
I want to query one title of each author and the title needs to be the one with the newest/highest date...

Thank you very much for any replies!

wkr
RV
0
Comment
Question by:Witheet
  • 18
  • 15
  • 5
  • +1
39 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39280542
What fields are interested in in your select clause?

You are going to do something like this:
SELECT * 
  FROM titels 
       INNER JOIN lu_titles_authors 
    ON T_Id = LUTITAU_T_Id 
 GROUP BY LUTITAU_A_Id 
 HAVING T_Date = MAX(T_Date)
 ORDER BY T_Date DESC

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39280555
what are the fields of titles table?
0
 

Author Comment

by:Witheet
ID: 39280569
Tyvm for your reply.

The fields i am interested in are:
T_Cover,T_Id,T_Date,LUTITAU_A_Id,LUTITAU_T_Id

I will give your example a go and get back to you.

Again thanks for the reply!

wkr
rv
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280576
author comments from previous question
database is MySQL

{edited}
0
 

Author Comment

by:Witheet
ID: 39280587
I tried the example... It does put out results in a new way but still not showing titles with highest dates for each author. I have works dating yesterday by one of the authors that is returned but it shows an older title with this author... And other authors do not come back at all while they have titles that are 3 days old... I checked the look up tables to see if the corresponding ids where really there and that is the case...

SELECT T_Cover,T_Id,T_Date,LUTITAU_A_Id,LUTITAU_T_Id FROM titels INNER JOIN lu_tit_au ON T_Id = LUTITAU_T_Id GROUP BY LUTITAU_A_Id HAVING T_Date = MAX(T_Date) ORDER BY T_Datum DESC

Open in new window


EDITED
As stated... when I use HAVING T_Date = MAX(T_Date) it does not show all the authors anymore. There should be 57 results and there are only 25 now. When I use MIN(T_Date) it does show all 57 results but also not the titles with the highest dates (which makes sense since it uses MIN I guess).
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39280607
Should you not use T_Datum instead of T_Date?
0
 

Author Comment

by:Witheet
ID: 39280613
@Louis01. I do actually but in the question I thought I'd best translate it to english inside the question for readability...

@PortletPaul TY for the addition!
I actually tried to achieve this current question by trying to build the query like you provided me with my previous problem but that did not fly ...

TYVM!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280625
I am somewhat lost by the absence of table definitions, is T_Datum different to T_Date?
I assume "T_" indicates its from titels, "LU..." from lu_tit_au
anyway: does this help at all?
SELECT
       T.T_Cover
     , T.T_Id
     , T.T_Date
     , L.LUTITAU_A_Id
     , L.LUTITAU_T_Id
FROM (
        SELECT
              T_Cover
            , T_Id
            , T_Datum
            , max(T_Date) as t_Date
        FROM titels
        GROUP BY
              T_Cover
            , T_Id
            , T_Datum
      ) as T
INNER JOIN lu_tit_au AS L ON T.T_Id = L.LUTITAU_T_Id
ORDER BY T.T_Datum DESC

Open in new window

although your field naming conventions probably indicate table source, you really should prefix each field by table or table alias.
{+edit - sorry missed typed something}
0
 

Author Comment

by:Witheet
ID: 39280628
Since it is a mysql database could it be that max() does not work as is supposed because a date is not numerical? I read somewhere that MAX() returns the row that would be last if the data were sorted by that column if used on textual columns... Does this apply to date types aswell maybe?

Looking for ways to keep you engaged but I have to admit that I am pretty stuck as to think of a next step te bring me closer to the solution I am looking for...

Is there any other info I can provide to make it easier to help with this? PLeas let me know and I will comply...

Again tyvm for your help.

wkr
rv
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280629
could I suggest you provide
the table definitions
and some sample data
and expected results

these 3 things will bring the question to a solution quickly (especially the data)
0
 

Author Comment

by:Witheet
ID: 39280631
@Portletpaul... I will give this a try and get back asap...

Regarding the naming conventions... This is indeed named this way so I do not have to do what you say I still should do even though I name it like this?
Is there a quick answer as to why? Don't want to take up to much of your time...

TYVM!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280639
max(date_field) will produce the maximum date :) no problem

in fact date field are actually stored as numeric (don't know the full detail of MySQL dates) but fr example a date field in Oracle is a group of 7 integers, in SQL Server a datetime field is 2 integers.

dates are "numeric" as far as the dbms is concerned (unless you convert then to stiongs of course)

that's a distraction:
fastest path to resolution is sample data
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280652
>>This is indeed named this way so I do not have to do what you say I still should do even though I name it like this?
If it is only ever you who maintains this code then perhaps you can get by
however, it is simply "best practice" to always indicate where a field comes from

IN PARTICULAR this should included in JOINS! (IMHO)

also: what happens if a query involves 2 or more lookup tables?
It would be confusing wouldn't it?

aliases make life easier - a lot.
0
 

Author Comment

by:Witheet
ID: 39280690
SELECT
       T.T_Cover
     , T.T_Id
     , T.T_Date
     , L.LUTITAU_A_Id
     , L.LUTITAU_T_Id
FROM (
        SELECT
              T_Cover
            , T_Id
            , T_Datum
            , max(T_Date) as t_Date
        FROM titels
        GROUP BY
              T_Cover
            , T_Id
            , T_Datum
      ) as T
INNER JOIN lu_tit_au AS L ON T.T_Id = L.LUTITAU_T_Id
ORDER BY T.T_Datum DESC

Open in new window


This does get the titles sorted by date but the result list still contains double authors... each author should have only 1 work showing...

The results from above query are shown in the attached image... This is almost like I need it... The authors should only show once though and the title shown with it should be - like now-  the latest on based on the value of T_Datum field...

Does this provide better info regarding the issue? Need more?
query-result-1.jpg
0
 

Author Comment

by:Witheet
ID: 39280699
also: what happens if a query involves 2 or more lookup tables?
It would be confusing wouldn't it?


VERY MUCH SO... Normally I also need the authors table to display the authors name so I inner join that aswell and that does get cluttered real quick... I will make sure to check out using aliases and follow your advice regarding naming conventions... TY for the info!!!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280743
PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE

supply sample data from each of these tables

& use text in preference to images by the way :(

anyway, try this, there may be scope to improve efficiency, but lets see if it gives the wanted result first
SELECT
       T.T_Cover
     , T.T_Id
     , L.LUTITAU_A_Id
     , max(T.T_Date) as T_Date
     , max(T.T_Datum) as T_Datum
FROM (
        SELECT
              T_Cover
            , T_Id
            , T_Datum
            , max(T_Date) as t_Date
        FROM titels
        GROUP BY
              T_Cover
            , T_Id
            , T_Datum
      ) as T
INNER JOIN lu_tit_au AS L ON T.T_Id = L.LUTITAU_T_Id
GROUP BY
       T.T_Cover
     , T.T_Id
     , L.LUTITAU_A_Id
ORDER BY T_Datum DESC

Open in new window

0
 
LVL 11

Expert Comment

by:Louis01
ID: 39280770
Hi Paul

Sample of how you can achieve what you are looking for:
--Prepare data sample
declare @titles table(id int, title_name varchar(50), title_date datetime);
declare @authors table(id int, author_name varchar(50));
declare @autor_titles table(id int, title_id int, author_id int);

insert into @titles values (1, 'title 1', '01-Jun-2013');
insert into @titles values (2, 'title 2', '02-Jun-2013');
insert into @titles values (3, 'title 3', '03-Jun-2013');
insert into @titles values (4, 'title 4', '04-Jun-2013');
insert into @titles values (5, 'title 5', '05-Jun-2013');

insert into @authors values (1, 'Louis');
insert into @authors values (2, 'Paul');
insert into @authors values (3, 'Carl');

insert into @autor_titles values (1, 1, 1);
insert into @autor_titles values (2, 2, 1);
insert into @autor_titles values (3, 3, 2);
insert into @autor_titles values (4, 4, 2);
insert into @autor_titles values (5, 5, 3);

--Get all authors with the latest titles
select a.author_name
     , t1.title_date
     , t1.title_name
  from @authors a
        inner join @autor_titles at
    on a.id = at.author_id
        inner join @titles t1
    on t1.id = at.title_id
        inner join (select ax.id
                         , max(tx.title_date) as max_title_date
                      from @authors ax
                            inner join @autor_titles atx
                        on ax.id = atx.author_id
                            inner join @titles tx
                        on tx.id = atx.title_id
                     group by ax.id
                    ) t2
    on a.id = t2.id    
   and t1.title_date = t2.max_title_date
 order by a.author_name    

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280869
@Louis01, thanks:) appreciate it. I am not the author however.

I have (too often) had to resort to providing sample data, the big danger is though that our assumptions about the data are reflected in that generated sample. ie. it may not reflect the real situation.

the very reason I am asking for sample data is that - when provided - these types of questions get solved quickly.

Perhaps you saw the most recent E-E newsletter? (top right), quote:
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE
Use the SSCCE

Get the hint?
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39280887
;) Indeed. You are in my data sample though, hehehe
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Witheet
ID: 39280927
--Prepare data sample
declare @titels table(T_Id int, T_Title varchar(50), T_Datum datetime, T_Cover varchar(255));
declare @lu_tit_au table(LUTITAU_T_Id int, LUTITAU_A_Id int);

insert into @titels values (1, 'title 1', '01-Jun-2013');
insert into @titles values (2, 'title 2', '02-Jun-2013');
insert into @titles values (3, 'title 3', '03-Jun-2013');
insert into @titles values (4, 'title 4', '04-Jun-2013');
insert into @titles values (5, 'title 5', '05-Jun-2013');

insert into @lu_tit_au values (1, 1);
insert into @lu_tit_au values (2, 1);
insert into @lu_tit_au values (3, 2);
insert into @lu_tit_au values (4, 1);
insert into @lu_tit_au values (5, 2);

I am not sure if this is the way you want the sample data?
I also skipped the authors table since I am not querying this... ???

After the query the result should then contain 2 titles:
For author with id 1 the title showed should be the 1 with the title id: 4
For author with id 2 the title shown should be the 1 with the title id 5

Does this give a better view on the problem?

Please let me know and I will try to provide more if needed or in different format...

TYVM!
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39280948
declare @titels table(T_Id int, T_Title varchar(50), T_Datum datetime, T_Cover varchar(255));
declare @lu_tit_au table(LUTITAU_T_Id int, LUTITAU_A_Id int);

insert into @titels values (1, 'title 1', '01-Jun-2013', '');
insert into @titels values (2, 'title 2', '02-Jun-2013', '');
insert into @titels values (3, 'title 3', '03-Jun-2013', '');
insert into @titels values (4, 'title 4', '04-Jun-2013', '');
insert into @titels values (5, 'title 5', '05-Jun-2013', '');

insert into @lu_tit_au values (1, 1);
insert into @lu_tit_au values (2, 1);
insert into @lu_tit_au values (3, 2);
insert into @lu_tit_au values (4, 1);
insert into @lu_tit_au values (5, 2);

select * 
  from @lu_tit_au ta
        inner join @titels t1
    on t1.T_Id = ta.LUTITAU_T_Id
        inner join (select tax.LUTITAU_A_Id
                         , MAX(tx.T_Datum) as Max_T_Datum
                      from @lu_tit_au tax
                            inner join @titels tx
                        on tx.T_Id = tax.LUTITAU_T_Id
                     group by tax.LUTITAU_A_Id
                    ) t2
    on ta.LUTITAU_A_Id = t2.LUTITAU_A_Id
   and t1.T_Datum = t2.Max_T_Datum

Open in new window

0
 

Author Comment

by:Witheet
ID: 39280960
@portletpaul
SELECT
       T.T_Cover
     , T.T_Id
     , L.LUTITAU_A_Id
     , max(T.T_Date) as T_Date
     , max(T.T_Datum) as T_Datum
FROM (
        SELECT
              T_Cover
            , T_Id
            , T_Datum
            , max(T_Date) as t_Date
        FROM titels
        GROUP BY
              T_Cover
            , T_Id
            , T_Datum
      ) as T
INNER JOIN lu_tit_au AS L ON T.T_Id = L.LUTITAU_T_Id
GROUP BY
       T.T_Cover
     , T.T_Id
     , L.LUTITAU_A_Id
ORDER BY T_Datum DESC

Open in new window


gives the following result - see attachment
Again dates are sorted correctly but the authors are still not unique...
query-result-2.jpg
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280963
please, one last time, provide some sample data from each of the 2 tables
only your data really counts

have you ever tried to run a query where the tables don't exist?
or, run a query where the tables exist but are empty?

that's what we have right now, no tables, no data - no ability to run a query

>>Please let me know and I will try to provide more if needed or in different format...
data, please
0
 

Author Comment

by:Witheet
ID: 39280964
@louis01... also tyvm for your help... I will try yours out now...

wkr

rv
0
 

Author Comment

by:Witheet
ID: 39280966
@Portletpaul,

not sure in which format you would like it... Can you let me know the I will prove...
I assume you saw my post with sample data?

wkr
rv
0
 

Author Comment

by:Witheet
ID: 39280989
@Louis01

select * 
  from lu_tit_au AS ta
        inner join titels AS t1
    on t1.T_Id = ta.LUTITAU_T_Id
        inner join (select tax.LUTITAU_A_Id
                         , MAX(tx.T_Datum) as Max_T_Datum
                      from lu_tit_au AS tax
                            inner join titels AS tx
                        on tx.T_Id = tax.LUTITAU_T_Id
                     group by tax.LUTITAU_A_Id
                    ) AS t2
    on ta.LUTITAU_A_Id = t2.LUTITAU_A_Id
   and t1.T_Datum = t2.Max_T_Datum

Open in new window


The result is not as wanted. The highest date is 2 months ago and the authors are shown multiple times in the list of results... + the max-date field is empty... I had to rewrite it to what you see above to get it working...

Working on more/better sample data
query-result-3.jpg
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39280992
copy paste into Excel
a csv
anything that will allow us to build some tables

we don't need ALL your data, enough so we can "see" what you see
0
 

Author Comment

by:Witheet
ID: 39281025
working on it... almost done...

tnx for ur patience!
0
 

Author Comment

by:Witheet
ID: 39281043
I was working on this for sample data (see attachment) or do you prefer csv?

EDIT!
I aso added thee seperate txt files with sample data for three tables... Just to be sure I added the Authors table also...

EDIT 2!
The desired result should be a list with the following titles only:
- Title 7 - belongs to author with id 4
- Title 6 - belongs to author with id 3
- Title 5 - belongs to author with id 2

Again tyvm for your help and patience!!!
sample-data.sql
sample-data-auteurs.txt
sample-data-lutitau.txt
sample-data-titels.txt
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39281222
T_TITEL	T_ID	T_DATUM	LUTITAU_A_ID	ROW_REF
Title 5	36	June, 21 2013 	2	1
Title 6	38	June, 23 2013 	3	1
Title 7	39	June, 24 2013 	4	1

Open in new window

The query uses a technique that mimics row_number() in Oracle/SQL Server, see http://www.explodybits.com/2011/11/mysql-row-number/
THis code and sample data is at http://sqlfiddle.com/#!8/c7f45/1
SELECT
  T_Titel
, T_Id
, T_Datum
, LUTITAU_A_Id
, Row_Ref
FROM (
       SELECT
                 @row_num := IF(@prev_value = LUTITAU_A_Id, @row_num + 1, 1) AS Row_Ref
               , LUTITAU_A_Id
               , T_Datum
               , T_Titel
               , T_Id
               , @prev_value := LUTITAU_A_Id AS Prev_Value
        FROM (
               SELECT *
               FROM titels AS T
               INNER JOIN lu_tit_au AS L ON T.T_Id = L.LUTITAU_T_Id
             ) x
        CROSS JOIN (SELECT @row_num := 1, @prev_value := '' ) AS CJ
        ORDER BY LUTITAU_A_Id, T_Datum DESC
    ) AS derived
WHERE Row_Ref = 1

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281231
for that row_number() substitute technique in MySQL please note:

The @row_num variable must be set before the @prev_value variable
The first field in the ORDER BY must be the field that you are partitioning by
The default value of @prev_value variable must not exist in the partition by field
ALSO
note that this technique DOES NOT work well with multiple joins, so to get around this I have used the innermost subquery (x, in the above) instead.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281242
also note I resorted to "select *" - I don't recommend this, it was for convenience
0
 

Author Comment

by:Witheet
ID: 39281306
TYVM for your efforts.. I gave it a go and it is almost as should be...
Originally it did not do exactly what I had hoped... But after changing:
ORDER BY LUTITAU_A_Id, T_Datum DESC

Open in new window

in to
ORDER BY T_Datum, LUTITAU_A_Id DESC

Open in new window

it is almost correct now it only displays in reversed order... It shows the oldest title associated to the author not the newest... I will play with the settings some more to see if I can straighten it out...

For the life of me I would never have been able to formulate this query... So your help is really much appreciated... Awesome!!!

I will let you know the outcome of the fiddling asap...

tyvm!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281551
be careful, that order by is specifically designed. read my notes at ID: 39281231

the first field in the order must be the field we are 'partitioning by' otherwise the 'prev_value' will be wrong!

if you are concerned about the FINAL outcome, then ADD an order by at the end - do not fiddle with the one I provided.
0
 

Author Comment

by:Witheet
ID: 39281678
Yes I saw that... Have tried to add the extra ORDER BY and that works like a charm...

I will take your other comments in to account also and implement it for this query and future ones...

You are a true life saver... This was way beyond my ability to query data...

TYVM! Once again... Excellent stuff... Now to see if I can start to make sense of it all...

WKR RV
0
 

Author Closing Comment

by:Witheet
ID: 39281687
Great support and more then patient!!!

Also thanks to Louis01 for the help!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281702
Happy to have helped.
This "row_number() subsitute" is very useful in so many ways
BUT you must take some care to get it right.

Please note I could not have done this without the aid of your .sql file with data
- thank you for doing that - keep it in mind "sample data & expected results"
Cheers, Paul

Can you close off the question?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39281711
thank you!
0
 

Author Comment

by:Witheet
ID: 39281787
I will make sure to package my questions better in the future...

wkr rv
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Introduction Chart.js, used properly, can visually add a difference to your charting applications. It engages your visitors and allows them to interact with data they otherwise wouldn't be able to without expensive and complicated systems. For this…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now