CODEWITHSUNDEEP
sqlpostgresql
Mamata
Mamata

Reputation: 23

Get one row from multiple rows

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

Upvotes: 2

Views: 78

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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

Upvotes: 0

GMB
GMB

Reputation: 222402

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)

Upvotes: 0

SwapnaSubham Das
SwapnaSubham Das

Reputation: 537

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

Upvotes: 4

mkRabbani
mkRabbani

Reputation: 16908

You can try this below script if there are always none and done exists in the Status column-

DEMO HERE

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

Upvotes: 0

Related Questions