I have data like
tableid name status uuid date 1 a none 1 2019-12-02 1 a none 2 2019-12-02 1 a done 4 2019-12-02 2 b none 6 2019-12-02 2 b done 7 2019-12-02 3 c none 8 2019-12-02 if I had multiple rows for one table, I want to select the row of that table which is having status done. if any table doesn't have status like 'done' want to return 'none'.
tableid name status uuid date 1 a done 4 2019-12-02 2 b done 7 2019-12-02 3 c none 8 2019-12-02 
SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY tableid ORDER BY status ASC) as RN, tableid, name, status, uuid, date FROM SAMPLE )T WHERE T.RN =1; CHECK THIS : http://sqlfiddle.com/#!17/562f6b/4
You can try this below script if there are always none and done exists in the Status column-
WITH your_table(tableid,name,status,uuid,date) AS ( SELECT 1,'a','none',1,'2019-12-02' UNION ALL SELECT 1,'a','none',2,'2019-12-02' UNION ALL SELECT 1,'a','done',4,'2019-12-02' UNION ALL SELECT 2,'b','none',6,'2019-12-02' UNION ALL SELECT 2,'b','done',7,'2019-12-02' UNION ALL SELECT 3,'c','none',8,'2019-12-02' ) SELECT tableid,name, MIN(status),MAX(uuid),MAX(date) FROM your_table GROUP BY tableid,name ORDER BY tableid Since you are using Postgres, I would recommend DISTINCT ON, which is generally more efficient than other approaches (and is also much simpler to write):
select distinct on(tableid) t.* from mytable t order by status, date desc The second sorting criteria is there to consistently break the ties on status, if any (ie if there there are several records with status = none and no record with status = done, only, latest record will be picked)
You want distinct on for this, but the correct formulation is:
select distinct on (tableid) t.* from mytable t order by tableid, (status = 'done') desc, (status = 'none') desc, date desc; Your question is unclear on what you want if there are no nones or dones.
If there is at most one done and you want all nones if there is no done, then a different approach is not exists:
select t.* from mytable t where t.status = 'done' or (t.status = 'none and not exists (select 1 from table t2 where t2.tableid = t.tableid and t2.status = 'done' ) ); 