CODEWITHSUNDEEP
sqlsql-server
Vijay
Vijay

Reputation: 117

SQL Query to pick a row based on some condition

Is there a way in SQL to choose a particular row by checking the condition in sequence?

Condition: choose 1 FC, min(status),max(Version),max(CD),max(MD)

Table Data:

FC  Status  Version CD  MD
1   999     23      20  13
1   500     10      22  15
1   400     23      19  11
1   500     15      18  9
1   400     19      17  12
1   400     19      16  13
1   400     23      17  30

Output should be:

FC  Status  Version CD  MD
1   400     23      19  11

Upvotes: 1

Views: 85

Answers (3)

Mansoor
Mansoor

Reputation: 4192

Using GROUP BY clause :

SELECT FC,min(status),max(Version),max(CD),max(MD)
FROM YOUR_table
GROUP BY FC

Upvotes: 0

Serg
Serg

Reputation: 22811

Sql server has handy with ties and allows for row_number() in Order by. So

select top 1 with ties t.*
from t
order by row_number() 
         over (partition by fc order by status asc, version desc, cd desc, md desc) ;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Yes. Use TOP and ORDER BY:

select top 1 t.*
from t
order by status asc, version desc, cd desc, md desc;

If you want to do this per FC, then use row_number():

select t.*
from (select t.*,
             row_number() over (partition by fc
                                order by status asc, version desc, cd desc, md desc
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 8

Related Questions