1

I have this table entry:

A B C
a1 1 c1
a1 2 c2
a1 3 c3
a2 2 c4
a2 5 c5
a2 7 c6

What I want is a for every unique A, get only the first row ordered by its value on column B. Desired output is to have minimum B for each distinct key on column A, like this:

A B C
a1 1 c1
a2 2 c4

I know how to do this for any known key, but how do I loop over all distinct keys on column A?

-- for one given key
SELECT entry.*
FROM entry
WHERE entry.A = 'a2'
ORDER by entry.B
LIMIT 1;
Ken White
  • 123,280
  • 14
  • 225
  • 444
lightyears
  • 11
  • 2

3 Answers3

1

"Desired output is to have minimum B for each distinct key on column A"

SELECT A,MIN(B)
FROM entry
GROUP BY A
Jatin Morar
  • 164
  • 1
  • 7
  • 2
    This won't return the first _row_ ordered by `B`, it will return the minima of `B` and `C` separately. – shadowtalker Mar 14 '23 at 01:19
  • I think hes looking for the min.. I'll drop the C since its not written above for more clarity. I'm also going to assume that column B is an int and column C doesn't matter. – Jatin Morar Mar 14 '23 at 01:21
1

This answer explains the solution provided by Dai in the comments.

The trick here is to separate this into two tasks:

  1. Find some kind of set of attributes that uniquely identifies the the rows that you want.
  2. Use those uniquely-identifying values to select the rows that you want.

Task 1 is easy:

SELECT a, MIN(b)
FROM entries
GROUP BY a

Task 2 can then be accomplished using a join:

SELECT a, b, c
FROM entries
NATURAL JOIN (
  SELECT a, MIN(b) AS b
  FROM entries
  GROUP BY a
) AS t

Consider what this join accomplishes. The inner query returns a list of (a, b) tuples that together uniquely identify the rows that you care about. The join then matches those (a, b) tuples with rows in the original entries table, thereby finding exactly what we are looking for.

This pattern can be used in any problem where you need to find one specific row for each group.

shadowtalker
  • 12,529
  • 3
  • 53
  • 96
0

In the question you mentioned that you want the 1st row as shown in the table.

SELECT A,B,C FROM entry GROUP BY A

However if you want it in for a min of B just add a HAVING Clause

SELECT A,B,C FROM entry GROUP BY A HAVING MIN(B)
Finic
  • 5
  • 4