Обсуждение: Required distinct record with all fields
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.
Thanks & Regards
Sunil
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
Hi paul,
Thank you so much for your prompt response .
It's very helpful
Thanks
Sunil
On Thu, 25 Apr, 2024, 8:45 pm Paul Smith*, <paul@pscs.co.uk> wrote:
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
You can also use 'distinct on' clause on the specific field and select record based on it
-Shweta
On Thu, 25 Apr 2024, 9:16 pm Sunil Jadhav, <sunilbjpatil@gmail.com> wrote:
Hi paul,Thank you so much for your prompt response .It's very helpfulThanksSunilOn Thu, 25 Apr, 2024, 8:45 pm Paul Smith*, <paul@pscs.co.uk> wrote: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
Hello Paul,
SELECT * FROM mytable INNER JOIN (SELECT MAX(id) AS id FROM mytable
GROUP BY val) USING (id);
As per you shared below query working fine but we required a lattest records from duplicate we have another timestamp column is there based on that date columns we need lattest records how we can achieve this any idea please help.
SELECT * FROM mytable INNER JOIN (SELECT MAX(id) AS id FROM mytable
GROUP BY val) USING (id);
Thanks
Sunil
On Thu, 25 Apr, 2024, 9:16 pm Sunil Jadhav, <sunilbjpatil@gmail.com> wrote:
Hi paul,Thank you so much for your prompt response .It's very helpfulThanksSunilOn Thu, 25 Apr, 2024, 8:45 pm Paul Smith*, <paul@pscs.co.uk> wrote: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