Re: Required distinct record with all fields

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(at)gmail(dot)com> 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(at)pscs(dot)co(dot)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
>>
>>
>>
>>