filter data in sql

[sonny1974]

i am using oracle 9i and crystal xi.
i posted this on oracle 9i forum too. sorry , not sure which area is the best, thank u.

i have these two rcvr with 3 records a piece, one has a rank of 1,2,3 and other has a rank of 1,3,3 . tran code '11' means qty checked in. 23 means reject, 28 is approved. 00189 had 2 qty and 1 passed and 1 failed, i want to keep that, since the time is the same the rank were both 3,3. but on rcvr 00188 , the rank is 1,2,3 and qty is 4,4,4 , well if my qty of rank 1 matches my qty of rank 2, i dont want to see rank 3. i want to see rank 1, 2, since both are 4 and 4. i want on the next set, i want to see all 3 because qty goes 2,1,1. is there a where statement i can put to fix this?

QTY RCVR TRAN_CODE DATE_TIME1 DATE_TIME2 RANK

4 188 11 12/29/2005 12:49 12/29/2005 12:49 1
4 188 23 2/21/2006 16:41 12/29/2005 12:49 2
4 188 28 3/6/2006 18:11 12/29/2005 12:49 3

2 189 11 12/29/2005 13:29 12/29/2005 13:29 1
1 189 23 2/28/2006 12:59 12/29/2005 13:29 3
1 189 28 2/28/2006 12:59 12/29/2005 13:29 3

 

[r937]

i think the oracle forum is the better choice for this

r937.com | rudy.ca