Jump to content

group by - having - aggregate???


kreplech

Recommended Posts

OK - This is an issue that has always been frustrating for me. In the data:John - Blue - 10/1/2006John - Red - 9/30/2007John - Black - 1/17/1999Paul - Green - 1/18/2008Paul - Red - 9/30/2007Ringo - Blue - 10/1/2003Ringo - Green - 3/2/1999How to get records with max dates for each unique name? Such as:John - Blue - 10/1/2006Paul - Green - 1/18/2008Ringo - Blue - 10/1/2003I've done this before with very kludgey sql. What's the best way?Thanks!!!Mark

Link to comment
Share on other sites

[...]How to get records with max dates for each unique name? Such as:John - Blue - 10/1/2006Paul - Green - 1/18/2008Ringo - Blue - 10/1/2003I've done this before with very kludgey sql. What's the best way?Thanks!!!Mark
To simply get the max dates for each unique name, you could use
	select beatlename, max(colourdate) 	from beatlecolours	group by beatlename

If you want other data included too (such as the colour names), you could perform an inner join between these results and the table:

select * from beatlecolours bcinner join (	select beatlename, max(colourdate) maxdate	from beatlecolours	group by beatlename)m on m.beatlename = bc.beatlename and m.maxdate = bc.colourdate

(beatlename used in join for illustration only--you would want to use IDs in reality)There are probably other ways too... in different sql engines... is my way less kludgey than yours?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...