[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

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

Posted on 2016-09-29
Medium Priority
68 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 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.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month12 days, 17 hours left to enroll