SQL Order BY

[johnrb87]

Hi all

 

I have a database table which has the following fields;

 

department | firstname | lastname | mydatetime

 

I run the following QUERY at the moment

 

SELECT * FROM `users` ORDER BY lastname DESC

 

I do this type of "ORDER BY" to ensure that anyone who has an empty value for `lastname` appears at the bottom of the list and anyone who has a value for `lastname` would appear at the top of the list

 

What I am stuck with is, I need to keep the query the same (ie: anyone who has a empty value for `lastname` is at the bottom), but I want to order the results by `mydatetime` which is a datetime value written like "2011-07-20 10:20:45"

 

So it would keep rows with no value for `lastname` at the bottom, but it would order all the results using the datetime field `mydatetime`

 

Anyone got any ideas?

[phpSensei]

SELECT * FROM `table_name` ORDER BY `field1` DESC, `field2` ASC

[johnrb87]

that doesn't seem to sort it into date order, it keeps rows with no value for `lastname` at the bottom of the list (which is what I need), but it doesn't sort it correctly into date order as it returns

 

 

2011-08-05 19:19:25

2011-08-05 19:16:29

2011-08-06 00:41:01

2011-08-05 21:52:17

 

any ideas why?

 

thanks

[AyKay47]

first order takes precedence over the second order..

[phpSensei]

edit: ^^ As mentioned.

 

hmm

 

When ordering by more than one column, the second column is only used if the values are identical with the onces in the first column

 

I guess I used the wrong approach for this, let me cook something up for you, otherwise maybe we can get kickstart or someone else in here to help.

 

try this

 

SELECT * FROM `table_name` ORDER BY `field1`, `field2` DESC

 

or try it with ASC

[johnrb87]

Nope that didn't seem to work, thanks for taking time to suggest somethings

[johnrb87]

any ideas anyone??

[AyKay47]

seems like the first suggestion did what you wanted it to, so you want to first sort by last name, then order them by date?

[PFMaBiSmAd]

What value are your empty lastnames? A null or an empty string?

 

Edit: Assuming a NULL value, use this query -

 

SELECT * FROM `users` ORDER BY IF(lastname IS NULL,0,1) DESC, mydatetime

[johnrb87]

thanks, lastname is an empty string value

[PFMaBiSmAd]

Should work -

SELECT * FROM `users` ORDER BY IF(lastname = '',0,1) DESC, mydatetime