Re: Required distinct record with all fields

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello Paul,

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@xxxxxxxxx> wrote:
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@xxxxxxxxxx> 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




[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux