select distinct first row from database

Question

number      | time

421112233444 | 1304010250
421112233444 | 1304272979
421001122333 | 1303563263
421112233444 | 1300217115
421001122333 | 1303649310

i need to return unique first row with lowest number from second row, like this:

421112233444 | 1300217115
421001122333 | 1303563263

any idea?

i try SELECT ph.number, mo.time from (select distinct(number) from table) ph, table mo where mo.number = ph.number;

but it returns both uniques:

421112233444 | 1304010250
421112233444 | 1304272979
421001122333 | 1303563263
421112233444 | 1300217115
421001122333 | 1303649310

SELECT DISTINCT ON will help :) http://stackoverflow.com/questions/3800551/sql-select-first-row-in-each-group-by-group — aloplop85, Jan 25 '13 at 13:30

score 3 · Accepted Answer · answered Jun 20 '11 at 14:29

3

You can use the GROUP BY to do this:

SELECT number, MIN(time)
FROM table
GROUP BY number
ORDER BY number DESC
LIMIT 2

answered Jun 20 '11 at 14:29

user254875486

oh, that really helped me right now! but why just `ORDER BY` command not work here? I got almost same stuff in query, but if I leave just `ORDER BY` i get this `column “p.mydate” must appear in the GROUP BY clause or be used in an aggregate function` – DanilGholtsman Jan 30 '14 at 10:01
1

You get that message because you have an unaggregated column in your select clause that's also not in your group by clause. – user254875486 Jan 30 '14 at 13:39

score 0 · Answer 2 · answered Jun 20 '11 at 14:31

0

SELECT ph.number, min(mo.time) from (select distinct(number) from table) ph, table mo group by ph.number

answered Jun 20 '11 at 14:31

dave

2 Answers2