CODEWITHSUNDEEP
mysqlsql
triden
triden

Reputation: 23

MySQL select last unique occurrence

Let's say you have the following table (the column of interest here is binid):

id  datetime                 agid   binid   status
 1  2013-02-01 11:03:49        0      25    1
 2  2013-02-01 11:03:53        0      25    1
 3  2013-02-01 11:04:21        0      26    1
 4  2013-02-01 11:04:23        0      26    0
 5  2013-03-01 11:04:26        0      25    0
 6  2013-03-01 11:04:30        0      36    0
 7  2013-03-01 11:04:34        0      36    1
 8  2013-03-01 11:04:35        0      36    1
 9  2013-03-01 11:04:36        0      36    1
 10 2013-03-01 11:04:39        0      36    0
 11 2013-03-01 11:04:41        0      36    1
 13 2013-03-01 11:04:50        0      25    1
 14 2013-03-01 11:04:53        0      26    1
 15 2013-03-01 11:15:25        0      25    1
 16 2013-03-01 11:15:30        0      25    0
 17 2013-03-01 11:15:39        0      23    1
 18 2013-03-01 11:15:43        0      26    1

How can I extract the last occurrence of each binid that occurs in a certain timeframe?

This is what I am using so far:

SELECT * FROM ( reports ORDER BY datetime ASC ) 
WHERE datetime >= TIMESTAMP('2013-03-01') 
GROUP BY binid

but it returns the first occurrences instead. How can I return the last occurrence of each unique binid?

Upvotes: 2

Views: 5460

Answers (4)

Mikhail Vladimirov
Mikhail Vladimirov

Reputation: 13890

SELECT binid, 
       SUBSTRING_INDEX(GROUP_CONCAT(agid ORDER BY datetime DESC), ',', 1) AS agid, 
       SUBSTRING_INDEX(GROUP_CONCAT(status ORDER BY datetime DESC), ',', 1) AS status
FROM reports
WHERE datetime <= TIMESTAMP('2013-03-01')
GROUP BY binid;

Upvotes: 3

MIIB
MIIB

Reputation: 1849

SELECT * FROM ( reports ORDER BY datetime DESC) 
WHERE datetime >= TIMESTAMP('2013-03-01') 
GROUP BY binid LIMIT 1

Upvotes: 0

Taryn
Taryn

Reputation: 247640

You should use a subquery to get the result:

select r1.*
from reports r1
inner join
(
  select max(datetime) MaxDate, binid
  from reports
  WHERE datetime >= TIMESTAMP('2013-03-01') 
  group by binid
) r2
  on r1.binid = r2.binid
  and r1.datetime = r2.maxdate
WHERE r1.datetime >= TIMESTAMP('2013-03-01')

See SQL Fiddle with Demo

The problem is that when you are using a GROUP BY on a single column then MySQL can return an unexpected value for the other columns not in the GROUP BY. (see MySQL Extensions to GROUP BY).

From the MySQL Docs:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

Upvotes: 8

Tom
Tom

Reputation: 6663

Change ORDER BY ASC to ORDER BY DESC. Should do the trick.

Upvotes: -3

Related Questions