CODEWITHSUNDEEP
mysqlsql
Max Nijholt
Max Nijholt

Reputation: 13

Select one row with priority for value

Ello!

I'm trying to select a single row with either 'w', 'e' or 's' and an unique GUID. 'e' has a the highest priority so when a GUID contains that that line should be selected. 'w' the 2nd highest priority, so when there is no other than 's' 'w' should be selected. Along with the entire row data.

I've tried to use the following code:

SELECT * FROM TABLENAME 
WHERE GUID IN (SELECT DISTINCT GUID FROM TABLENAME ) 
AND (CATEGORY='S' OR CATEGORY='E' OR CATEGORY='W')

Is there anyway how I can do what I'm describing? I've been trying to use different ways of using distinct and other methods but it's still giving me more than one line per GUID.

Group by hasn't worked either. Since I get an error when I add that.

EDIT: Added Images https://i.sstatic.net/3zgeF.png

Upvotes: 1

Views: 2447

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select guid, min(priority) from
(SELECT *, 
 case when category = 'E' then 1
      when category = 'W' then 2
      when category = 'S' then 3 
 end as priority 
FROM TABLENAME 
WHERE CATEGORY in ('S','E','W') ) a

The inner query assigns priority to the category column as per the question. In the outer query you select from whichever category has the highest priority. Also include all the other columns you need in the outer select.

Edit: The method above isn't robust as you can't use the calculated priority column to select the corresponding category per guid. Hence, Another approach is to use union.

SELECT * FROM TABLENAME 
WHERE CATEGORY = 'E'
union
SELECT * FROM TABLENAME 
WHERE CATEGORY = 'W' 
and guid not in (select guid from tablename where category ='E')
union
SELECT * FROM TABLENAME 
WHERE CATEGORY = 'S'
and guid not in(select guid from tablename where category in ('E','W'))

The first query selects all rows where category is E. The second query selects all rows whose category is W and whose guid wasn't selected before. The third query selects all rows whose category is S and whose guid wasn't selected in the previous two queries. UNION ing all the queries should give you the results you need. Try the SQL fiddle with dummy data. http://sqlfiddle.com/#!9/07c0d/1

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269563

One method is to get the appropriate category for each guid and then join back. Another is a series of union alls. The latter is possibly more efficient, because you don't have very many options to prioritize:

select t.*
from tablename t
where category = 'e'
union all
select t.*
from tablename t
where category = 'w' and
      not exists (select 1 from tablename t2
                  where t2.category in ('e') and t2.guid  = t.guid
                 ) 
union all
select t.*
from tablename t
where category = 's' and
      not exists (select 1 from tablename t2
                  where t2.category in ('e', 'w') and t2.guid  = t.guid
                 ) ;

For performance, you want an index on category and guid, category.

Upvotes: 0

Related Questions