query to get top 2 records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Microsoft Access » Microsoft Access » query to get top 2 records

query to get top 2 records

Rate Topic

Display Mode

Topic Options

Author

Message

ekknaveen

Posted Monday, February 13, 2012 5:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 22, 2012 2:43 AM
Points: 177, Visits: 268

Hi,

I need the top and prior to top records from a table for each country. below is my test data.

can you pleaset tell me how we can get the desired result. In oracle we can get this using lead function, but in MS Access how to get it

table a
col1 col2 col3
us 01/01/2012 10
us 02/02/2012 11
us 03/03/2012 33
gpb 01/01/2012 20
gbp 02/02/2012 21
chf 01/01/2012 30

output should be
us o3/03/2012 33 02/02/2012 11
gbp 02/01/2012 21 01/01/2012 20
chf 01/01/2012 30

Thanks & regards
Naveen

Post #1251126

WendellB

Posted Monday, February 13, 2012 10:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:46 AM
Points: 106, Visits: 405

Unfortunately there is no such function in Access. Two possible approaches you might consider:
1) open a recordset on your data and perform a rank calculation on each record using a VBA procedure and then select only records ranked on or two.
2) try using a self-join (Access allows that) with a combination of Max functions on the appropriate parameter.
Both of those may require that you have a unique key to identify a specific record.
Also, what do you want to do if you have several records of a given type that are all equal?

Post #1251323

J Livingston SQL

Posted Monday, February 13, 2012 10:16 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 1,455, Visits: 14,246

as per your previous post......in Access design view...switch to SQL view...cut and paste following...assuming you have provided correct table/column names...if not modify as necessary

SELECT Q.col1, Last(Q.col2) AS Top1DATE, Last(Q.col3) AS Top1QTY, First(Q.col2) AS Top2DATE, First(Q.col3) AS Top2QTY
FROM (SELECT col1, col2, col3
FROM tableA
WHERE col3 IN

(select top 2 col3 from tableA as S
where s.col1 = tableA.col1
order by col3 desc
)
ORDER BY col1, col3 DESC)  AS Q
GROUP BY Q.col1;

doesnt this work for you ?....didnt see any reply to my post on your first thread

__________________________________________________________________
you can lead a user to data....but you cannot make them think !
__________________________________________________________________

Post #1251330

ekknaveen

Posted Tuesday, February 14, 2012 12:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 22, 2012 2:43 AM
Points: 177, Visits: 268

Thanks very much its working fine, but where there is only 1 record for a group (in this case for chf), it should not display the same record for both

Post #1251694

ekknaveen

Posted Tuesday, February 14, 2012 1:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 22, 2012 2:43 AM
Points: 177, Visits: 268

the input and output should be similar
table a
col1 col2 col3
us 01/01/2012 10
us 02/02/2012 11
us 03/03/2012 33
gpb 01/01/2012 20
gbp 02/02/2012 21
chf 01/01/2012 30

output should be
us o3/03/2012 33 02/02/2012 11
gbp 02/01/2012 21 01/01/2012 20
chf 01/01/2012 30

in this chf is having only 1 record so, in output the the other cols are blank

Post #1251701

guerillaunit

Posted Wednesday, February 29, 2012 12:37 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 8:48 AM
Points: 149, Visits: 329

Four steps

Query "step1":

SELECT a.col1, Max(a.col2) AS MaxOfcol2
FROM a
GROUP BY a.col1;

Query "step2":

SELECT a.col1, Max(a.col2) AS MaxOfcol21
FROM a LEFT JOIN step1 ON (a.col1 = step1.col1) AND (a.col2 = step1.MaxOfcol2)
WHERE (((step1.MaxOfcol2) Is Null))
GROUP BY a.col1;

Query "step3":

select * from step1
UNION ALL select * from step2;

Query "step4":

SELECT a.col1, a.col2, a.col3
FROM step3 INNER JOIN a ON (step3.col1 = a.col1) AND (step3.MaxOfcol2 = a.col2);

Post #1259767

« Prev Topic | Next Topic »

Permissions