Opened:Need SQL query-Maximum date for each author

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Opened:Need SQL query-Maximum date for each author

Author

Topic

imbrod
Starting Member

10 Posts

Posted - 2006-09-28 : 04:35:02

Can someone help me out on this:

I have a table 'articles'

ID --- date --- authorID
1 --- 20060901 --- 1
2 --- 20060902 --- 1
3 --- 20060903 --- 3
4 --- 20060904 --- 3
5 --- 20060905 --- 2

I need a query (ID, date, authorID) that will produce the records that equals maximum date for each author (distinct authors), ordered by date descending.
For example, output should be in this case:

5 --- 20060905 --- 2
4 --- 20060904 --- 3
2 --- 20060902 --- 1

Hope that you understand what I mean...
Please help!

P.S. Don't worry about using the reserved word in the field name 'date', it has another original name...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 04:49:38

select a.* from articles a inner join
(select authorid, max(date) md from articles group by authorid) z on z.authorid = a.authorid and z.md = a.date

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 04:50:53

select id, t2.date, authorid
from tbl t1
join
(select authorid, max(date) as date
from tbl
group by authorid) as t2
on t1.date = t2.date and
t1.authorid = t1.authorid

Not again, Peter !!!

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 05:01:38

No worry! Next posting from you will be half K!

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 08:19:27

Peso is always faster

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 09:06:51

quote:
Originally posted by madhivanan

Peso is always faster

Madhivanan

Failing to plan is Planning to fail

I couldn't agree more and still always has nice solution !!

Harsh Athalye
India.
"Nothing is Impossible"

imbrod
Starting Member

10 Posts

Posted - 2006-09-28 : 09:25:38

Wow - that was fast replies!

Thank you all! I'll check it out and let you know.

imbrod
Starting Member

10 Posts

Posted - 2006-09-28 : 15:33:37

quote:
Originally posted by Peso

select a.* from articles a inner join
(select authorid, max(date) md from articles group by authorid) z on z.authorid = a.authorid and z.md = a.date

Peter Larsson
Helsingborg, Sweden

It works like a clock!!!
(I just added ORDER BY a.date DESC to finish my goal)
Thanks again!

(I should put 'SOLVED' on this post but don't know how)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 21:08:30

Edit your question and Change the subject

Madhivanan

Failing to plan is Planning to fail

imbrod
Starting Member

10 Posts

Posted - 2006-09-30 : 09:41:35

Would it be too much trouble if I ask you to refine the query so I can get authorName as well from joining with another table?

authors

authorID --- authorName
1 --- AAAA
2 --- BBBB
3 --- CCCC

Along with the fields I mentioned (articles.ID, articles.date, articles.authorID), I need also authors.authorName?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-30 : 09:52:44

You just have join to Authors table as well:

select t1.id, t2.date, t2.authorid, au.AuthorName
from tbl t1
join
(select authorid, max(date) as date
from tbl
group by authorid) as t2
on t1.date = t2.date and
t1.authorid = t1.authorid
join Authors au
on t2.authorid = au.authorid

Harsh Athalye
India.
"Nothing is Impossible"

imbrod
Starting Member

10 Posts

Posted - 2006-09-30 : 10:09:51

Sorry, it doesn't work...
Could you rewrite the query with the field names I provided, please?

Maybe this line is wrong:
t1.authorid = t1.authorid?

articles:
ID --- date --- authorID --- title --- text
1 --- 20060901 --- 1 --- aaa --- aaa1
2 --- 20060902 --- 1 --- bbb --- aaa1
3 --- 20060903 --- 3 --- ccc --- aaa1
4 --- 20060904 --- 3 --- ddd --- aaa1
5 --- 20060905 --- 2 --- eee --- aaa1

authors:
authorID --- authorName
1 --- aaaaaa
2 --- bbbbbb
3 --- cccccc

So basically I need:
- articles.ID, articles.date, articles.title, articles.text, authors.authorName
- show only records of each author that has newest (highest) date for that author (1 record for 1 author)
- order all the records by date descending

Thanks in advance!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-30 : 10:28:58

may be this will solve your problem:

select t1.id, t2.date, t2.authorid, t1.text, au.AuthorName
from Articles t1
join
(select authorid, max(date) as date
from Articles
group by authorid) as t2
on t1.date = t2.date and
t1.authorid = t2.authorid
join Authors au
on t2.authorid = au.authorid
order by t2.date

Harsh Athalye
India.
"Nothing is Impossible"

imbrod
Starting Member

10 Posts

Posted - 2006-09-30 : 10:45:52

Sorry, doesn't work.

Maybe it's because I use MS Access and it probably can't put 3 JOINs together...

I made a solution with 2 recordsets (2 queries) finally.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources