Grouping dilema

[philsivyer]

Best shown as an example ..
table_1
type cust_id order_no seq expire_date print_online
4 1234 a5432 1 01/01/07 null
0 1234 a5432 2 02_04_07 print
0 1234 a5432 3 03/04/07 online
1 1234 a5432 4 03/04/07 print
2 2345 b456 1 01/01/07 print
0 2345 b456 2 02_04_07 print
4 6789 b8907 1 01/01/06 null
0 6789 b8907 2 02_04_06 print
0 6789 b8907 3 03/04/06 online

with the above data set I only want to see groups of data where the first line "type" field = 4 and if the following "cust_id" & "order_no" are the same then this is the data I want to see - so, this being the case my resultant data set would be..

type cust_id order_no seq expire_date print_online
4 1234 a5432 1 01/01/07 null
0 1234 a5432 2 02_04_07 print
0 1234 a5432 3 03/04/07 online
1 1234 a5432 4 03/04/07 print
4 6789 b8907 1 01/01/06 null
0 6789 b8907 2 02_04_06 print
0 6789 b8907 3 03/04/06 online

Hope this makes sense and your help much appreciated.

Phil

 

[manmaria]

Do you want all the types or only type=4? If you want only type 4 then why you have type1 and type0 in the result set?

Sorry, not very clear.

 

[manmaria]

If I assume you only want type=4 then the query should be

select A.* from table_1 A
(select cust_id,Order_no from table_1 group by cust_id,Order_no)B
WHERE A.cust_id=B.cust_id
AND A.Order_no=B.Order_no
and A.type=4