Results 1 to 14 of 14
  1. #1
    Claudio is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4

    Wrong results with SELECT TOP

    I have the following query:



    SELECT TOP 3 * FROM Tabla1
    WHERE [Grupo]=1
    ORDER BY [Rank] DESC
    UNION ALL
    SELECT TOP 3 * FROM Tabla1
    WHERE [Grupo]=2
    ORDER BY [Rank] DESC;

    The problem is that results for the first part Grupo = 1 are right, but for Grupo=2 are wrong. I mean, in ghe first case, I get the 3 records with highest [Rank] in Grupo=1, but not in the second case.

    Any ideas to solve this are most welcome.

    Thanks

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Do you get the correct result for Grupo=2 when that query is run on its own?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As far as I know only the last ORDER BY clause is valid in a UNION query. Maybe this will work for you?

    http://allenbrowne.com/subquery-01.html#TopN
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, what am I missing? Why isn't this just
    SELECT TOP 3 * FROM Tabla1 WHERE [Grupo]=1 OR [Grupo] = 2
    ORDER BY [Rank] DESC

    or maybe WHERE [Grupo] IN (1,2)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Micron View Post
    OK, what am I missing? Why isn't this just
    SELECT TOP 3 * FROM Tabla1 WHERE [Grupo]=1 OR [Grupo] = 2
    ORDER BY [Rank] DESC

    or maybe WHERE [Grupo] IN (1,2)
    Presumably the goal is the top 3 1's and the top 3 2's. That would produce the top 3 overall. Unless I'm having brain cramps.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think the OP wants 6 records, the top three for group 1 and the top 3 for group 2; yours would return the top 3 records for group 1 or 2 combined.

    Claudio, can you save the individual queries as qryTop3Grupo1 and qryTop3Grupo2 and do your union on them instead?

    Cheers,
    Vlad

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It was too simple. I better start cracking open some wobbly pops, I guess.


    Would have to try a Group By on some field to know if that would help.
    Or I could just take one foot out of my mouth and put the other one in by suggesting it. Wait! There's probably room for both...

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Gicu View Post
    Claudio, can you save the individual queries as qryTop3Grupo1 and qryTop3Grupo2 and do your union on them instead?
    I'd expect that to return the same result as Allen's method. It's probably simpler for a beginner, but not dynamic.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Micron View Post
    It was too simple. I better start cracking open some wobbly pops, I guess.
    I had to search on that one, though I suppose the smiley should have been a tipoff.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's funny, coming from a wino (moderator)!

  11. #11
    Claudio is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4

    Thanks for your answer.

    I had a problem with my computer and was unable to answer before. Sorry

    It's pretty strenge.
    If i change the Grupos order, I mean first number 2 or number 3, whichever goes first gets the rignt result. The others are wrong.

    Individually I get the right results also.


    Quote Originally Posted by isladogs View Post
    Do you get the correct result for Grupo=2 when that query is run on its own?

  12. #12
    Claudio is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4
    I had a problem with my computer and was unable to answer before. Sorry

    Paulīs query works!!! Thanks

    However I still do not know why the other fails.

  13. #13
    Claudio is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4
    Thanks. That works too.
    Quote Originally Posted by Gicu View Post
    I think the OP wants 6 records, the top three for group 1 and the top 3 for group 2; yours would return the top 3 records for group 1 or 2 combined.

    Claudio, can you save the individual queries as qryTop3Grupo1 and qryTop3Grupo2 and do your union on them instead?

    Cheers,
    Vlad

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Claudio View Post
    Paulīs query works!!! Thanks.
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Sum from 2 Tables Results Wrong Values
    By Athar Khan in forum Queries
    Replies: 3
    Last Post: 08-19-2017, 07:14 AM
  2. Replies: 4
    Last Post: 06-20-2017, 12:53 PM
  3. Search form getting wrong results
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 08-22-2013, 06:32 PM
  4. Sum Query Delivers wrong results
    By rbf in forum Queries
    Replies: 5
    Last Post: 03-30-2013, 03:18 PM
  5. Expression gives wrong results
    By newtoAccess in forum Queries
    Replies: 22
    Last Post: 12-03-2010, 12:21 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums