select distinct and one another column of the id

Question

I have a table with multiple columns but I need only 2.

select id, department from tbl

If I want to use distinct, how do I do that? This is not working:

select id, distinct department from tbl

Solution 1

Use the following to get distinct rows:

select distinct id, department 
from tbl

However, you can't simply get distinct departments if some departments have multiple Id's - you need to figure out which of the multiple Id's you want (max? min? something else?).

Solution 2

SELECT  * FROM Table c1
 WHERE ID = (SELECT MIN(ID) FROM Table c2
    WHERE c1.department = c2.department)

Solution 3

DISTINCT needs to operate on all of the columns for the same reason why GROUP BY needs to include all the columns (that don't have aggregate functions operate on them) and that is that in the case you want to apply DISTINCT to the following resultset

id    department
----------------
1     one
2     one
3     one
4     two

then even if SELECT id, DISTINCT department FROM table_name was allowed (and it is in some databases; for example mysql can do group by department and not include id in the GROUP BY) then you would end up with undefined situation:

id    department
----------------
?     one
4     two

What should go instead of ? - 1, 2 or 3?