This SQL 'Order By' Is Not Working Properly

This SQL 'ORDER BY' is not working properly

Try

SELECT test_column 
FROM test_table 
ORDER BY cast(test_column as int)

But you should look into changing the column types to the correct ones.

SQL ORDER BY - Why is it not working here?

You are ordering by quantity. Because it is not aggregated, you are getting an arbitrary value from each group. You need to order by the total. One method is:

order by sum(quantity) desc

However, I would recommend assigning a reasonable alias (one that doesn't need to be escaped) and using that:

select sku,sum(quantity) as total_quantity
from transactions
where DATE(date) between '2017-07-23' and '2017-09-23'
group by sku
order by total_quantity desc;

SQL Order By not working properly

You are storing them as text(nvarchar), that's why you get lexographical order. That means every character is compared with each other from left to right. Hence 4000 is "higher" than 30000 (the last zero doesn't matter since the first 4 is already higher than the 3).

So the correct way is to store it as a numeric value. However, that seems to be impossible since you also use values like 16.000 with 4.1/2"DP. Then i would add another column, one for the numeric value you want to order by and the other for the textual representation.

Sql query with order by not working

The problem is with your GROUP BY clause, you didn't specify which date you want for each one, so it is randomly selecting one, not necessarly the maximum one.

You can find a good explanation about this behaviour here in @mjv answer

Try this query:

SELECT S.staff_id, S.staff_full_name, max(ET.examtime_endtime) as max_endTime
FROM staffs S 
LEFT JOIN examtime ET
 ON (ET.examtime_staffid = S.staff_id)
WHERE S.staff_status =  'active' 
GROUP BY S.staff_id ,
         S.staff_full_name 
ORDER BY S.staff_full_name ASC ,
         max_endTime DESC 

It will select the maximum date for each staff_id and will order by it. In general - it is common to specify all the columns that represent each group(in this case staff_id,staff_full_name) in the GROUP BY clause and all other columns with an aggregation function(AVG/MAX/MIN..) , this will help you avoid this kind of problems in the future.

Order by SQL not working as expected, I don't know what it's doing but I want a simple sort of date ascending

Simply use aggregation functions in the ORDER BY:

ORDER BY MIN(NCPDPREPS.CREATEDON)

sql order by not working with group by only

Remember if a column that doesn't belongs to the grouping key is being referenced without any sort of aggregation so such statement is impossible.
So remember a little formula to came our this problem.

SELECT * FROM 
    (
     SELECT * FROM `table`
     ORDER BY AnotherColumn
    ) t1
    GROUP BY SomeColumn
    ;

Modify your query like this and hope it will work fine!!!.

SELECT * FROM(
SELECT DISTINCT(item_id),balance
FROM `stock_activity` 

ORDER BY(activity_id) DESC
) t1
GROUP BY (item_id)

sql order by not working with decimal(18,6)

There is no ORDER BY clause in your query.

The ORDER BY inside the correlated subquery is useless because that subquery will work only if it returns only 1 row and does not effect the final results.

Try this:

select tw.name, 
       CONCAT_WS(" : ", "Actual Qty", ifnull(round(tb.actual_qty, 2), 0)) actual_qty
from tabWarehouse tw left join tabBin tb
on tb.warehouse = tw.name and tb.item_code = '30440'
where tw.name like '%%%%' 
  and ifnull(tw.company, '') in ('', 'TILE SELECT') 
  and tw.is_group = 0.0
order by ifnull(round(tb.actual_qty, 2), 0) desc limit 0, 20

Also what is this condition:

`tabWarehouse`.`name` like '%%%%' 

SQL error ORDER BY clause Not working

In your united result you have not a mid column

You have only new_id
you should add

select mid, senderid as new_id 
from messenger  where  receiverid=200
union 
select mid, receiverid 
from messenger  where  senderid =200
order by mid  desc

or avoid union just using a single query

select case when senderid = 200 then reciverid else senderid as new_id 
from messenger  
where  receiverid=200 or senderid = 200
order by mid

SQL Query not Working on ORDER BY

Your third sort expression is on Flag. Those values are being sorted alphabetically before the QNumber sort applies. And note that case matters in the ordering as well.

Here's how I would write it:

ORDER BY
    Status DESC, -- might be better to use a case expression
    CASE WHEN Flag IS NOT NULL THEN 0 ELSE 1 END,
    QNumber

Since your data in the examples contradicts the data in the screenshot, it's not clear whether you needed to remove the third sort column entirely or just sort by ignoring the case of the text.

---

Related Topics