Selecting two records from each Group

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following data
Clm SEQ
43643251220000 998
43643251220000 999
50912166618001 998
50912166618001 999
50912176992003 998
50912176992003 999
50912557629003 998
50912557629003 999
50912634061003 997
50912634061003 998
50912634061003 999
50912829533001 998
50912829533001 999
50912912054002 997
50912912054002 998
50912912054002 999

and and attempting to select the lowest two SEQ for each CLM.

What is the best way to append these records to a new table?
 
Hi,



A possible way is to use a TOP 2


SELECT clm, seq
FROM myTable As a
WHERE seq IN ( SELECT TOP 2 b.seq
FROM myTAble As b
WHERE b.clm=a.clm
ORDER BY b.seq ASC)
ORDER BY clm, seq



another solution is to rank each record, by group


SELECT a.clm, a.seq, COUNT(*) as rank
FROM myTable As a INNER JOIN myTable As b
ON a.clm=b.clm AND a.seq >= b.seq
GROUP BY a.clm, a.seq
HAVING COUNT(*) <= 2
ORDER BY a.clm, a.seq, rank








Hoping it may help,
Vanderghast, Access MVP
 
Back
Top