General discussion

Locked

Select DISTINCT on specific column?

By andrew ·
I'd like to query a table, returning only rows where a specific column has unique values. Something like:

SELECT DISTINCT Col1, Col2, Col3
FROM Table1

but only have the 'DISTINCT' predicate apply to Col1, not the entire row.

I'm able to count the unique rows using a HAVING COUNT(*)>1 clause, but I can't seem to find a concise way (other than temp tables) to filter them out.

I'm using SQL Server 7.0.

TIA for any pointers.

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Select DISTINCT on specific column?

by sparent In reply to Select DISTINCT on specif ...

This is the way I would do it in Oracle.

SELECT col1, col2, col3
FROM table1 a
WHERE
NOT EXISTS
(SELECT 1
FROM table1 b
WHERE b.ROWID != a.ROWID
AND b.col1 = a.col1)

A more generic version would look like this

SELECTcol1, col2, col3
FROM table1 a
WHERE 1 = (SELECT COUNT(*)
FROM table1 b
WHERE b.col1 = a.col1)

Collapse -

Select DISTINCT on specific column?

by andrew In reply to Select DISTINCT on specif ...

Very cool. It was the self join idea that was eluding me. Thanks!

Collapse -

Select DISTINCT on specific column?

by andrew In reply to Select DISTINCT on specif ...

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums