CODEWITHSUNDEEP
oracle-database
junaidp
junaidp

Reputation: 11221

using multiple conditions in one query in oracle

I have this record.

      id           performer           end_time
      300135       testuser            15-OCT-13
      300135       testuser            14-OCT-13
      300135       testuser            12-OCT-13  
      300137       newuser             14-OCT-13
      300137       newuser             18-OCT-13

Now I want to show distinct id's but it will show me all of the Id's because end_time is different . So what i need is to shwo disticnt Id's with the latest dates.

So the result should be 

  id        performer      end_time
  300135    testuser       15-OCT-13  (as 15OCT is the heightest date for 300135)
  300137    newuser        18-OCT-13  (as 18OCT is the heightest date for 300137)

Currently what i have is 

       select distinct id, performer, END_TIME from workstep where workstep_name =       'Review' and status ='W_COMPLETED'   ....?

but this gives me all the 5 records.where as it should be only 2 , as there are only 2 unique id'.

please suggest

Thanks

Upvotes: 0

Views: 52

Answers (2)

vav
vav

Reputation: 4694

When you ask for unique row of 3 columns (id, performer, END_TIME ) you will get rows where combination of all 3 columns is unique (your first data listing)

There is simply not enough conditions to get from your first listing to the second one.

I assume that you want distint IDs, and for that IDs, you want to select record where END_TIME is the latest one. Withing the same ID, performer never changes.

So, put just that in sql (query will return only 1 reqord per ID):

select ID, max(performer), max(END_TIME) 
from workstep
where ID in 
(select distinct ID from workstep where <conditions>)
group by ID

Above query will work if performer is the same for ID. If performer could be different, but you want to display it from row with end_time = max(END_TIME), you will need more (query could return more than 1 record per ID if there is a tie on END_TIME):

select ID, performer, END_TIME from 
from workstep
where (id, end_time) in
(
select ID, max(END_TIME) 
from workstep
where ID in 
(select distinct ID from workstep where <conditions>)
group by ID
)

So, for query above, there is an assumtion that END_TIME is different for every record with the same ID.

Here is a more advanced version that is free from that assumtion (exatly 1 record per ID):

select id, performer, end_time
from (
select ID, performer, END_TIME,
 row_number() over (partition by id order by end_time desc, performer) as No
from workstep
where <conditions>
)
where No = 1

questions?

Upvotes: 1

li-on
li-on

Reputation: 539

You need GROUP BY

select id, performer, max(END_TIME) from workstep where workstep_name = 'Review' and status ='W_COMPLETED' group by id,performer

Upvotes: 2

Related Questions