Query selecting Top 12 Results

[pinkpanther6666]

I know how to select the top 12 results in my query where .. Say the 12th result has a value of 6 but the 13th result also has a value of 6 ... But my query returns 13 results


How do I only get it return 12 results

Thanks

Steve

 

[Joe4]

How does it know which record to return in the event of a tie like that?
What other fields are being returned by the query?
Can you post a sample of what is being returned, and also the SQL code for the query?

 

[pinkpanther6666]

I'm not at home so can't post a copy of my query at the moment ... But when when I return I will post a copy

 

[pinkpanther6666]

Hi,

My code is listed below





SELECT TOP 12 Main.Team, Main.Name, Sum(([Main].[DD]+[DS])+[DO]+[53]) AS AllRounders
FROM Main
GROUP BY Main.Team, Main.Name
ORDER BY Sum(([Main].[DD]+[DS])+[DO]+[53]) DESC;


Hope you can help

Cheers


Steve

 

[Joe4]

OK, you addressed two of the four things I requested. Can you address the other two (highlighted in red)?

How does it know which record to return in the event of a tie like that?
What other fields are being returned by the query?
Can you post a sample of what is being returned, and also the SQL code for the query?

In regards to the first one, it is more a logic question for you. In the event of a tie, like you mention, how do you make the determination of which record to return and which not to?

 

[pinkpanther6666]

joe

Thanks for your reply

How does it know which record to return in the event of a tie like that?

Basically it doesnt but the when the query is in a report it puts teh score highest value first then down to the lowest value and then the name is sorted alphabetically

Can you post a sample of what is being returned,

Name Score
Player 1 25
Player 2 24
Player 3 23
Player 4 21
Player 5 19
Player 6 16
Player 7 15
Player 8 14
Player 9 12
Player 10 9
Player 11 7
Player 12 6
Player 13 6

Hope this helps



Steve

 

[Joe4]

It looks like if you add in a secondary sort field that does NOT end in a tie, it will work (so if you have a primary key or a unique, field that would be a good one to use). Since you are grouping on Team and Name, if we add those fields to the sort, that will work (as Grouping on them guarantees a unique combination in your query).

So that code would just look like:

SELECT TOP 12 Main.Team, Main.Name, Sum(([Main].[DD]+[DS])+[DO]+[53]) AS AllRounders
FROM Main
GROUP BY Main.Team, Main.Name
ORDER BY Sum(([Main].[DD]+[DS])+[DO]+[53]) DESC, [COLOR=#0000ff]Main.Team, Main.Name[/COLOR];

 

[pinkpanther6666]

Joe

Many Thanks and i will try the code you have posted



Cheers


Steve

 

[pinkpanther6666]

Solved Re: Query selecting Top 12 Results

Joe

Just tried the code and it works beautifully


Many thanks for your help

Steve

 

[Joe4]

Re: Solved Re: Query selecting Top 12 Results

You are welcome!