Query select rows based on distinct values of a column

Question

i have a problem for my current query, i wish to get record/result based on a column (example column Status have the value of new/pending/completed for the query i execute if there are 3 record with status new,it should be filter to show 1 record with status new only). Below is my current query which get duplicate column .

select a.CW_UPD_TMS,
          case when a.CW_CRT_UID='AAA' then 'BBB'
               else a.CW_CRT_UID end as CW_CRT_UID,  
          COALESCE(b.CW_S_BR, a.CW_S_BR) as CW_S_BR,
          a.CW_TRX_STAT as STATUS,   
          SUBSTR(a.CW_UPD_TMS,7,2) as day,
          SUBSTR(a.CW_UPD_TMS,5,2) as month,
          SUBSTR(a.CW_UPD_TMS,1,4) as ayear,   
          SUBSTR(a.CW_UPD_TMS,9,2) as hours,
          SUBSTR(a.CW_UPD_TMS,11,2) as mins,
          SUBSTR(a.CW_UPD_TMS,13,2) as secs,   
          case when cast(SUBSTR(a.CW_UPD_TMS,9,2) as INT) > 12 then 'PM'
               else 'AM' end as zone   
from  TABLEA  a  
    left outer join TABLEB b on a.CW_CRT_UID = b.CW_S_USR  
where a.CW_TRX_ID = '20150415110000798' 
union  
select a.CW_UPD_TMS,
          case when a.CW_CRT_UID='AAA' then 'BBB'
               else a.CW_CRT_UID end as CW_CRT_UID,  
          COALESCE(b.CW_S_BR, a.CW_S_BR) as CW_S_BR,
          a.CW_TRX_STAT as STATUS,   
          SUBSTR(a.CW_UPD_TMS,7,2) as day,
          SUBSTR(a.CW_UPD_TMS,5,2) as month,
          SUBSTR(a.CW_UPD_TMS,1,4) as ayear,   
          SUBSTR(a.CW_UPD_TMS,9,2) as hours,
          SUBSTR(a.CW_UPD_TMS,11,2) as mins,
          SUBSTR(a.CW_UPD_TMS,13,2) as secs,   
          case when cast(SUBSTR(a.CW_UPD_TMS,9,2) as INT) > 12 then 'PM'
               else 'AM' end as zone   
from  TABLEC  a  
    left outer join TABLEB b on a.CW_CRT_UID = b.CW_S_USR  
where a.CW_TRX_ID = '20150415110000798'

Here is current result:

CW_UPD_TMS          CW_CRT_UID  CW_S_BR STATUS  DAY MONTH   AYEAR   HOURS   MINS    SECS    ZONE
2015062610260746811 happy       KLC     NEW     26  06      2015    10      26      07      AM
2015062610273984711 happy       KLC     NEW     26  06      2015    10      27      39      AM
2015062610275762511 happy       KLC     NEW     26  06      2015    10      27      57      AM

so now how do i change the query so that only show 1 record only (show with min(CW_UPD_TMS)) as now 3 record have same STATUS (New) .

my expected result should be :

CW_UPD_TMS          CW_CRT_UID  CW_S_BR STATUS  DAY MONTH   AYEAR   HOURS   MINS    SECS    ZONE
2015062610260746811 happy       KLC     NEW     26  06      2015    10      26      07      AM

sorry for my poor english.

You don't have to specify DISTINCT when you do a UNION, since the UNION will remove duplicate rows. — jarlh, Jun 30 '15 at 08:53
So if they didn't all have the same status you'd still want to see those rows keeping the row with the earliest timestamp within each group? — shawnt00, Jun 30 '15 at 08:57
Don't store dates or timestamps as character, use DATE or TIMESTAMP data type! — jarlh, Jun 30 '15 at 08:57
Also, `case when b.CW_S_BR is null then a.CW_S_BR else b.CW_S_BR end` can be written as `COALESCE(b.CW_S_BR, a.CW_S_BR)`, which is clearer. — Andy Nichols, Jun 30 '15 at 08:59
Perhaps you need the "oldest" row with NEW status? Or the "newer" one? `DISTINCT` or `UNION` are basically doing their work: the three rows you provided are different. — sblandin, Jun 30 '15 at 09:02
So you want one line per status. Each with its oldest date/time and the CW_CRT_UID of that time, yes? And what is your DBMS? The various DBMS solve this differently. You also want one CW_TRX_ID only, right? — Thorsten Kettner, Jun 30 '15 at 10:34
@ThorstenKettner hi my DBMS is mssql,yes you are right i just want unique status,for CW_TRX_ID i will get the value from my program.(current CW_TRX_ID is set to '20150415110000798') — FruitLai, Jun 30 '15 at 10:44
mssql is Microsoft SQL Server, right? Okay, I will think of a solution... — Thorsten Kettner, Jun 30 '15 at 10:45
@ThorstenKettner yes thank you...by the way you might refer to shawnt00 reply on below i think that 1 is close enough but hit error when execute — FruitLai, Jun 30 '15 at 10:58

score 1 · Answer 1 · answered Jun 30 '15 at 08:59

1

Its expected behaviour in your case. You should come up with another column in addition to "Status" so they could form composite key, which will be unique and you get single record for it.

Either you can have any criteria like date or anything which suits your requirement and use row number attribute to limit result with 1 record.

Let me know if this make sense.

answered Jun 30 '15 at 08:59

Sagar

125
1
15

hi i think your way is work but i not able to change the table design :( – FruitLai Jun 30 '15 at 09:16

shawnt00 · Answer 2 · 2015-06-30T15:31:56.517

This might be a good start but I had to assume that timestamp works as a primary key. You should mention which platform you're on in the question. It might also be cleaner if we understood what was going on with the joins and the union.

select
    d.CW_UPD_TMS, CW_CRT_UID, CW_S_BR, STATUS, day, month, ayear, hours, mins, secs, zone   
from 
(
    select
        a.CW_UPD_TMS, case when a.CW_CRT_UID='AAA' then 'BBB' else a.CW_CRT_UID end as CW_CRT_UID,  
        case when b.CW_S_BR is null then a.CW_S_BR else b.CW_S_BR end as CW_S_BR, a.CW_TRX_STAT as STATUS,   
        SUBSTR(a.CW_UPD_TMS,7,2) as day, SUBSTR(a.CW_UPD_TMS,5,2) as month, SUBSTR(a.CW_UPD_TMS,1,4) as ayear,   
        SUBSTR(a.CW_UPD_TMS,9,2) as hours, SUBSTR(a.CW_UPD_TMS,11,2) as mins,SUBSTR(a.CW_UPD_TMS,13,2) as secs,   
        case when cast(SUBSTR(a.CW_UPD_TMS,9,2) as INT) > 12 then 'PM' else 'AM' end as zone   
    from TABLEA a left outer join TABLEB b on a.CW_CRT_UID = b.CW_S_USR  
    where a.CW_TRX_ID = '20150415110000798' 
    union  
    select
        a.CW_UPD_TMS, case when a.CW_CRT_UID='AAA' then 'BBB' else a.CW_CRT_UID end as CW_CRT_UID,  
        case when b.CW_S_BR is null then a.CW_S_BR else b.CW_S_BR end as CW_S_BR, a.CW_TRX_STAT as STATUS,   
        SUBSTR(a.CW_UPD_TMS,7,2) as day, SUBSTR(a.CW_UPD_TMS,5,2) as month, SUBSTR(a.CW_UPD_TMS,1,4) as ayear,   
        SUBSTR(a.CW_UPD_TMS,9,2) as hours, SUBSTR(a.CW_UPD_TMS,11,2) as mins,SUBSTR(a.CW_UPD_TMS,13,2) as secs,   
        case when cast(SUBSTR(a.CW_UPD_TMS,9,2) as INT) > 12 then 'PM' else 'AM' end as zone   
    from TABLEC a  
    left outer join TABLEB b on a.CW_CRT_UID = b.CW_S_USR  
    where a.CW_TRX_ID = '20150415110000798'
) as d /* data */ inner join
(
    select min(CW_UPD_TMS) as CW_UPD_TMS, CW_TRX_STAT
    from (
        select a.CW_UPD_TMS, a.CW_TRX_STAT
        from TABLEA a
        where a.CW_TRX_ID = '20150415110000798' 
        union  
        select c.CW_UPD_TMS, c.CW_TRX_STAT
        from TABLEC c
        where c.CW_TRX_ID = '20150415110000798'
    ) t0
    group by CW_TRX_STAT
) as r /* representative */
    on r.CW_UPD_TMS = d.CW_UPD_TMS and r.CW_TRX_STAT = d.CW_TRX_STAT

tried your query get this error : Column name 'CW_UPD_TMS' is in more than one table in the FROM list. i think hit when inner join r there. — FruitLai, Jun 30 '15 at 09:27
That just mean you needed to qualify that column in the outermost `select` clause. I guess I had originally been think I was going to write `where in` rather than an inner join... — shawnt00, Jun 30 '15 at 15:34

score 0 · Answer 3 · answered Jun 30 '15 at 10:58

To get the oldest record, you need to rank the records by date. So what you'd do is combine TableA and TableC, give the records a number, with 1 for the oldest per status, then only keep those records ranked 1.

select a.cw_upd_tms,
  case when a.cw_crt_uid='AAA' then 'BBB'
       else a.cw_crt_uid end as cw_crt_uid,  
  coalesce(b.cw_s_br, a.cw_s_br) as cw_s_br,
  a.cw_trx_stat as status,   
  substr(a.cw_upd_tms,7,2) as day,
  substr(a.cw_upd_tms,5,2) as month,
  substr(a.cw_upd_tms,1,4) as ayear,   
  substr(a.cw_upd_tms,9,2) as hours,
  substr(a.cw_upd_tms,11,2) as mins,
  substr(a.cw_upd_tms,13,2) as secs,   
  case when cast(substr(a.cw_upd_tms,9,2) as int) > 12 then 'PM'
       else 'AM' end as zone   
from 
(
  select 
    cw_trx_stat, cw_crt_uid, cw_upd_tms
  from
  (
    select 
      cw_trx_stat, cw_crt_uid, cw_upd_tms,
      row_number() over (partition by cw_trx_stat order by cw_upd_tms) as rn
    from
    (
      select cw_trx_stat, cw_crt_uid, cw_upd_tms
      from tablea where cw_trx_id = '20150415110000798' 
      union all
      select cw_trx_stat, cw_crt_uid, cw_upd_tms
      from tablec where cw_trx_id = '20150415110000798' 
    ) combined
  ) ranked
  where rn = 1
) a
left outer join tableb b on a.cw_crt_uid = b.cw_s_usr;

hi sorry to say that my DBMS is Derby and i run query on Squirrel SQL — FruitLai, Jul 01 '15 at 01:15
Okay, so it's not SQL Server, but Apache Derby. Above query is standard SQL. The most advanced thing in it is the analytic function ROW_NUMBER, which Derby supports as of version 10.4. Have you tried the query? — Thorsten Kettner, Jul 01 '15 at 08:03
hit error during execute - Error: Syntax error: Encountered "partition" at line 22, column 26. — FruitLai, Jul 02 '15 at 02:14
After telling you that ROW_NUMBER is available in Derby as of version **10.4**, I learn from you that you get a syntax error in that line. Can you guess my next question? — Thorsten Kettner, Jul 02 '15 at 08:16

Query select rows based on distinct values of a column

3 Answers3