开发者

Oracle query distinct records issue

开发者 https://www.devze.com 2023-02-10 05:51 出处:网络
I have a table with the following records seqNodesctypeIDstatusIDGroupSeqNo 1test203016 2test1214216 3test2204317

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
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号