From: | Paul Smith* <paul(at)pscs(dot)co(dot)uk> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Required distinct record with all fields |
Date: | 2024-04-25 15:09:20 |
Message-ID: | fe410f5e-9f98-420a-8254-f11334c69689@pscs.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On 25/04/2024 15:58, Sunil Jadhav wrote:
> Hello team,
>
> we have table & its having 10 diff columns.
>
> 1) we need to fetch distinct records from 1 column
>
> suppose earlier we have 500 records after
> distinct we have 150 records only -->> we can used simply distinct
> function with column name
>
> will get the 150 unique data/records BUT.... based on output means 150
> records
>
> 2) how we can retrieve all other filed information like how we can
> achieve through SQL.
>
If I understand you correctly, you want to get distinct values of one
column with the LATEST data for the other columns from those records
So, if you have a table like
CREATE TABLE mytable (id BIGSERIAL PRIMARY KEY, val VARCHAR, datetime
TIMESTAMP WITH TIME ZONE);
and you want to get all the data for each distinct value of 'val', you
could do
SELECT * FROM mytable INNER JOIN (SELECT MAX(id) AS id FROM mytable
GROUP BY val) USING (id);
The 'GROUP BY' gives you the distinct values; the MAX(id) gives you the
IDs of the latest records containing those distinct values, so the JOIN
just gives you those latest records
Paul
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos Alves | 2024-04-25 15:11:16 | Re: pg_basebackup Failed |
Previous Message | Sunil Jadhav | 2024-04-25 14:58:58 | Required distinct record with all fields |