CODEWITHSUNDEEP
sqlms-accessselect
Y.G.J
Y.G.J

Reputation: 1108

select distinct and one another column of the id

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

Upvotes: 7

Views: 29527

Answers (6)

user8860492
user8860492

Reputation: 1

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

Upvotes: 0

Craig Brown
Craig Brown

Reputation: 2461

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.

Upvotes: 3

jwize
jwize

Reputation: 4175

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

Upvotes: 3

Unreason
Unreason

Reputation: 12704

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?

Upvotes: 2

Karl Gohery
Karl Gohery

Reputation: 124

Would a group by fix your problem?

select id, department from tbl group by id

Upvotes: -1

Oded
Oded

Reputation: 499392

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

Upvotes: 6

Related Questions