그룹별 상위 n 데이터 추출

CREATE TABLE test(id INT IDENTITY, acc INT, tabID INT, valueID INT)
INSERT test (acc, tabID, valueID) VALUES (1, 1, 30)
INSERT test (acc, tabID, valueID) VALUES (1, 2, 31)
INSERT test (acc, tabID, valueID) VALUES (1, 2, 32)
INSERT test (acc, tabID, valueID) VALUES (1, 1, 33)
INSERT test (acc, tabID, valueID) VALUES (1, 1, 34)
INSERT test (acc, tabID, valueID) VALUES (1, 2, 35)
INSERT test (acc, tabID, valueID) VALUES (2, 1, 40)
INSERT test (acc, tabID, valueID) VALUES (2, 2, 41)
INSERT test (acc, tabID, valueID) VALUES (2, 2, 42)
INSERT test (acc, tabID, valueID) VALUES (2, 1, 43)
INSERT test (acc, tabID, valueID) VALUES (2, 1, 44)
INSERT test (acc, tabID, valueID) VALUES (2, 2, 45)
INSERT test (acc, tabID, valueID) VALUES (3, 1, 50)
INSERT test (acc, tabID, valueID) VALUES (3, 2, 51)
INSERT test (acc, tabID, valueID) VALUES (3, 2, 52)
INSERT test (acc, tabID, valueID) VALUES (3, 1, 53)
INSERT test (acc, tabID, valueID) VALUES (3, 1, 54)
INSERT test (acc, tabID, valueID) VALUES (3, 2, 55)


SELECT * FROM test WHERE acc = 2 AND tabID = 1 ORDER BY valueID DESC

SELECT * FROM test a WHERE id in
(SELECT TOP(2) id FROM test WHERE tabID = a.tabID and acc = 2 ORDER BY valueID DESC)
ORDER BY tabID, valueID DESC

댓글 없음: