CODEWITHSUNDEEP
sqlgreatest-n-per-groupnetezza
user3206440
user3206440

Reputation: 5049

Select all columns from rows distinct on one column

I am using Netezza (based on PostgreSQL) and need to select all columns in a table for rows distinct on one column. A related question with answer can be found here, but it doesn't handle the case with all columns, going by that answer throws an error:

select distinct on (some_field) table1.* from table1 order by some_field;

Snippet from error with real data:

"(" (at char 77) expecting '')''

Upvotes: 0

Views: 6718

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

The syntax of your query is correct for Postgres (like you declared at first). See:

You later clarified you actually work with Netezza, which is only loosely related to Postgres. Wikipedia states:

Netezza is based on PostgreSQL 7.2, but does not maintain compatibility.

Netezza does not seem to support DISTINCT ON (), only DISTINCT.
It supports row_number(), though. So this works:

SELECT *
FROM  (
   SELECT *, row_number() OVER (PARTITION BY some_field) AS rn
   FROM   table1
   ) sub
WHERE  rn = 1;

If, from each set with identical some_field, any row is good, you are done here. Else, implement your priority with ORDER BY in the OVER clause.

Related:

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I don't think your code should throw an error in Postgres. However, it won't do what you expect without an order by:

select distinct on (some_field) table1.*
from table1
order by some_field;

Upvotes: 3

Related Questions