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