CODEWITHSUNDEEP
sql
user3669523
user3669523

Reputation: 69

SQL order by DESC not working

I am doing a relatively small query with a sub query where I want to sort the sub query results in descending order. But in the result the sub query are not ordered in a descending order. Cannot see a reason why the ORDER BY does not work for me...

my query:

select 

customers.id,
customers.Name,
customers.Surname,

(select ifnull(sum(bets.amount),0)
from bets 
where customers.id=bets.customerId
and bets.date >'2014-06-01'
and bets.date <'2014-06-02'
order by bets.amount DESC
) as '1st_June',

(select ifnull(sum(bets.amount),0)
from bets 
where customers.id=bets.customerId
and bets.date >'2014-06-02'
and bets.date <'2014-06-03'
order by bets.amount DESC
) as '1st_June',


from customers

group by customers.id

I need to have a DESC order because I want to limit 100 so i get the top 100 values. Could someone suggest a way of doing this...?

Upvotes: 0

Views: 8076

Answers (1)

arserbin3
arserbin3

Reputation: 6148

Order By does not carry over past the group by like that.

This should give you what you're looking for instead:

SELECT customers.id
    ,customers.Name
    ,customers.Surname
    ,(
        SELECT ifnull(sum(bets.amount), 0)
        FROM bets
        WHERE customers.id = bets.customerId
            AND bets.date > '2014-06-01'
            AND bets.date < '2014-06-02'
        ) AS First_June
    ,(
        SELECT ifnull(sum(bets.amount), 0)
        FROM bets
        WHERE customers.id = bets.customerId
            AND bets.date > '2014-06-02'
            AND bets.date < '2014-06-03'
        ) AS Second_June
FROM customers
GROUP BY customers.id
ORDER BY First_June DESC
    ,Second_June DESC
LIMIT 100
  • NOTE: (changed to "First_June" instead. beginning a column name with a number has issues in multiple SQL Servers, and you didn't specify which you're using. Oracle, MS SQL, MySql, Postgres, etc)

Upvotes: 2

Related Questions