CODEWITHSUNDEEP
sqloracle-databaseoracle10gwindow-functions
Tistkle
Tistkle

Reputation: 500

Obtaining min and max of partitioned data with a filter

I've just find this problem in a query: (Sorry, I'll try to simplify the table definitions and shorten the details. If you need some more, please, tell me).

I've a table with (ID, date, status). ID is a FK, date is a date, and status is an int value between 1 and 5. Both date and status allow null values. Repeated values are also allowed.

What I need: Extract one row for each ID, min(date) having an status of 1 or 2, min(date) having an status of 2 (only), max(status)...

I'm completely lost... I'm trying to use 

SELECT
   ID,
   min(date) over (partition by(status)) ?? as min_12_date,
   min(date) over (partition by(status)) ?? as min_2_date,
   max(status) as max_status
FROM table
group by ID

So, the question is: Is this correct? How can I select only the status I want?

Upvotes: 0

Views: 327

Answers (1)

sstan
sstan

Reputation: 36513

If I understand your question correctly, you don't need analytic functions for this. You only need conditional aggregation:

select id,
       min(case when status in (1, 2) then date end) as min_12_date,
       min(case when status = 2 then date end) as min_2_date,
       max(status) as max_status
  from tbl
 group by id

Upvotes: 1

Related Questions