Tryin to find top 5 results for 4 different values in a field.

[KaayJaay]

I am trying to figure out the top 5 values, out of 4 different values in a field. In other works if i have apples, oranges, grapes, and pears, i am tryin to find the 5 biggest of each type for a total of 20 results. How do i write a query that does this?

KaayJaay

 

[lespaul]

SELECT TOP 5 * FROM TABLE ORDER BY APPLES
UNION
SELECT TOP 5 * FROM TABLE ORDER BY ORANGES
UNION
SELECT TOP 5 * FROM TABLE ORDER BY GRAPES
UNION
SELECT TOP 5 * FROM TABLE ORDER BY PEARS

Leslie

 

[cdagenais]

SELECT TOP 5 * FROM TABLE ORDER BY APPLES DESC
UNION
SELECT TOP 5 * FROM TABLE ORDER BY ORANGES DESC
UNION
SELECT TOP 5 * FROM TABLE ORDER BY GRAPES DESC
UNION
SELECT TOP 5 * FROM TABLE ORDER BY PEARS DESC;

This will sort from highest to lowest for you

Cindy

http://www.aslconsulting.com/EN/Index.asp

 

[lespaul]

oops, forgot the DESC!!

Leslie

 

[cdagenais]

Actually, that might not work. There is a risk of getting more than 5 records ( I tried it and got 7 )
The results would be more accurate if you created 1 query for each column using:
SELECT TOP 5 Grapes, (SELECT count(*) from TABLE d Where Table1.Grapes <= d.Grapes) AS RowNum
FROM TABLE
ORDER BY Grapes DESC;

And then a final Select query of all 4 queries joined by RowNum.
Then all will be in Decending order and you will always have only 5 columns.

A little more work but worth it in the end.




Cindy

http://www.aslconsulting.com/EN/Index.asp

 

[cdagenais]

Actually, that might not work. There is a risk of getting more than 5 records ( I tried it and got 7 )
The results would be more accurate if you created 1 query for each column using:
SELECT TOP 5 Grapes, (SELECT count(*) from TABLE d Where Table1.Grapes <= d.Grapes) AS RowNum
FROM TABLE
ORDER BY Grapes DESC;

And then a final Select query of all 4 queries joined by RowNum.
Then all will be in Decending order and you will always have only 5 columns.

Cindy

http://www.aslconsulting.com/EN/Index.asp

 

[cdagenais]

ha...that's interesting!!???? 2 for 1 Sorry about that.

Cindy

http://www.aslconsulting.com/EN/Index.asp

 

[KaayJaay]

SELECT TOP 5 * FROM TABLE ORDER BY APPLES DESC
UNION
SELECT TOP 5 * FROM TABLE ORDER BY ORANGES DESC
UNION
SELECT TOP 5 * FROM TABLE ORDER BY GRAPES DESC
UNION
SELECT TOP 5 * FROM TABLE ORDER BY PEARS DESC;

This will sort from highest to lowest for you

Will this query know to look in the size column to check for the 5 biggest of each type?

KaayJaay

 

[cdagenais]

Could you post an example of the values stored in your table and how you would like the query to display them? Just to make sure we can answer this question right for you.


Cindy

http://www.aslconsulting.com/EN/Index.asp

 

[KaayJaay]

field 1 as BOSSID, field 2 as TechID, field 3 as Name, field 3 as count()which counts the amount of jobs

Now i want the query to take 4 hard coded BossID's, and get me the names and count correspoding to the amount jobs the tech has done, of the top five techs for each boss. Let me know if you understand, i confused myself on this reply,
KaayJaay

 

[cdagenais]

SELECT TOP 5 BossID, * FROM Table
WHERE (((BossID)="ABC"))
ORDER BY Count DESC
Union
SELECT TOP 5 BossID, * FROM Table
WHERE (((BossID)="123"))
ORDER BY Count DESC
Union
SELECT TOP 5 BossID, * FROM Table
WHERE (((BossID)="DEF"))
ORDER BY Count DESC
Union
SELECT TOP 5 BossID, * FROM Table
WHERE (((BossID)="456"))
ORDER BY Count DESC;

switch the ABC, 123... with your Boss ID's

Hope that's what you're looking for

Cindy

http://www.aslconsulting.com/EN/Index.asp