SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query to get top 2 records


query to get top 2 records

Author
Message
ekknaveen
ekknaveen
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 343
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
WendellB
WendellB
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1968 Visits: 1829
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!
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11528 Visits: 37419
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

ekknaveen
ekknaveen
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 343
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
ekknaveen
ekknaveen
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 343
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
guerillaunit
guerillaunit
Right there with Babe
Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)

Group: General Forum Members
Points: 751 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);
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search