Results 1 to 5 of 5

Thread: Show 2nd, 3rd, etc. Result in a Query only
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,783
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

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

    I would be tempted to pull your data into a temp table then apply > http://www.advancesharp.com/question...-in-sql-server
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,111
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

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

    or, second top

    Code:
    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,
    Code:
    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
    Code:
    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
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


  4. #4
    New Member
    Join Date
    Dec 2016
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  5. #5
    MrExcel MVP
    Join Date
    May 2006
    Location
    Excel 2003, Australia
    Posts
    9,111
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

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

    if the random data is an issue, maybe you can write an instance (of fixed values) to a temporary table
    If you've posted a clearly explained question & sample data - both input & corresponding output - that can be copied to Excel, THANK YOU.
    Google can find answers to nearly every question.


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •