Home » SQL & PL/SQL » SQL & PL/SQL » Conditional MIN Function (DB: Oracle 10g, OS:Windows Server 2003)
Conditional MIN Function [message #406623] Fri, 05 June 2009 00:40 Go to next message
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 #406627 is a reply to message #406623] Fri, 05 June 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    data as (
  3      select col_1, data_level, seq,
  4             min(data_level) over (partition by col_1) min_lvl
  5      from test_tab
  6    )
  7  select col_1, min_lvl, min(seq) min_seq
  8  from data
  9  where data_level = min_lvl
 10  group by col_1, min_lvl
 11  order by 1
 12  /
C    MIN_LVL    MIN_SEQ
- ---------- ----------
A          1          1
B          2          1
C          1          3
D          1          2

4 rows selected.

Regards
Michel
Re: Conditional MIN Function [message #406634 is a reply to message #406627] Fri, 05 June 2009 01:47 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Thanks awfully Michel. Works like a charm!!!!! ./fa/2115/0/

SQL> SELECT   col_1, min_lvl, MIN (seq)
  2      FROM (SELECT col_1, data_level, seq,
  3                   MIN (data_level) OVER (PARTITION BY col_1) min_lvl
  4              FROM test_tab)
  5     WHERE data_level = min_lvl
  6  GROUP BY col_1, min_lvl
  7  ORDER BY col_1
  8  /

C    MIN_LVL   MIN(SEQ)
- ---------- ----------
A          1          1
B          2          1
C          1          3
D          1          2

SQL>


It never occured to me to apply MIN Function on data_level. ./fa/1637/0/

Thanks again,
Regards,
Jo
Re: Conditional MIN Function [message #406707 is a reply to message #406634] Fri, 05 June 2009 08:15 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT col_1, min(data_level), min(seq) keep dense_rank first order by data_level
FROM ...
GROUP BY col_1


Ross Leishman
Re: Conditional MIN Function [message #406712 is a reply to message #406707] Fri, 05 June 2009 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice.
Just fixing a small typo:
SQL> SELECT col_1, 
  2         min(data_level) min_lvl, 
  3         min(seq) keep (dense_rank first order by data_level) min_seq
  4  FROM test_tab
  5  GROUP BY col_1
  6  /
C    MIN_LVL    MIN_SEQ
- ---------- ----------
A          1          1
B          2          1
C          1          3
D          1          2

4 rows selected.

Regards
Michel
Re: Conditional MIN Function [message #406781 is a reply to message #406712] Fri, 05 June 2009 17:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yeah, I really need a [CODE=Untested code] ... [/CODE] capability like the QUOTE tag.
Re: Conditional MIN Function [message #406876 is a reply to message #406781] Sun, 07 June 2009 01:08 Go to previous message
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 ./fa/1582/0/ 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



Previous Topic: selelct from a field
Next Topic: ORA-06502: PL/SQL character string buffer too small
Goto Forum:
  


Current Time: Sat Dec 03 08:05:56 CST 2016

Total time taken to generate the page: 0.09460 seconds