Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query to get top 2 records Expand / Collapse
Author
Message
Posted Monday, February 13, 2012 5:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:09 AM
Points: 189, Visits: 333
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
Posted Monday, February 13, 2012 10:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 165, Visits: 607
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?


Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Post #1251323
Posted Monday, February 13, 2012 10:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 1,942, Visits: 20,007
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
and remember....every day is a school day
Post #1251330
Posted Tuesday, February 14, 2012 12:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:09 AM
Points: 189, Visits: 333
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
Posted Tuesday, February 14, 2012 1:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 10:09 AM
Points: 189, Visits: 333
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
Posted Wednesday, February 29, 2012 12:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 8, 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 »

Add to briefcase

Permissions Expand / Collapse