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

Pneumatics

You have not provided sufficient information for an answer. You should post some sample data. – Withdrew

Answer 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?).

Melanous

i want the distinct to be only for the department and that the rest columns i will need will be avilable for using later so i need to get the id column – Pneumatics

@Pneumatics - Does each department have just one Id associated with it, or multiple? – Melanous

I cannot see anything wrong with this answer, it is the best that can be done given the lack of information from the OP. – Withdrew

Answer 2

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

Bookstall

Answer 3

What the question is asking exactly is unclear, but one scenario could be that you want to get all rows, except that a particular column should only contain unique values, and you don't mind which rows are discarded to achieve this.

In SQL Server this can be achieved with the following:

SELECT id, department FROM tbl WHERE id IN (
    SELECT MIN(id)
    FROM tbl
    GROUP BY department
)

Where id is unique for each row, department is the column which should be distinct, and tbl is the table name.

If you want to only perform this check on non-NULL values (so all NULL values for department are still returned), this can be tweaked to:

SELECT id, department FROM tbl WHERE department IS NULL OR id IN (
    SELECT MIN(id)
    FROM tbl
    GROUP BY department
)

Note that this will run very slowly, so is only feasible for tables with a small number of rows.

Lavender

Answer 4

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?

Conlan

Answer 5

The presented statement below assumes select the first id where matches the distinct value

select distinct (select top 1 id from tbl t2 where t1.department= t2.department) as id, department from tbl t1 

Figurative

Answer 6

Would a group by fix your problem?

select id, department from tbl group by id

Jurdi

no, not in msaccess (or databases other than mysql). the suggested SQL throws an error in databases that follow SQL standard in regards to GORUP BY. – Conlan

Sorry, I meant to write: select id, department from tbl group by id, department – Jurdi