9 Replies Latest reply: Sep 1, 2012 5:23 AM by Ashu_Neo RSS

    SQL to get unique record

    884364
      EVENT_ID TRANSFER_ID
      -------------- ------------------
      44745 693420
      44744 693420
      44743 603420
      44742 603420
      44741 543420
      44740 543420
      44739 543421
      44738 543421

      How can I get list of unique TRANSFER_ID?

      EVENT_ID TRANSFER_ID
      -------------- ------------------
      44745 693420
      44743 603420
      44741 543420
      44739 543421
        • 1. Re: SQL to get unique record
          sb92075
          881361 wrote:
          EVENT_ID TRANSFER_ID
          -------------- ------------------
          44745 693420
          44744 693420
          44743 603420
          44742 603420
          44741 543420
          44740 543420
          44739 543421
          44738 543421

          How can I get list of unique TRANSFER_ID?

          EVENT_ID TRANSFER_ID
          -------------- ------------------
          44745 693420
          44743 603420
          44741 543420
          44739 543421
          SELECT DISTINCT EVENT_ID, TRANSFER_ID FROM TABLE_A;
          • 2. Re: SQL to get unique record
            GVR
            One way
            select event_id,transfer_id from(
            select event_id,transfer_id ,rank() over(partition by  transfer_id order by event_id desc ) rnk from table_name
            )where rnk=1
            • 3. Re: SQL to get unique record
              852736
              One way of doing.
              with t as (select 44745 event_id, 693420 transfer_id from dual union all
               select 44744, 693420 from dual union all
               select 44743, 603420 from dual union all
               select 44742, 603420 from dual union all
               select 44741, 543420 from dual union all
               select 44740, 543420 from dual union all
               select 44739, 543421 from dual union all
               select 44738, 543421 from dual )
               select event_id, transfer_id
               from (select row_number() over (partition by transfer_id order by event_id desc) rn, event_id,
               transfer_id
               from t)
               where rn = 1
              /
              • 4. Re: SQL to get unique record
                Frank Kulash
                Hi,
                881361 wrote:
                EVENT_ID TRANSFER_ID
                -------------- ------------------
                44745 693420
                44744 693420
                44743 603420
                44742 603420
                44741 543420
                44740 543420
                44739 543421
                44738 543421

                How can I get list of unique TRANSFER_ID?

                EVENT_ID TRANSFER_ID
                -------------- ------------------
                44745 693420
                44743 603420
                44741 543420
                44739 543421
                Do you want one row of output for each transfer_id (regardless of how many rows in the table have that transfer_id), along with the highest event_id for theat transfer_id?
                That sounds like a job for GROUP BY and MAX:
                SELECT       MAX (event_id)     AS max_event_id
                ,       transfer_id
                FROM       table_x
                GROUP BY  transfer_id
                ;
                 

                I hope this answers your question.
                If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data (if not what youve posted already).
                Explain, using specific examples, how you get those results from that data.
                Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
                See the forum FAQ {message:id=9360002}
                • 5. Re: SQL to get unique record
                  884364
                  What if schema is schema have key value pair and information and I want to get list of unique transfer_id

                  EVENT_ID KEY VALUE
                  -------------- ----------- ----------
                  44745 transfer.id 693420
                  44745 transfer.user ABC
                  44744 transfer.id 693420
                  44744 transfer.user DEF
                  44743 transfer.id 603420
                  44743 transfer.user GHI
                  44742 transfer.id 603420
                  44742 transfer.user JKL
                  44741 transfer.id 543420
                  44741 transfer.user MNO
                  44740 transfer.id 543420
                  44740 transfer.user PQR
                  44739 transfer.id 543421
                  44739 transfer.user STU
                  44738 transfer.id 543421
                  44738 transfer.user VWX

                  I ran the query

                  select distinct value, event_id
                  from table t
                  where t.key = 'transfer.id' order by event_id desc;

                  but still getting duplicate transfer_id.
                  • 6. Re: SQL to get unique record
                    Etbin
                    Maybe you'd be better of pivoting your data first and use Frank's approach on the result
                    NOT TESTED !
                    with
                    the_data(event_id,the_key,the_value) as
                    (select 44745,'transfer.id','693420' from dual union all
                     select 44745,'transfer.user','ABC' from dual union all
                     select 44744,'transfer.id','693420' from dual union all
                     select 44744,'transfer.user','DEF' from dual union all
                     select 44743,'transfer.id','603420 ' from dual union all
                     select 44743,'transfer.user','GHI' from dual union all
                     select 44742,'transfer.id','603420' from dual union all
                     select 44742,'transfer.user','JKL' from dual union all
                     select 44741,'transfer.id','543420' from dual union all
                     select 44741,'transfer.user','MNO' from dual union all
                     select 44740,'transfer.id','543420' from dual union all
                     select 44740,'transfer.user','PQR' from dual union all
                     select 44739,'transfer.id','543421 ' from dual union all
                     select 44739,'transfer.user','STU' from dual union all
                     select 44738,'transfer.id','543421' from dual union all
                     select 44738,'transfer.user','VWX' from dual
                    )
                    select event_id,
                           max(decode(the_key,'transfer.id',the_value)) transfer_id,
                           max(decode(the_key,'transfer.user',the_value)) transfer_user
                      from the_data
                     group by event_id
                    You could use PIVOT ... but no Database version was specified

                    Regards

                    Etbin
                    • 7. Re: SQL to get unique record
                      884364
                      Is that mean if I have 1000000 records I have to write 1000000 select statements?
                      • 8. Re: SQL to get unique record
                        Etbin
                        certainly not, the first factor is there to simulate your table. (rather common practice on this Forum - no need to create table ... & insert into ...)
                        I don't have a Database at hand so it's just there so others might use it as you somehow changed the original question.
                        select event_id,
                               max(decode(the_key,'transfer.id',the_value)) transfer_id,
                               max(decode(the_key,'transfer.user',the_value)) transfer_user
                          from the_data  /* substitute your table name */
                         group by event_id
                        Regards

                        Etbin
                        • 9. Re: SQL to get unique record
                          Ashu_Neo
                          Simple one :-

                          select transfer_id, count(event_id) cnt
                          from table_name
                          group by transfer_id
                          having count(event_id) =1
                          /