Oracle SQL Conditional Select Query [duplicate]

Question

I have this table below

uid    rid   time_type    date_time

a11    1     2            5/4/2013 00:32:00 (row1)
a43    1     1            5/4/2013 00:32:01 (row2)
a68    1     1            5/4/2013 00:32:02 (row3)
a98    1     2            5/4/2013 00:32:03 (row4)
a45    1     2            5/4/2013 00:32:04 (row5)
a94    1     1            5/4/2013 00:32:05 (row6)
a35    1     2            5/4/2013 00:32:07 (row7)
a33    1     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

a43    1     1            5/4/2013 00:32:01 (row2)
a98    1     2            5/4/2013 00:32:03 (row4)
a94    1     1            5/4/2013 00:32:05 (row6)
a35    1     2            5/4/2013 00:32:07 (row7)

The date_time field is in asc order. logic is that time_type '1' needs to be paired with the next time_type '2' of the same rid. If time_type '1' or '2' appears in a group of 2 or more ordered by date_time, I will take the earlier one and ignore the rest.

Can this be done?

This is tagged mysql and Oracle. Which are you using?

Gordon Linoff
– Gordon Linoff

2013-07-23 23:59:56 +00:00
Commented Jul 23, 2013 at 23:59 — Gordon Linoff
– Gordon Linoff, Commented Jul 23, 2013 at 23:59

krokodilko · Accepted Answer · 2013-07-24 00:09:30Z

0

Try this query:

with src as (
  select tst.*,  
         case when time_type <> lag( time_type) over ( partition by rid order by date_time, time_type ) 
            then 1 else 0
         end take_me
  from tst
) 
select * from src where take_me = 1
order by rid, date_time;

Here is an SQL Fiddle demo

answered Jul 24, 2013 at 0:09

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Conditional Select Query [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related