[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 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