mysql select where field = max(date) and group by

Question

I have the following query. I'd need to pick only the oldest rows (by DATE column). keeping my conditions (where and group by)

SELECT 
table.*
from database.table
where views = $views AND sales>23
group by 
name

to be a bit clearer it is like passing frm this first table

+--------------------+-----------+--------------------+
| USER               | id        | DATE               |
+--------------------+-----------+--------------------+
| sue1               |       494 | 2014-06-23 12:08:29|
| sue2               |       494 | 2014-06-25 12:08:29|
| sue3               |       494 | 2014-06-27 12:08:29|
| sue4               |       520 | 2014-06-26 12:08:29|
| sue5               |       570 | 2014-06-24 12:08:29|
+--------------------+-----------+--------------------+

to this second one

+--------------------+-----------+--------------------+
| USER               | id        | DATE               |
+--------------------+-----------+--------------------+
| sue3               |       494 | 2014-06-27 12:08:29|
| sue4               |       520 | 2014-06-26 12:08:29|
| sue5               |       570 | 2014-06-24 12:08:29|
+--------------------+-----------+--------------------+

marko.c

This has been asked literally **thousand times**. Duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) (this has the best answer), http://stackoverflow.com/q/7745609/684229 and may other. Look at the tag [tag:greatest-n-per-group] — Tomas, Jul 08 '14 at 18:32
Why would you need a GROUP BY clause in a query with no aggregation? — Strawberry, Jul 08 '14 at 18:36

Zeus · Accepted Answer · 2014-07-08T18:28:47.347

1

You can use the following subselect way of doing.

SELECT 
t1.* 
from database.table t1
where t1.views = $views AND t1.sales>23
and t1.date in (select min(date) from table where id = t1.id group by id)
group by 
t1.name  ;

edited Jul 08 '14 at 18:28

answered Jul 08 '14 at 18:15

Zeus

6,386
6
54
89

so the sub select returns a date and tableID returns an int... these don't appear to match. no records returned each time right? – xQbert Jul 08 '14 at 18:20
@xQbert You are right, I've updated the query. :) a good way of doing is by using correlated subquery. – Zeus Jul 08 '14 at 18:23

score 0 · Answer 2 · answered Jul 08 '14 at 18:12

0

Use subselect
SELECT * FROM (SELECT table.*....) WHERE `DATE` ... ORDER BY `DATE` LIMIT ...
or having
SELECT table.* ... GROUP BY name HAVING `DATE` ...

answered Jul 08 '14 at 18:12

Droid

569
4
12

score 0 · Answer 3 · answered Jul 08 '14 at 18:19

0

Not positive this is what you're after though, your group by seems strange. Perhaps simple abuse of the the mySQL group by extensions could be causing part of the problem... As a rule of thum, anything with an aggravate should have a group by, otherwise... why do you need a group by? (I see no aggregrates here...)

SELECT 
table.*
from database.table DT1
where Dt1.views = $views AND Dt1.sales>23
and dt1.date = (Select min(date) from database.table DT2 where user=Dt1.user)
group by name

answered Jul 08 '14 at 18:19

xQbert

34,733
2
41
62

well, let's suppose I have different equal names with the same date. There, shouldn't group it? – marko c. Jul 09 '14 at 12:09
1

different equal names... Oxymoron much? I must admit I'm having trouble understanding the needs here. You say group by name, but there's no name column in the data listed, I assumed you meant user but *shrug*.. Next Sue5 isn't in your results. She has a different ID I figured she should be in the results. So at this time I have no idea what the end result should be. I assumed you were after the most recent record based on the question title, but that's clearly not the case given your expected results since sue5 isn't there. Clarify the problem and expected results. Help us, help you. – xQbert Jul 09 '14 at 12:45

mysql select where field = max(date) and group by

3 Answers3