Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2016-09-29
4
Medium Priority
?
77 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
  • 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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.

Join & Write a Comment

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 month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

580 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