Query Help: Getting distinct MAX values

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)

Query Help: Getting distinct MAX values

Author

Topic

jaroot
Starting Member

46 Posts

Posted - 2005-10-28 : 11:38:05


DATA:
tRecords
RECORDID  ARTISTID  RELDATE
1             1     1/1/2002
2             1     1/1/2003
3             2     2/2/2002
4             2     2/2/2003

I want to return in my recordset:


RECORDID  ARTISTID  RELDATE
2             1     1/1/2003
4             4     2/2/2003

Basically the latest record(RECORDID) and release date (RELDATE)
for each artist.
I thought I could do something like..


SELECT RECORDID, ARTISTID, MAX(RELDATE) AS LATESTRELDATE
FROM tRecords
GROUP BY ARTISTID ,RECORDID, RELDATE

But that returns them all...

Thanks in advance!

jaroot
Starting Member

46 Posts

Posted - 2005-10-28 : 11:44:09

Ohh wait I think I have it..
[CODE]
SELECT R.RECORDID, R.ARTISTID, MAX(R.RELDATE) AS LATESTRELDATE
FROM(
SELECT RECORDID, ARTISTID, RELDATE
FROM tRecords
GROUP BY ARTISTID ,RECORDID, RELDATE) R
[/CODE]

That seems to work..

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-10-28 : 11:59:25

Hope this will work for you

select max(recordid), max(artistid), max(reldate) from Test
group by artistid

With Regards
BSR

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-28 : 12:06:24

Where do you get an Artist ID of 4?


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(RECORDID int, ARTISTID int, RELDATE datetime)
GO

INSERT INTO myTable99(RECORDID, ARTISTID, RELDATE)
SELECT 1,             1,     '1/1/2002' UNION ALL
SELECT 2,             1,     '1/1/2003' UNION ALL
SELECT 3,             2,     '2/2/2002' UNION ALL
SELECT 4,             2,     '2/2/2003'
GO

SELECT * 
  FROM myTable99 o
 WHERE  RELDATE = (SELECT MAX(RELDATE) 
		     FROM myTable99 i 
		    WHERE o.ARTISTID = i.ARTISTID)
ORDER BY ArtistID
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources