Re: Required distinct record with all fields

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
>
>
>
>