2

i have a table like this:

id        code    delta
--        ----    -----
1          113        0 <-- minimum delta of ID 1
1          254       12
1         1254       55
2           45       42
2          148       10 <-- minimum delta of ID 2
2           20      100

I want to select the code that have the minimum delta of all row with the same id:

id    code
--    ----
1      113
2      148
Carlo77PV
  • 106
  • 8

1 Answers1

2

You could use:

SELECT id, code
FROM table
WHERE (id, delta) IN (SELECT id, MIN(delta)
                      FROM table
                      GROUP BY id);
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Is it working if you order by delta and den group by id? – René Höhle Nov 02 '17 at 16:32
  • Oh dear! It was so simple!? – Carlo77PV Nov 02 '17 at 16:33
  • @Stony Please post query – Lukasz Szozda Nov 02 '17 at 16:33
  • @lad2025 it was only a quest `SELECT * FROM table ORDER BY delta DESC GROUP BY id` Something like that... so that you order the table by delte that the minimum is on top and then group by... which entry will be the result? – René Höhle Nov 02 '17 at 16:35
  • @Stony Perhaps in MySQL, but I don't like this kind of aggreation from simple reason, if you agg by id then you cannot (shouldn't) use `SELECT *` Also please check https://stackoverflow.com/questions/33629168/group-by-clause-in-mysql-and-postgresql-why-the-error-in-postgresql – Lukasz Szozda Nov 02 '17 at 16:37