[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

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.
0
Vadim Rapp
Asked:
Vadim Rapp
  • 2
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
Vadim RappAuthor Commented:
Cool, let's see if there are others.
0
 
Vadim RappAuthor Commented:
Perhaps even simpler:

    SELECT top 1 ColumnA  
    FROM Table1
    WHERE ColumnC = 5
    order by ColumnB
0
 
Scott PletcherSenior DBACommented:
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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now