Conditional MIN Function [message #406623] |
Fri, 05 June 2009 00:40  |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
Hi,
I am baffled with the following requirement:
-- DDL Statement For Sample Data
CREATE TABLE test_tab AS
(
SELECT 'A' col_1, 1 data_level, 1 seq FROM DUAL
UNION ALL
SELECT 'A' col_1, 2 data_level, 2 seq FROM DUAL
UNION ALL
SELECT 'B' col_1, 2 data_level, 1 seq FROM DUAL
UNION ALL
SELECT 'B' col_1, 2 data_level, 2 seq FROM DUAL
UNION ALL
SELECT 'C' col_1, 2 data_level, 1 seq FROM DUAL
UNION ALL
SELECT 'C' col_1, 2 data_level, 2 seq FROM DUAL
UNION ALL
SELECT 'C' col_1, 1 data_level, 3 seq FROM DUAL
UNION ALL
SELECT 'D' col_1, 2 data_level, 1 seq FROM DUAL
UNION ALL
SELECT 'D' col_1, 1 data_level, 2 seq FROM DUAL
UNION ALL
SELECT 'D' col_1, 2 data_level, 3 seq FROM DUAL
UNION ALL
SELECT 'D' col_1, 1 data_level, 4 seq FROM DUAL
)
/
SQL> SELECT * FROM TEST_TAB
2 /
C DATA_LEVEL SEQ
- ---------- ----------
A 1 1
A 2 2
B 2 1
B 2 2
C 2 1
C 2 2
C 1 3
D 2 1
D 1 2
D 2 3
D 1 4
11 rows selected.
My required output is:
COL_1 DATA_LEVEL SEQ
----- ---------- ----------
A 1 1
B 2 1
C 1 3
D 1 2
Output Explanation:
We have to take the minimum SEQ For each col_1 data based on the following condition:
1. If data_level = 1 is present for a col_1 get the minimum seq for that particular col_1.
2. If data_level = 2 is present for a col_1 get the minimum seq for that particular col_1 only if data_level = 1 is not present for that particular col_1.
The following example might help understand the required output:
C DATA_LEVEL SEQ
- ---------- ----------
C 2 1
C 2 2
C 1 3 <-- Data Level 1 Present Req. O/P SEQ
C DATA_LEVEL SEQ
- ---------- ----------
B 2 1 <-- Data Level 1 Absent Req. O/P SEQ
B 2 2
I tried the following code:
SQL> SELECT col_1, MIN (CASE
2 WHEN data_level = 1
3 THEN seq
4 --WHEN DATA_LEVEL = 2 THEN SEQ
5 END) min_seq
6 FROM test_tab
7 GROUP BY col_1
8 ORDER BY col_1
9 /
C MIN_SEQ
- ----------
A 1
B <-- Can't get this Value.
C 3
D 2
Please help on how to proceed with this.
Thanks & Regards,
Joice
PS: I don't recall the full version number of the database. I don't have access to the database on which the issue was raised. I will post the version number once I have access to it.
|
|
|
|
|
|
|
|
Re: Conditional MIN Function [message #406876 is a reply to message #406781] |
Sun, 07 June 2009 01:08  |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
Thanks Ross.....
I clearly understand the logic you and Michel have used. To be honest my thoughts were fixed on how to apply MIN on SEQ Column....
*Sigh* Sadly the client never gets satisfied. They are bound to change the requirement now. But I think I can work it out on the grounds of solutions Michel and you have provided.
Thanks again....
Have a good day!!!
Regards,
Jo
|
|
|