SQL - Select rows based on a hierarchy of conditions

Question

Hi All

It would be great if there was a POSITIVE answer to this question...

Is it possible to make an SQL selection based on an order of possible condition values.

Eg.

Rows
ID Type
2 Dog
2 Cat
4 Cat
5 Cat

As you can see the IDs of the 1st 2 rows are the same. So I would like to select 1 row per group in order of preference, therefore "Dog" first. If "Dog" doesn't exists, then select "Cat"

E.G.
SELECT ID, Type
FROM pets
WHERE Type = "Dog, Cat"
GROUP BY ID

Results would be...

ID Type
2 Dog
4 Cat
5 Cat

Answer 1

I'm not sure what your "real" requirement is, but for your exmple you can use:

SELECT ID, Max(Type)
FROM pets
WHERE Type IN ('Dog', 'Cat')
GROUP BY ID

Notes:

1. The IN clause is required only if you want to limit types. It may be removed if you want all the types included in the query.
2. This query works if the ordering by which you prioritize the Types can be done using sorting, either alphabetically or numerically. If so, using Min or Max together with GROUP BY is the simplest and most efficient solution. If however your prioritization is based on some specific ordering, look-up table etc, you will need a different solution where you have a separate table containing the priorities and joined into the main query, as astander answered.

Answer 2

If grouping (as in the answer above) is not the option take a look at ranking functions introduced in SQL Server 2005.

Answer 3

If you have another table which ranks your options in order by preference, then just do something like this:

SELECT pets.ID, pets.Type
FROM pets,petprefs
WHERE pets.Type=petprefs.Type
GROUP BY pets.ID
ORDER BY petprefs.rank

The type with a lower value for rank would always be selected before the type(s) with a higher rank.

Answer 4

I'm fully agree with answer of Rax Olgud. Also can add hint to use ROW_NUMBER function with partition syntax - you get possibility to write more complex condition (then MAX(Type) . Then in WHERE section just filter records by rownum_alias = 1

Update:

select * from (
select ID, Type, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Type DESC) rn
FROM #temp
) a where rn = 1

In contradiction to GROUP BY solution this allows to place any columns in select statement (not only grouping)