Oracle SQL Select Matching Query

Question

I have this table below

uid    rid   time_type    date_time

a11    1     1            5/4/2013 00:32:00 (row1)
a43    2     1            5/4/2013 00:32:01 (row2)
a68    2     2            5/4/2013 00:32:02 (row3)
a98    2     1            5/4/2013 00:32:03 (row4)
a45    2     1            5/4/2013 00:32:04 (row5)
a94    1     2            5/4/2013 00:32:05 (row6)
a35    2     2            5/4/2013 00:32:07 (row7)
a33    2     2            5/4/2013 00:32:08 (row8)

Can I use a normal select query to extract the data such that it becomes

uid    rid   time_type    date_time

a11    1     1            5/4/2013 00:32:00 (row1)
a94    1     2            5/4/2013 00:32:05 (row6)
a43    2     1            5/4/2013 00:32:01 (row2)
a68    2     2            5/4/2013 00:32:02 (row3)
a98    2     1            5/4/2013 00:32:03 (row4)
a35    2     2            5/4/2013 00:32:07 (row7)
a45    2     1            5/4/2013 00:32:04 (row5)
a33    2     2            5/4/2013 00:32:08 (row8)

The logic is that time_type of 1 needs to be paired with the next corresponding time_type 2 for the same rid. Can this be done?

I don't think it can be done with the current data as the combination or rid and time_type are not unique in anyway. — Phil
– Phil, Commented Jul 18, 2013 at 18:32
I don't understand the output for the rid=2 case. You have multiple pairs of row with time_type = 1,2. How do you know how to pair them up? This is ambiguous. — OldProgrammer
– OldProgrammer, Commented Jul 18, 2013 at 18:32
I don't see it as ambiguous. time_type=1 is ordered by date_time, then paired up with the next time_type=2 with least date_time following. — 000
– 000, Commented Jul 18, 2013 at 18:49

Nick Krasnov · Accepted Answer · 2013-07-19 09:37:46Z

You could try this approach:

-- sample of data from the question
SQL> with t1(uid1, rid, time_type, date_time) as
  2  (
  3    select 'a11',  1, 1, '5/4/2013 00:32:00' from dual union all
  4    select 'a43',  2, 1, '5/4/2013 00:32:01' from dual union all
  5    select 'a68',  2, 2, '5/4/2013 00:32:02'  from dual union all
  6    select 'a98',  2, 1, '5/4/2013 00:32:03'  from dual union all
  7    select 'a45',  2, 1, '5/4/2013 00:32:04'  from dual union all
  8    select 'a94',  1, 2, '5/4/2013 00:32:05'  from dual union all
  9    select 'a35',  2, 2, '5/4/2013 00:32:07'  from dual union all
 10    select 'a33',  2, 2, '5/4/2013 00:32:08'  from dual
 11  ) -- the query
 12  select uid1
 13       , rid
 14       , time_type
 15       , date_time
 16    from (select uid1
 17               , rid
 18               , time_type
 19               , date_time
 20               , row_number() over(partition by rid, time_type order by rid) as rn
 21            from t1
 22          )
 23  order by rid, rn, time_type
 24  /

Result:

UID1        RID  TIME_TYPE DATE_TIME
---- ---------- ---------- -----------------
a11           1          1 5/4/2013 00:32:00
a94           1          2 5/4/2013 00:32:05
a43           2          1 5/4/2013 00:32:01
a68           2          2 5/4/2013 00:32:02
a98           2          1 5/4/2013 00:32:03
a35           2          2 5/4/2013 00:32:07
a45           2          1 5/4/2013 00:32:04
a33           2          2 5/4/2013 00:32:08

8 rows selected

SQLFiddle Demo

Collectives™ on Stack Overflow

Oracle SQL Select Matching Query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related