How to sort top N in Access Query

[nappim11]

Hello,



I just want everyone to know this is an X-post from Reddit where I have not received a response. This post will be updated if I receive a solution there and visa versa. I've been trying to solve my problem by reading other posts and http://allenbrowne.com/subquery-01.html#TopN but I'm not successful at applying this logic to my query.

I usually work in excel, but my data files that I'm using have become very large. I brought the file into access so my computer can better handle it. In my job we have several companies and orders in each company. I need to identify the top 10 orders per company by dollar amount.


In Excel I would make a pivot table, add my fields and then select top 10 from the drop down menu. How can I get the same results in a Query in Access?


Here is a short example of what I'm looking to do:


My table, named "Day2", looks something like this:


Company, Order, Amount
1, 123, $100
1, 124, $250
1, 125, $200
1, 126, $130
2, 223, $250
2, 224, $120
2, 225, $300


How could I make access return me the top 2 orders per company? I'd like my query results to look like:


Company, Order, Amount
1, 124, $250
1, 125, $200
2, 223, $250
2, 225, $300

I'd really love to understand the logic behind the query. I can read the basics of SQL where you SELECT the column FROM the table WHERE this criteria is met. The more complicated sub queries confuse me a little.


Thank you in advance!

[CJ_London]

Ideally you need a uniqueID for each record

SELECT *
FROM myTable
WHERE uniqueID in (SELECT TOP 2 uniqueID FROM myTable AS T WHERE Company=myTable.Company ORDER BY Amount Desc)

[nappim11]

I was going to use Order as my uniqueID. Do I need another field to be my uniqueID?

Just curious how come you give myTable an aliases of T, but don't use T anywhere? I'm still new to SQL and trying to understand the parts of it.

[June7]

Did it work? I think need to use the alias:

SELECT Orders.*
FROM Orders
WHERE OrderID in (SELECT TOP 2 OrderID FROM Orders AS T WHERE T.Company=Orders.Company ORDER BY T.Amount DESC, T.OrderID DESC)

[CJ_London]

I was going to use Order as my uniqueID. Do I need another field to be my uniqueID?

if it is unique, then that is OK

SELECT *
FROM myTable
WHERE OrderNo in (SELECT TOP 2 OrderNo FROM myTable AS T WHERE Company=myTable.Company ORDER BY Amount Desc)

Note that Order is a reserved word so if this is the name of your field - change it to something else

@June - you don't need to use the T alias - not specifying it defaults to the aliased table, but you can use it if you want

Just curious how come you give myTable an aliases of T, but don't use T anywhere?

Can use anything you like, doesn't have to be T - in this case it stands for Temp but you can use anything you like. I usually alias tables just to keep the typing down and make it more readable

SELECT [This table I made earlier].field1, [This table I made earlier].field2, [This table I made earlier].field3
FROM [This table I made earlier] where [This table I made earlier].id>1

or

SELECT A.field1, A.Field2 ,A.field3
FROM [This table I made earlier] AS A where A.id>1

You need to use aliasing in any query where the same table is used more than once - if you start a new query and drag the same table into the query grid twice, Access will automatically alias the second instance by adding _1

[nappim11]

@June7

Thank you this worked!!!

I'm still trying to understand the logic behind this. I'm usually very good at Excel, but the SQL in Access makes me feel like a toddler taking his first steps . What I understand is:

The query is selecting all fields from my table where (it is selecting the top 2 orderIDs... then I get lost). How does it know to get two per company???

Thanks again.

[nappim11]

@ Ajax

Thank you for your explanations, they were very helpful. I could never figure out why all these sub queries used an aliasing, but now I do. I'm still a little confused at how the logic works. Would you be able to break down the WHERE part for me that tells the query how to choose two orderIDs per company? I'm very good at understanding Excel and if() statements, but this logic loses me a little. I'd really like to understand it.

Thanks again!

[June7]

This particular nested query has always been a bit of a mystery to me as well. I just try not to think too much about it. If I'd never seen Allen's tutorial, never would have figured it out myself. Really, seems the only time I've ever dealt with it is helping posters. Don't recall ever having a need for it in any db I have built.

Try this, take the SQL statement for the inner query and create a query object then view it in Design view and Datasheet view. The inner query includes the same table twice but without a JOIN clause - this results in a Cartesian relationship where every record of each table is associated with every record of other table. View it with and without the TOP N parameter. With and without the WHERE clause. What do you see?

[nappim11]

@ June7

That is a very smart way to think of it. When I learn complicated formulas in excel I always break them down piece by piece to see what they are returning. I will do the same for access.

Do you know where to find the sample DB used in Allen's tutorial? I was checking on my mobile for it yesterday, but wasn't able to find it.

Thank you again for all your help!

[June7]

What sample DB? There is not one referenced in the Subquery tutorial. There is one in the Search tutorial referenced by Allen. http://allenbrowne.com/ser-62.html

[CJ_London]

you need to do a tutorial on subqueries

breaking this down

SELECT *
FROM myTable
WHERE OrderNo in (SELECT TOP 2 OrderNo FROM myTable AS T WHERE Company=myTable.Company ORDER BY Amount Desc)

the bit in red is a query in itself - and selects the top 2 records for a given company. If you copied and pasted it into another query and ran it, you would be prompted for 'myTable.company'

So the bit in black provides myTable.company so the whole query can run

[June7]

Sorry, Ajax is correct. This nested query cannot run independently. Still twists my brain trying to understand the logic.

[nappim11]

I was able to get the query to work on the small sample, but when I apply it to my DB with 600,000 records it won't return .

Here is my query:

SELECT Day2.COMPANY, Day2.WORK_ORDER_NO, Day2.T107_CHARGES
FROM Day2
where day2.T107_CHARGES in
(
select top 2 T107_CHARGES
from day2 as temp
where temp.company = day2.company
order by temp.T107_CHARGES DESC, temp.WORK_ORDER_NO DESC
)
;


I did post this same question on the MS Access thread because I was panicking and was going to call them. Just FYI.. I didn't want anyone to get mad at X-Post.

Thanks again for all the help!

[June7]

I have read that users have issues with nested queries on large datasets. I don't have any datasets that large so can't verify by experience.

[CJ_London]

ensure all relevant fields are indexed - looks like T107_CHARGES and WORK_ORDER_NO

Also, there is no benefit so far as I can see in matching on the charges column - if you have two works orders with the same charges you will get three records