Oracle query distinct records issue
I have a table with the following records
seqNo desc typeID statusID GroupSeqNo
1 test 20 30 16
2 test1 21 42 16
3 test2 20 43 17
4 test3 20 30 17
5 test4 21 42 18
6 test5 20 43 18
I am joining this table with couple of other tables to get the typedescription and Statusdescription.
My question is how can I display just one record from each GroupSeqNo? If you look at the a开发者_如何学JAVAbove sample records there are 3 records from GroupSeqNo 16, 2 for 17 and 2 for 18.
Below is the query I am using:
SELECT DISTINCT SS.GROUPSEQNO,SS.SEQNO,SS.DESC,T.DESC,S.DESC
FROM STATS SS, DDTYPES T, DDSTATUSES S
WHERE SS.TYPE_ID=T.TYPE_ID AND SS.STATUS_ID=S.STATUS_ID
Join a sub query that groups the STATS
table using GROUP BY
and selects the MIN(seqNo)
or MAX(seqNo)
depending on what you want.
SELECT SS.GROUPSEQNO, SS.SEQNO, SS.DESC, T.DESC, S.DESC
FROM STATS SS
INNER JOIN DDTYPES T ON SS.TYPE_ID = T.TYPE_ID
INNER JOIN DDSTATUSES S SS.STATUS_ID = S.STATUS_ID
INNER JOIN (
SELECT MIN(seqNo) MinID
FROM STATS
GROUP BY GroupSeqNo
) g ON SS.seqNo = g.MinID
精彩评论