group by - having - aggregate???

[kreplech]

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

[Reg Edit]

[...]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?

[kreplech]

Thanks for the quick reply...[is my way less kludgey than yours?]Looks like it may be... are you treating color and date as two distinct fields?(Also, I'm generally using either MSSQL or MSAccess)

[Reg Edit]

are you treating color and date as two distinct fields?

Yes. In my example, colourdate is just the date. I didn't reference the colour field explicitly anywhere, but the select * would include it in the final output.