How to fetch value of one column where another column is minimal?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perhaps even simpler:
SELECT top 1 ColumnA
FROM Table1
WHERE ColumnC = 5
order by ColumnB
SELECT top 1 ColumnA
FROM Table1
WHERE ColumnC = 5
order by ColumnB
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.
ASKER