By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Query - Top 5 Records

P: 7
Hi

I have a table which has a list of suppliers, total spend and what cateogry they fit under.

I want a query to return the top 5 records in terms of total spend, however when I select the top 5 option from the top values drop down box it returns the first 5 records I entered into the table.

Is there any way I can get the query to select the top 5 records in terms of total spend?
Sep 5 '07 #1
Share this Question
Share on Google+
12 Replies


Expert 100+
P: 126
Hi

I have a table which has a list of suppliers, total spend and what cateogry they fit under.

I want a query to return the top 5 records in terms of total spend, however when I select the top 5 option from the top values drop down box it returns the first 5 records I entered into the table.

Is there any way I can get the query to select the top 5 records in terms of total spend?
On the query window, there is an option under each field to "Sort". Under the total spent, change this to "Sort Descending", and this should do the trick.
(If you want to edit the SQL view, you need to add at the end (just before the semi-colon): ORDER BY [Total_Spent] DESC)

Hope this helps.
Sep 5 '07 #2

P: 7
Cheers for that. So simple yet so effective
Sep 5 '07 #3

P: 4
I have a question along a similar path...given the previous scenario, what if you wanted to run a query that would pull the top 5 values by state, not just the top 5 overall values. Any ideas on what that SQL statement would look like? Any help would be very appreciated! Thanks!
Sep 19 '07 #4

Expert 100+
P: 296
I have a question along a similar path...given the previous scenario, what if you wanted to run a query that would pull the top 5 values by state, not just the top 5 overall values. Any ideas on what that SQL statement would look like? Any help would be very appreciated! Thanks!
In your query design view, under State, click the Sort drop down box and choose Ascending. Under your Values field, click the sort drop down box and choose Descending. In SQL it would like like:
ORDER BY [State],[Values];
You could also try right clicking the design grid and adding the Totals row and using the Max function.
Sep 19 '07 #5

P: 4
I gave it a shot, but still no luck...see below for the query in SQL. Can you tell where I went wrong? Thanks again for your help!

Expand|Select|Wrap|Line Numbers
  1. SELECT top 2  tbl_Rates.State, tbl_Rates.Product, tbl_Rates.Bank, tbl_Rates.Rate
  2. FROM tbl_Rates
  3. ORDER BY tbl_Rates.State, tbl_Rates.Rate DESC;
Once again, the query output was just for the top 2 values in Arizona (alphabetically the first state in my table).
Sep 19 '07 #6

Expert 100+
P: 296
I gave it a shot, but still no luck...see below for the query in SQL. Can you tell where I went wrong? Thanks again for your help!

Expand|Select|Wrap|Line Numbers
  1. SELECT top 2  tbl_Rates.State, tbl_Rates.Product, tbl_Rates.Bank, tbl_Rates.Rate
  2. FROM tbl_Rates
  3. ORDER BY tbl_Rates.State, tbl_Rates.Rate DESC;
Once again, the query output was just for the top 2 values in Arizona (alphabetically the first state in my table).
Expand|Select|Wrap|Line Numbers
  1. SELECT top 2 tbl_Rates.Rate,tbl_Rates.State, tbl_Rates.Product, tbl_Rates.Bank, 
  2. FROM tbl_Rates
  3. ORDER BY tbl_Rates.State, tbl_Rates.Rate DESC;
I've never used the TOP predicate, but we'll see if we can get this. I'm going to guess the value you want the Top 2 of is the Rate. Try rearranging the SELECT fields as above...

When you say it doesn't work, what kind of results is it giving you?
Sep 19 '07 #7

Expert 100+
P: 296
Expand|Select|Wrap|Line Numbers
  1. SELECT top 2 tbl_Rates.Rate,tbl_Rates.State, tbl_Rates.Product, tbl_Rates.Bank, 
  2. FROM tbl_Rates
  3. ORDER BY tbl_Rates.State, tbl_Rates.Rate DESC;
I've never used the TOP predicate, but we'll see if we can get this. I'm going to guess the value you want the Top 2 of is the Rate. Try rearranging the SELECT fields as above...

When you say it doesn't work, what kind of results is it giving you?
I'm going to venture a guess and say that my suggestion above won't work.
Here's a snippet from microsoft:
TOP n [PERCENT] Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause. Suppose you want the names of the top 25 students from the class of 1994:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 25
  2. FirstName, LastName
  3. FROM Students
  4. WHERE GraduationYear = 1994
  5. ORDER BY GradePointAverage DESC;
If you do not include the ORDER BY clause, the query will return an arbitrary set of 25 records from the Students table that satisfy the WHERE clause.
The TOP predicate does not choose between equal values. In the preceding example, if the twenty-fifth and twenty-sixth highest grade point averages are the same, the query will return 26 records.
Try just putting State in your order by clause?
Sep 19 '07 #8

P: 4
I tried putting state in my ORDER BY clause, but still no luck. The only thing I get back are the top two vales for the first (alphabetically) state in my table.

I also tried moving the field order around within the SELECT statement so that rate comes before state (see below), but the output for that query was the same as for the previous query...this seems to be a tough one!

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 2 tbl_Rates.Rate, tbl_Rates.State, tbl_Rates.Product, tbl_Rates.Bank
  2. FROM tbl_Rates
  3. ORDER BY tbl_Rates.State , tbl_Rates.Rate DESC;
Sep 19 '07 #9

Expert 100+
P: 296
I tried putting state in my ORDER BY clause, but still no luck. The only thing I get back are the top two vales for the first (alphabetically) state in my table.

I also tried moving the field order around within the SELECT statement so that rate comes before state (see below), but the output for that query was the same as for the previous query...this seems to be a tough one!

Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 2 tbl_Rates.Rate, tbl_Rates.State, tbl_Rates.Product, tbl_Rates.Bank
  2. FROM tbl_Rates
  3. ORDER BY tbl_Rates.State , tbl_Rates.Rate DESC;
Have you tried using Group By?
Sep 19 '07 #10

P: 4
I tried that too, but no such luck. I did pose this question to a co-worker, and he came back with the following. I have to admit that I don't fully understand the query, but it apparently works, so hopefully it will help anyone else who has the same problem. It does not however break ties.

SELECT r.State
, r.Bank
, r.Product
, r.Rate
, r.Rank
FROM (SELECT r1.State
, r1.Bank
, r1.Product
, r1.Rate
, (SELECT count(*) + 1
FROM tbl_Rates AS r2
WHERE r2.Rate > r1.Rate
AND r1.State = r2.State
) AS Rank
FROM tbl_Rates AS r1) AS r
WHERE r.Rank <= 2
ORDER BY r.State
, r.Rank

What it does is ranks rates by State, then it gets the Rank <= 2.

If there is a tie it will get more than 2.

State Bank Rate Rank
CA WAMU 5.20 2
CA BOFA 5.25 1
CA CWB 5.25 1
AZ WAMU 5.20 3
AZ BOFA 5.25 2
AZ CWB 5.27 1
TX WAMU 5.25 1
TX BOFA 5.25 1
TX CWB 5.25 1
TX CWB 5.15 2
CO WAMU 5.30 1
CO BOFA 5.25 2
CO CWB 5.25 2
Sep 19 '07 #11

Expert 100+
P: 296
I tried that too, but no such luck. I did pose this question to a co-worker, and he came back with the following. I have to admit that I don't fully understand the query, but it apparently works, so hopefully it will help anyone else who has the same problem. It does not however break ties.

SELECT r.State
, r.Bank
, r.Product
, r.Rate
, r.Rank
FROM (SELECT r1.State
, r1.Bank
, r1.Product
, r1.Rate
, (SELECT count(*) + 1
FROM tbl_Rates AS r2
WHERE r2.Rate > r1.Rate
AND r1.State = r2.State
) AS Rank
FROM tbl_Rates AS r1) AS r
WHERE r.Rank <= 2
ORDER BY r.State
, r.Rank

What it does is ranks rates by State, then it gets the Rank <= 2.

If there is a tie it will get more than 2.

State Bank Rate Rank
CA WAMU 5.20 2
CA BOFA 5.25 1
CA CWB 5.25 1
AZ WAMU 5.20 3
AZ BOFA 5.25 2
AZ CWB 5.27 1
TX WAMU 5.25 1
TX BOFA 5.25 1
TX CWB 5.25 1
TX CWB 5.15 2
CO WAMU 5.30 1
CO BOFA 5.25 2
CO CWB 5.25 2
Thanks for posting your results. I tried googling this problem for you and it does seem like a complicated one to resolve, so hopefully your solution can help others.
Sep 19 '07 #12

P: 1
I was having the same issue of try in rank top 2 and have no ties.
Below is the VB Code that will loop through running sum the entries.
I set up a table RankTest which I included the State, Product, Lender, Rate, Rank. In the VB Code it will loop through sorting the table properly and give a running sum (aka Rank). Hope this helps.

Function Rank()

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Dim SQL1 As String
Dim SQL2 As String

Dim I As Integer

Set db = CurrentDb()

SQL1 = "SELECT RankTest.State, RankTest.Product FROM RankTest GROUP BY RankTest.State, RankTest.Product;"

Set rs1 = db.OpenRecordset(SQL1)
rs1.MoveFirst
Do While Not rs1.EOF

I = 1

SQL2 = "Select [RankTest].State, [RankTest].Product, [RankTest].Lender, " & _
"[RankTest].rate,[RankTest].rank From [RankTest] " & _
"WHERE [RankTest].state = '" & rs1!State & "' and [RankTest].Product = '" & rs1!product & "' Order by [RankTest].State, [RankTest].Product,[RankTest].rate Desc, [RankTest].Lender;"

Set rs2 = db.OpenRecordset(SQL2)

rs2.MoveFirst

Do While Not rs2.EOF

With rs2
.Edit
!Rank = I
.Update
.MoveNext
End With

I = I + 1

Loop

rs1.MoveNext

Loop

End Function
Oct 2 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.