SQL MIN across multiple columns with a GROUP BY

Question

I'm trying to take a table with information as follows:

+----+---+---+
| ID | X | Y |
+----+---+---+
| A  | 1 | 3 |
| A  | 1 | 1 |
| A  | 1 | 2 |
| A  | 1 | 7 |
| B  | 2 | 2 |
| B  | 3 | 3 |
| B  | 1 | 9 |
| B  | 2 | 4 |
| B  | 2 | 1 |
| C  | 1 | 1 |
+----+---+---+

I'd like to be able to select the minimum across both columns, grouping by the first column - the "X" column is more important than the Y column. So for example, the query should return something like this:

+----+---+---+
| ID | X | Y |
+----+---+---+
| A  | 1 | 1 |
| B  | 1 | 9 |
| C  | 1 | 1 |
+----+---+---+

Any ideas? I've gone through dozens of posts and experiments and no luck so far.

Thanks, James

What RDBMS you are using? RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc... — John Woo
– John Woo, Commented Mar 1, 2013 at 18:19

Gordon Linoff · Accepted Answer · 2013-03-01 18:56:08Z

4

You seem to want the row that has the minimum x value. And, if there are duplicates on x, then take the one with the minimum y.

For this, use row_number():

select id, x, y
from (select t.*,
             row_number() over (partition by id order by x, y) as seqnum
      from t
     ) t
where seqnum = 1

If your database does not support window functions, you can still express this in SQL:

select t.id, t.x, min(t.y)
from t join
     (select id, MIN(x) as minx
      from t
      group by id
     ) tmin
     on t.id = tmin.id and t.x = tmin.minx
group by t.id, t.x

edited Mar 1, 2013 at 18:56

answered Mar 1, 2013 at 18:20

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-03-01 18:20:58Z

2

If your RDBMS supports Window Function,

SELECT ID, X, Y
FROM
        (
            SELECT ID, X, Y,
                    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY X, Y) rn
            FROM    tableName
        ) d
WHERE   rn  = 1

SQLFiddle Demo

answered Mar 1, 2013 at 18:20

John Woo

265k70 gold badges509 silver badges500 bronze badges

1 Comment

James F Over a year ago

Works like a charm. For anyone wondering, to get info from other tables via JOINS/etc, joi in the subquery and go from there.

Collectives™ on Stack Overflow

SQL MIN across multiple columns with a GROUP BY

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related