[SOLVED] SELECT particular disctinct row based on date

[infinets]

Hi,

    I have data as shown below.

 

park_id        item          pay_date

--------      ------ -    -----------

1              red            2007-01-04

1              blue            2007-03-05

2              red            2007-02-02

3              blue            2007-01-20

 

I want to be able to select distinct rows but selecting the row with the most recent date

 

e.g. want to output

 

park_id        item          pay_date

--------      ------ -    -----------

1              blue            2007-03-05

2              red            2007-02-02

3              blue            2007-01-20

 

 

That particular park_id 1 selected because it's date was the most recent.

 

Help please...

[bubblegum.anarchy]

The following code looks all kinds or wrong to me even through the results appear correct:

SELECT * FROM ( 
     SELECT * FROM park 
     ORDER BY pay_date DESC 
) AS derived 
GROUP BY id 
ORDER BY pay_date DESC;

[Barand]

It does rely on the assumption that values from those fields not specified in the GROUP BY clause will come from the first record encountered in the group. In my experience they do, BUT the manual does say those values are unpredictable.

 

Also MySql is lenient in allowing the group by to exclude some selected fields

 

I'd play safe and use a more portable version like this (sales table 10,000+ rows, 100 products, query time 0.005 sec)

SELECT s.prodcode, s.salesvalue, s.datesold FROM sales s
INNER JOIN (SELECT prodcode, MAX(datesold) as latest FROM sales GROUP BY prodcode) as b
ON s.prodcode = b.prodcode AND s.datesold = b.latest

 

For your table:

SELECT p.park_id, p.item, p.pay_date FROM park p
INNER JOIN (SELECT park_id, MAX(pay_date) as latest FROM park GROUP BY park_id) as b
ON p.park_id = b.park_id AND p.pay_date = b.latest

[bubblegum.anarchy]

oh yes, that is great Barand... and ORDER BY pay_date DESC

[infinets]

Thanks, that works great... was driving me nuts!!