Awkward grouping with an order by. Am stuck

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 2005 Forums

Transact-SQL (2005)

Awkward grouping with an order by. Am stuck

Author

Topic

GazNewt
Starting Member

14 Posts

Posted - 2007-02-21 : 17:34:00

I need to select records in a fairly complex way, and I'm stuck. My basic knowledge just can't find a way to pick out the records.

I have a table :

id articleid accountid date
-- --------- --------- ----
1 101 200 2007-02-09
2 102 200 2006-02-12
3 102 99 2007-01-02
4 101 200 2006-02-19
5 103 200 2006-09-22
6 102 99 2007-02-03
7 101 99 2006-11-08
8 103 200 2007-02-17
9 103 200 2006-12-21

First of all I want to get all records where the accountid is 200.

Second I want to only return one record for a distinct articleid.

Third, the distinct articleid must be the record from the group of records with the same accountid/articleid that has the most recent date.

So from the data above I need a select statement that will return :

id articleid accountid date
-- --------- --------- ----
1 101 200 2007-02-09
2 102 200 2006-02-12
8 103 200 2007-02-17

Any help will be much appreciated, thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-21 : 17:40:19

I think this'll work:


SELECT id, t.articleid, t.accountid, t.[date]
FROM YourTable y
INNER JOIN 
(
	SELECT accountid, articleid, MAX([date]) AS [date]
	FROM YourTable
	WHERE accountid = 200
	GROUP BY accountid, articleid
) t
ON y.accountid = t.accountid AND y.articleid = t.articleid

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources