SQL select with preference on column values

Question

I am new to SQL and I would like to ask about how to select entries based on preferences and grouping.

+----------+----------+------+
| ENTRY_ID | ROUTE_ID | TYPE |
+----------+----------+------+
|        1 |       15 |    0 |
|        1 |       26 |    1 |
|        1 |       39 |    1 |
|        2 |       22 |    1 |
|        2 |       15 |    1 |
|        3 |       30 |    1 |
|        3 |       35 |    0 |
|        3 |       40 |    1 |
+----------+----------+------+

With the table above, I would like to select 1 entry for each ENTRY_ID with the following preference for the returned ROUTE_ID:

1. IF TYPE = 0 is available for any one of the entries with the same ENTRY_ID, return the minimum ROUTE_ID for all entries with TYPE = 0
2. IF for the same ENTRY_ID only TYPE = 1 is available, return the minimum ROUTE_ID

The expected outcome for the query will be the following:

+----------+----------+------+
| ENTRY_ID | ROUTE_ID | TYPE |
+----------+----------+------+
|        1 |       15 |    0 |
|        2 |       15 |    1 |
|        3 |       35 |    0 |
+----------+----------+------+

Thank you for your help!

Answer 1

You can group by both TYPE and ENTRY_ID, and then use the HAVING clause to filter out those where TYPE is not the minimal value for that record.

SELECT ENTRY_ID, MIN(ROUTE_ID), TYPE
FROM MyTable
GROUP BY ENTRY_ID, TYPE
HAVING TYPE = (SELECT MIN(s.TYPE) FROM MyTable s WHERE s.ENTRY_ID = MyTable.ENTRY_ID)

This relies on type only being able to be 0 or 1. If there are more possible values, it will only return the lowest type.