How to Specify SQL Sort Order in SQL Query

How to sort order by number in SQL?

I am no expert in using regular expression , however using basic one we could achieve it as bellow,

with cte as
(
select '200 DAVID' name
union all
select '100 JOHN'
union all
select '100-1 SHAWN'
union all
select '100-1/1 PETER'
union all
select '100-1/1/1 ALVIS'
union all
select '100-1/1/3 PIRES'
union all
select '100-1/1/10 ROBERT'
union all
select '100-1/1/11 HENRY'
union all
select '100-1/2 BILLY'
union all
select '100-1/2/1 JIOUS'
)
select *
  from(select t.*,cast(regexp_substr(name,'[0-9]+') as unsigned) col1
                 ,cast(regexp_substr(name,'[0-9]+',1,2) as unsigned) col2
                 ,cast(regexp_substr(name,'[0-9]+',1,3) as unsigned) col3
                 ,cast(regexp_substr(name,'[0-9]+',1,4) as unsigned) col4
  from cte t) c
order by col1,col2,col3,col4

First we extract the numeric required for ordering from the string using regexp_substr(name,'[0-9]+') which matches first numeric one more character and returns the first occurrence and with regexp_substr(name,'[0-9]+',1,2) the second occurrence from one more numeric character match and so on...... and then use accordingly in the order by clause.

Demo

Edit:- Solution for MYSQL older versions

select t.id,t.name
from
(
select t.*, cast((case when col1_col2_ref > 0 
                      then 
                        substring_index(modified_name,'-',1)
                  else 
                     modified_name
                  end) as unsigned) col1
          , cast((case when    col1_col2_ref > 0
                           and col3_ref > 0
                       then 
                          substr(modified_name,(col1_col2_ref + 1),(col3_ref - (col1_col2_ref + 1)))
                       when col1_col2_ref > 0
                       then 
                         substr(modified_name,(col1_col2_ref + 1))
                  end) as unsigned) col2
          , cast((case when    col3_ref > 0
                           and col4_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1),(col4_ref - (col3_ref + 1)))
                       when col3_ref > 0
                       then 
                         substr(modified_name,(col3_ref + 1))
                  end) as unsigned) col3
          , cast((case when col4_ref > 0
                        then 
                         substr(modified_name,(col4_ref + 1))
                   end) as unsigned) col4
  from
(
select t.*,substring_index(name,' ',1) modified_name
          ,locate('-',name,1) col1_col2_ref
          ,locate('/',name,1) col3_ref
          ,locate('/',name,locate('/',name,1)+1) col4_ref
  from test t
) t
) t
order by col1,col2,col3,col4

Demo2

Explicitly specify sort order for mysql query?

Actually, you were surprisingly close. It's a simple as:

select * from TABLE order by field(ID,4,2,5,3,1)

TSQL - Is it possible to define the sort order?

It's incredibly clunky, but you can use a CASE statement for ordering:

SELECT * FROM Blah 
ORDER BY CASE MyColumn 
    WHEN 'orange' THEN 1 
    WHEN 'apple' THEN 2 
    WHEN 'strawberry' THEN 3 
    END 

Alternately, you can create a secondary table which contains the sort field and a sort order.

TargetValue  SortOrder
orange       1
apple        2
strawberry   3

And join your table onto this new table.

When no 'Order by' is specified, what order does a query choose for your record set?

If you don't specify an ORDER BY, then there is NO ORDER defined.

The results can be returned in an arbitrary order - and that might change over time, too.

There is no "natural order" or anything like that in a relational database (at least in all that I know of). The only way to get a reliable ordering is by explicitly specifying an ORDER BY clause.

Update: for those who still don't believe me - here's two excellent blog posts that illustrate this point (with code samples!) :

• Conor Cunningham (Architect on the Core SQL Server Engine team): No Seatbelt - Expecting Order without ORDER BY
• Alexander Kuznetsov: Without ORDER BY, there is no default sort order (post in the Web Archive)

How can I specify sql sort order in sql query

It's actually quite easy. Here's an example to sort your query by the "subject":

SELECT subject, sender_list, date, uid
  FROM messages
 WHERE folder_id = 3
 ORDER BY subject ASC

This will order your list A-Z. If you want to order your list Z-A then use:

ORDER BY subject DESC

Sorting in SQL query in ascending order

You must do this :

    select * from temp
    order by 
    left(columnA,2) asc ,
    right(columnA,4) asc,      
    cast(replace(replace(columnA,left(columnA,3),''),right(columnA,5),'') as int) asc

And a DEMO here !
So you edited the post and this should work just fine. This is not converting into Date the last part of the string

SQL Select and Sort from Multiple tables

If by display 5 latest/newest products, included from each table you mean 5 latest from the combined result set, a view using a UNION will do the job:

create view testvw as select * from 
(
(select productname,manufacturer,arrivaldate from Clothes)
UNION
(select productname,manufacturer,arrivaldate from Toys)
UNION
(select productname,manufacturer,arrivaldate from Tools)
) x 
order by arrivaldate desc limit 5;

---

Related Topics