SQL/Query to retrieve top two records from a panel of data?

[Alan.Aylett]

Hi,

I have a panel of data organised as follows:

id1, 1990, 100
id1, 1991, 101
id1, 1992, 100
id1, 1993, 103
id2, 1990, 80
id2, 1991, 90
id2, 1992, 81
id2, 1993, 83
id3..... etc.

Is there any way to -- without re-structuring the data -- write a
query that will return the top two records based on the third column?
Like the max function but for the top two instead of top one. The
desired output would be:

id1, 1993, 103
id1, 1991, 101
id2, 1991, 90
id2, 1993, 83
id3.... etc.

Thanks in advance for your time,

Alan

 

[Wayne-I-M]

Have a look at John Spencer's answer

http://groups.google.com/group/micr...4/8447ac6b127d2f69?lnk=st&q=#8447ac6b127d2f69

 

[James A. Fortune]

Hi,

I have a panel of data organised as follows:

id1, 1990, 100
id1, 1991, 101
id1, 1992, 100
id1, 1993, 103
id2, 1990, 80
id2, 1991, 90
id2, 1992, 81
id2, 1993, 83
id3..... etc.

Is there any way to -- without re-structuring the data -- write a
query that will return the top two records based on the third column?
Like the max function but for the top two instead of top one. The
desired output would be:

id1, 1993, 103
id1, 1991, 101
id2, 1991, 90
id2, 1993, 83
id3.... etc.

Thanks in advance for your time,

Alan

Have a look at my answer to your MULTI-POSTED question:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/7d962eff8ecc8877

James A. Fortune
(e-mail address removed)