Show 2nd, 3rd, etc. Result in a Query only

[Gabbelgak]

Hello, I'm trying to find out how to display only the Nth result in a query. I have a query currently that shows all Categories of question types possible, assigns a random integer and limits the results to the top 5. On my form I want to display each of those results in a separate box.

The query I want to base everything off looks like this currently:
SELECT TOP 5 tblCategories.Category, tblCategories.AutoNumber
FROM tblCategories
ORDER BY Rnd([AutoNumber]);

Getting the top result in the first box on my form I can just do:
SELECT TOP 1 qryCategorySelector.Category
FROM qryCategorySelector
ORDER BY qryCategorySelector.AutoNumber;

and getting the bottom result I just order by DESC and its working.

How do I get the correct result for my 2nd, 3rd and 4th box?

 

[mole999]

I would be tempted to pull your data into a temp table then apply > http://www.advancesharp.com/questions/18687/select-nth-row-from-a-table-in-sql-server

 

[Fazza]

or, second top

SELECT TOP 1 A.CategoryFROM (
SELECT TOP 4 Q.category, Q.autonumber
FROM YourQuery Q
ORDER BY Q.autonumber DESC) A
ORDER BY A.autonumber ASC

and third top,

SELECT TOP 1 A.Category
FROM (
SELECT TOP 3 Q.category, Q.autonumber
FROM YourQuery Q
ORDER BY Q.autonumber DESC) A
ORDER BY A.autonumber ASC

and fourth top

SELECT TOP 1 A.Category
FROM (
SELECT TOP 2 Q.category, Q.autonumber
FROM YourQuery Q
ORDER BY Q.autonumber DESC) A
ORDER BY A.autonumber ASC

 

[Gabbelgak]

I've tried to get both of these to work with no luck. I believe the hickup on yours Fazza is because of the random element I have on the query. Each of those queries will create a new Rnd seed query which can cause me to get duplicate results.

I'm trying to create a very rudimentary version of Jeopardy to help people at the office with studying and am just trying to have it pick and show 5 random categories for me on a single form at once if anyone has an alternative idea to try.

 

[Fazza]

if the random data is an issue, maybe you can write an instance (of fixed values) to a temporary table