[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
4
Medium Priority
?
68 Views
Last Modified: 2016-09-30
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
Comment
Question by:Vadim Rapp
[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
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 70

Accepted Solution

by:
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

by:Vadim Rapp
ID: 41822229
Cool, let's see if there are others.
0
 
LVL 40

Author Comment

by:Vadim Rapp
ID: 41822798
Perhaps even simpler:

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

Expert Comment

by:Scott Pletcher
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question