Group by and select lowest

Question

I am trying to select the non repeated records of my database, and if there is one repeated, choose the one with the soonest date. To do so, I have tried the following query:

SELECT name, MIN(date) FROM `table` GROUP BY number_offer ORDER BY `date` ASC

and then, showing them like this:

echo $row['fecha'];
echo $row['name'];

The query selects correctly all the non repeated records but fails at selecting the sooner date (it says 'undefined index "date"'). Do you have any idea of how to do that?

I am trying to select the non repeated records of my database, and if there is one repeated, choose the one with the min date. — alberzyzz, Jul 04 '17 at 07:18
`SELECT name, MIN(date) as adate FROM table GROUP BY name ORDER BY adate ASC` use an alias to the column min(date) — Ivo P, Jul 04 '17 at 07:20
Why are you grouping by a different column from the one you're selecting? Is there a 1-to-1 correspondence between `number_offer` and `name`? If not, you'll get a random `name` from the `number_offer` group. — Barmar, Jul 04 '17 at 07:21
@NavnitMishra If he removes `min()` then he won't get the lowest date like he wants. — Barmar, Jul 04 '17 at 07:23
@NavnitMishra That will return the lowest date in the whole table, not in each group. — Barmar, Jul 04 '17 at 07:24
@NavnitMishra `MIN(date)` is exactly correct for getting the lowest date in each group. I don't know why you think it's wrong. — Barmar, Jul 04 '17 at 07:24
Thank you all for you answers. @Strawberry and IvoP your solutions worked for me. If you could make a complete answer I will accept it — alberzyzz, Jul 04 '17 at 07:25
This amounts to little more than a simple typographical error, so it doesn't in my view warrant an answer, and should be closed accordingly. Others obviously disagree. — Strawberry, Jul 04 '17 at 07:28

score 3 · Accepted Answer · answered Jul 04 '17 at 07:23

the index fail is related to a missing alias

SELECT name, MIN(date) as `date`
FROM `table` 
GROUP BY number_offer 
ORDER BY `date` ASC

but you are select for name and grouping by number_offer ..the select for column not aggreation function and not in group by is deprecated
in sql starting recent db version

you should re evaluate you query using proper group by eg:

if you are select name you should group by name (and not number_offer)

SELECT name, MIN(date) 
FROM `table` 
GROUP BY name 
ORDER BY `date` ASC

score 0 · Answer 2 · edited May 01 '20 at 15:54

0

You can set MIN(date) as datee or MIN(date) datee

edited May 01 '20 at 15:54

Dharman

30,962
25
85
135

answered Jul 04 '17 at 07:25

kui.z

74
2

Group by and select lowest

2 Answers2