CODEWITHSUNDEEP
sql-serverstored-proceduresstored-functions
Lester
Lester

Reputation: 1133

Get only one row with returned rows based on the value - SQL Server

I have a select query that returns data a table with different cases by Id, see below:

Case 1:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | large   |
+--------+---------+

Case 2:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | small   |
|      2 | x-large |
+--------+---------+

Case 3:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | small   |
|      2 | small   |
|      3 | x-large |
|      4 | large   |
+--------+---------+

Case 4:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | large   |
|      2 | medium  |
|      3 | large   |
+--------+---------+

Case 5:

+--------+---------+
| RowNum |  Size   |
+--------+---------+
|      1 | small   |
|      2 | x-large |
|      3 | medium  |
|      4 | large   |
+--------+---------+

Case 6, 7, 8.....

Note: all the returned table may have different rows and value, so it could be tens cases, and the row number just as a index with no meaning.

I need only one row returned with the largest size compared to the rows that the table has.

For example:

  1. in Case 1, I only need the 'large' row;
  2. in Case 2, I only need the 'x-large' row;
  3. in Case 3, I only need the 'x-large' row;
  4. in Case 4, I only need the 'large' row;
  5. in Case 5, I only need the 'x-large' row;

Can anyone help me find a way how to get the result?

Solution can be stored procedures, functions, views, or queries.

Many thanks!

Upvotes: 0

Views: 76

Answers (1)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Just to answer the presentation issue regardless of the logic you can use CASE over ORDER BY

select top 1 *
from 
table
order by (
case size
   when 'x-large' then 4 
   when 'large' then 3
   when 'medium' then 2
   when 'small' then 1
   else 0
end
) desc

Upvotes: 1

Related Questions