Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Mar 2006
    Posts
    93
    Thanks
    3
    Thanked 0 Times in 0 Posts

    sql question (order by)

    Here's my query :
    Code:
    SELECT TOP 10 topnet_joueurs.id,topnet_joueurs.nom, ((SELECT topnet_stats.moy FROM topnet_stats  WHERE topnet_stats.joueur=topnet_joueurs.id AND topnet_stats.evolution=3)-(SELECT topnet_stats.moy FROM topnet_stats  WHERE topnet_stats.joueur=topnet_joueurs.id AND topnet_stats.evolution=1)) AS moyenne FROM topnet_joueurs ORDER BY 3 DESC
    If i order by 1 or 2 it seems to work but with 3 the results are not correctly ordered.

    Anybody know how come?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Rewriting so the code is readable and simplified...
    Code:
    SELECT TOP 10 
        topnet_joueurs.id,
        topnet_joueurs.nom, 
        (
            (SELECT moy FROM topnet_stats  WHERE joueur=topnet_joueurs.id AND evolution=3)
          - (SELECT moy FROM topnet_stats  WHERE joueur=topnet_joueurs.id AND evolution=1)
        ) AS moyenne 
    FROM topnet_joueurs 
    ORDER BY 3 DESC
    Okay... first question: Why is this in the ASP forum when it's clearly a database question?

    Second question: What DB is it? SQL Server or Access?

    Third question: Aside from the fact that the ORDER BY comes out wrong, does the data result look okay? That is, are the values you are getting in the 3rd column correct?

    Personally, I'd rewrite the query. Like this, in SQL Server:

    Code:
    SELECT TOP 10 
        J.id,
        J.nom, 
        SUM( CASE S.evolution WHEN 3 THEN S.moy WHEN 11 THEN -S.moy ELSE 0 END ) AS moyenne
    FROM topnet_joueurs AS J LEFT JOIN topnet_stats AS S
    ON J.id = S.joueur
    GROUP BY J.id, J.nom
    ORDER BY moyenne DESC
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    Mar 2006
    Posts
    93
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Old Pedant for the response...
    1 - you're right. Should be in database questions
    2 - Access
    3 - data result looks ok. really only the order by that's not working

    Tried your query but blocks on case. I think Case does not work in access, am i correct?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Correct. Access uses IIF instead of CASE WHEN. A little less flexible than CASE WHEN but still usable.
    Code:
    SELECT TOP 10 
        J.id,
        J.nom, 
        SUM( IIF( S.evolution=3, S.moy, IIF( S.evolution=11, -S.moy, 0 ) ) ) AS moyenne
    FROM topnet_joueurs AS J LEFT JOIN topnet_stats AS S
    ON J.id = S.joueur
    GROUP BY J.id, J.nom
    ORDER BY moyenne DESC
    If Access hiccups on that because of NULL values for S.moy, then we can fix it this way:
    Code:
    SELECT TOP 10 
        J.id,
        J.nom, 
        SUM( IIF(ISNULL(S.moy),0,S.moy) * IIF( S.evolution=3, 1, IIF( S.evolution=11, -1, 0 ) ) ) AS moyenne
    FROM topnet_joueurs AS J LEFT JOIN topnet_stats AS S
    ON J.id = S.joueur
    GROUP BY J.id, J.nom
    ORDER BY moyenne DESC
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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