How do I write an SQL query or what PHP function do I use so that I only one row of results is returned when one column has duplicate entries?

Question

My SQL table is like the following

╔══════════╦═════════════════╦═════════╦════════════╦═══════╗
║ username ║ expiration_date ║ item_id ║ item_price ║ cents ║
╠══════════╬═════════════════╬═════════╬════════════╬═══════╣
║ joe      ║      1313446093 ║      10 ║          0 ║     1 ║
║ james    ║      1324242242 ║      11 ║          0 ║     1 ║
║ Sarah    ║      1324552599 ║      10 ║          0 ║     1 ║
╚══════════╩═════════════════╩═════════╩════════════╩═══════╝

and what I have so far for my sql query is variable $sql receives

SELECT expiration_date, item_price 
FROM list 
WHERE expiration_date > '$current_time'

and from the example table above, I would get two results back for item_id = 10. However, I only wish to receive the latest one, in this case Sarah's row. How do I code so that only the latest of the same item_id type is returned during each query.

Dup of [Fetch the row which has the Max value for a column](http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column), [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql), [SQL: Find the max record per group](http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group), — outis, Aug 17 '11 at 01:07
... [How to get all the fields of a row using the SQL MAX function?](http://stackoverflow.com/questions/2893903/how-to-get-all-the-fields-of-a-row-using-the-sql-max-function), [MySQL : selecting all corresponding fields using MAX and GROUP BY](http://stackoverflow.com/questions/1305056/mysql-selecting-all-corresponding-fields-using-max-and-group-by) — outis, Aug 17 '11 at 01:11

score 3 · Accepted Answer · edited Aug 17 '11 at 01:09

3

$sql = "
SELECT expiration_date, item_price 
FROM list 
WHERE expiration_date > '$current_time' 
ORDER BY expiration_date DESC 
LIMIT 1
";

Sort them with latest being returned first, then just limit to one result. If I understood the question properly, this should work like a charm.

edited Aug 17 '11 at 01:09

Jared Farrish

48,585
17
95
104

answered Aug 17 '11 at 01:07

Matt Stein

3,053
1
19
35

score 2 · Answer 2 · answered Aug 17 '11 at 01:08

2

Try This:

SELECT * 
FROM list L 
INNER JOIN (
        SELECT MAX(expiration_date) as expiration_date, item_id 
        FROM  list 
        GROUP BY item_id 
       ) M 
ON L.item_id = M.item_id and L.expiration_date = M.expiration_date

answered Aug 17 '11 at 01:08

Code Magician

23,217
7
60
77

`MAX` should do the trick. I'd be curious to know how it compares with an `ORDER BY` plus `LIMIT 1` in terms of performance. – James P. Aug 17 '11 at 01:11
1

A sort would almost certainly be faster that the aggregated subquery. The `ORDER BY Expiration_date DESC LIMIT 1` is the best solution if you need one record i.e. `SELECT * RFOM list WHERE item_id = 10 ORDER BY expiration_date DESC LIMIT 1` If you want to get the latest row for all the item_ids, the query above is probably your best option in mySQL – Code Magician Aug 17 '11 at 01:18

score 2 · Answer 3 · answered Aug 17 '11 at 01:08

2

Add an ORDER BY and LIMIT to the end of your query:

SELECT expiration_date, item_price 
FROM list
WHERE expiration_date > '$current_time'
ORDER BY expiration_date DESC
LIMIT 1;

answered Aug 17 '11 at 01:08

Derek

21,828
7
53
61

Dubbo · Answer 4 · 2011-08-17T03:03:00.297

0

$sql = "
select item_price, MAX(expiration_date) 
from list 
where expiration_date > '$current_time' 
group by item_id";

edited Aug 17 '11 at 03:03

answered Aug 17 '11 at 01:25

Dubbo

686
6
8

How do I write an SQL query or what PHP function do I use so that I only one row of results is returned when one column has duplicate entries?

4 Answers4