Jump to content

[SOLVED] SELECT particular disctinct row based on date


infinets

Recommended Posts

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...

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

Archived

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

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.