Web Development

Ask something
Discussion Locked

Select DISTINCT on specific column?

0 helpful
3 replies

by andrew

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

3 replies

by sparent

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)

This discussion is closed to new replies.