x
Solved

# How to fetch value of one column where another column is minimal?

Posted on 2016-09-29
Medium Priority
77 Views
What is the most short/elegant way to extract the value of column A where column B has the minimal value in the resultset?

For example:

create table Table1 (ColumnA char(1), ColumnB int, ColumnC int )
insert into table1 select 'A' , 2, 5
insert into table1 select 'B' , 3, 5
insert into table1 select 'C' , 1, 7

The query should return the value of ColumnA in the row where ColumnB is the minimal among the rows having ColumnC=5. In this case, it should return 'A' because 2<3. Ideally, the query should have condition WHERE ColumnC=5 only once.
0
• 2
• 2

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 41822199
Using ROW_NUMBER and a derived table is usually the fastest way.  I've included the "PARTITION BY ColumnC" just in case you want to look at multiple/all ColumnC values at the same time.

SELECT ColumnA, ColumnB, ColumnC
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ColumnC ORDER BY ColumnB) AS row_num
FROM table1
WHERE ColumnC = 5
) AS derived
WHERE row_num = 1
0

LVL 40

Author Comment

ID: 41822229
Cool, let's see if there are others.
0

LVL 40

Author Comment

ID: 41822798
Perhaps even simpler:

SELECT top 1 ColumnA
FROM Table1
WHERE ColumnC = 5
order by ColumnB
0

LVL 70

Expert Comment

ID: 41823671
For a single ColumnC value, yes, that's better.  As I noted in my original post, I included ROW_NUMBER() and partitioning in case you wanted to list all ColumnCs, or lots of different ColumnCs.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.