Selecting Top Four Rows For Multiple Key Fields

[19FORBES]

I HAVE A TABLE THAT CONTAINS MULTIPLE ROWS OF INFORMATION IN BLOCKS OF TEN ROWS BASED ON A KEY FIELD "CUSIP"

EX.

CUSIP RATE DATE

123 .50 8/1/2002
123 .55 8/2/2002
123 .56 8/3/2002
124 .55 8/1/2002
124 .77 8/2/2002
124 .66 8/3/2002
125 .22 8/1/2002
125 .44 8/2/2002
125 .66 8/3/2002

I AM TRYING TO HAVE A QUERY GO THROUGH AND RETURN SAY THE TOP 2 ROWS OF INFO (CUSIP,RATE,DATE) FOR EACH SPECIFIC CUSIP (123,124,125.ETC)

I TRIED USING :

SELECT TOP 2 CUSIP,RATE,DATE
FROM TBLHOLDINGS

THIS ONLY RETURNS THE TOP 2 FROM THE FIRST BLOCK OF CUSIPS
(123) IS IT POSSIBLE TO LOOP THRU THE OTHERS BLOCKS OF CUSIPS (124,125,ETC..) AND RETURN THEIR TOP 2 AS WELL????

 

[Jon K]

SELECT CUSIP, Rate, [Date]
FROM tblHoldings AS a
WHERE (select Count(*) from tblHoldings where CUSIP=a.CUSIP and [Date] < a.[Date])+1 <=2;


Notice that:-
The criteria (i.e. the Where clause) ranks the records of each group of CUSIPs by Date and retrieves the first two dates. It assumes CUSIP + Date is unique in the table.

Date is a function name and should not be used as field name. It is put in square brackets here just in case Access is confused.

 

[19FORBES]

thanks... is it possible to also sort that by the most recent date....

the top 2 rows for each cusip sorted by the most recent date going down??????

 

[Jon K]

To retrieve the two most recent dates, simply change [Date] < a.[Date] to [Date] > a.[Date] :-


SELECT CUSIP, Rate, [Date]
FROM TableName AS a
WHERE (select Count(*) from TableName where CUSIP=a.CUSIP and [Date] > a.[Date])+1 <=2;

 

[Jon K]

After reading Pat's answer in your other thread (Returning top 2 rows for multiple blocks of info), I think you should do it in the Microsoft way:-

SELECT CUSIP, Rate, [Date]
FROM TableName as a
WHERE [Date] in (select top 2 [Date] from TableName where CUSIP=a.CUSIP order by [Date] desc);


which is more readable than mine.