SQL> SELECT * FROM tab_1;
USER_ID USER_NAME SEQ STATUS
---------- ---------- ---------- ----------
1 A 1
1 B 2 Approve
2 C 1
2 D 2
3 E 1
3 F 2 Approve
3 G 3
SQL> SELECT user_id, user_name, seq, status
2 FROM (
3 SELECT user_id, user_name, seq, status
4 FROM tab_1
5 WHERE status = 'Approve'
6 UNION ALL
7 SELECT user_id, user_name, seq, status
8 FROM (
9 SELECT user_id, user_name, seq, status,
10 MAX(status) OVER (PARTITION BY user_id
11 ORDER BY status) m_stat
12 FROM tab_1
13 )
14 WHERE m_stat IS NULL AND seq = 1
15 )
16 ORDER BY user_id, user_name;
USER_ID USER_NAME SEQ STATUS
---------- ---------- ---------- ----------
1 B 2 Approve
2 C 1
3 F 2 Approve
hth,
AH
> aly rmb <[EMAIL PROTECTED]>
> Sent by: [email protected]
> 23/10/2007 15:13
> Please respond to
> [email protected]
>
> To
> [email protected]
> cc
>
> Subject
> [indo-oracle] Query table
>
> Hi .....
> ada data spt di bawah :
>
> User_id User_name Seq Status
> ---------------------------------------------------------
> 1 A 1
> 1 B 2 Approve
> 2 C 1
> 2 D 2
> 3 E 1
> 3 F 2 Approve
> 3 G 3
>
> yg ingin di hasilkan
> 1 B 2 Approve
> 2 C 1
> 3 F 2 Approve
>
> Klo Statusnya ngga' ada yg Approve, maka Seq = 1 yg akan di tampilkan
>
> Tolong pencerahannya donk ....
>
> Send instant messages to your online friends
http://uk.messenger.yahoo.com
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
>
>
>
>
> Send instant messages to your online friends
http://uk.messenger.yahoo.com
>
> [Non-text portions of this message have been removed]
>
