CODEWITHSUNDEEP
mysql
charlie
charlie

Reputation: 481

SQL order by 2 columns

i have a table with 2 columns that contain either text or just 1

I want to execute a simple SELECT * FROM query on the table and order by the 2 columns

Some rows will have one column blank and others will not have any columns blank but i want to display the rows with either one or both columns that contain text at the bottom of my results

Sample Data:

sequence = 1
col1 = 'hello'
col2 = 'hello'

sequence = 2
col1 = ''
col2 = ''

sequence = 3
col1 = 'hello'
col2 = ''

so the above data should display in the order (by sequence):

2
3
1

Upvotes: 1

Views: 142

Answers (4)

Rico_93
Rico_93

Reputation: 108

SELECT  *
FROM    table
ORDER BY 
        col1 ASC
        , col2 ASC

you could even limit it 

SELECT  *
FROM    table
ORDER BY 
        col1 ASC LIMIT 3
        , col2 ASC LIMIT 3

Upvotes: 0

Nicolai Süper
Nicolai Süper

Reputation: 1

You can just do SELECT * FROM tbl ORDER BY col1, col2 ASC/DESC;.

Upvotes: -1

sagi
sagi

Reputation: 40481

You need conditional ordering :

SELECT * FROM YourTable t
ORDER BY CASE WHEN t.col1 = '' THEN 1 ELSE 0 END +
         CASE WHEN t.col2 = '' THEN 1 ELSE 0 END DESC,
         t.col1,t.col2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can explicitly count the number of blanks and use that for ordering:

order by ((col1 = '') + (col2 = '')) desc

This counts the number of values.

Note: if the values are really NULL and not empty strings, then:

order by ((col1 is null) + (col2 is null)) desc

Both of these use the MySQL feature that a boolean expression is treated as an integer in a numeric context, with 1 for true and 0 for false.

Upvotes: 4

Related Questions