ORDER BY

Anon

I have a table that contains the fields fname and lname. When I do order by lname, fname, the order is fine but it puts the records that are missing both a first and last name at the top when I want them at the bottom. Is there something that I can add to the command that would put these records at the bottom?

Anon

why don't you just do ORDER BY lname?

that would solve your problem

Anon

because I want the order to be

Doe, Amy
Doe, John
Smith, Albert
Smith, Charles
Smith, Will

so I have to to an order by lname, fname. For some reaon though, blank firlds are put at the top when ordering.

Anon

try...

ORDER BY lname ASC

this will make it ascending.

Did you try my suggestion?

Anon

Well, excluding fname from the querry doesn't order the first names too which I need. ASC is the default and it places blank values at the top of the list. This is what I'm trying to prevent. I can't just exclued the records that have bland fname and lname values because I need the other fields in the record. I just want the blank ones to be placed at the bottom.
hmmmm

Anon

im guessing here...

ORDER BY lname, fname DESC; ?

[deleted]

Just using ORDER won't cut it, because an empty field always comes before anything that is not empty.

Instead, create a new virtual field based on wether or not the real field is empty, and order by that first.
If the 'empty' fields contain NULL:

SELECT fname, lname,
CASE WHEN fname IS NULL THEN 1 ELSE 0 END as fnameempty,
CASE WHEN lname IS NULL THEN 1 ELSE 0 END as lnameempty
FROM table
ORDER BY lnameempty, fnameempty, lname, fname;

if your fields are not NULL but "" then replace IS NULL with =""

Anon

Hi,

That's a problem because a space comes before an a in sorting order. Perhaps you could use a little trick.

$query="SELECT REPLACE(lname,' ','ZZ') AS mylname, REPLACE(fname,' ','ZZ') AS myfname ORDER BY mylname,myfname";

In your code you can replace the Z's for space's again. I know, its ugly.

greetZ,
Joris

Anon

Thanks for the posts guys. I actually did something similar to the zz thing earlier but I'm trying to get vincent's thing to work because it's cool.

Here's my statement:
select fname, lname, CASE WHEN fname ="" THEN 1 ELSE 0 END as fnameempty, CASE WHEN lname ="" THEN 1 ELSE 0 END as lnameempty FROM address WHERE uname='mark' ORDER BY fnameempty, lnameempty, lname, fname, email;

and here's the error:
ERROR 1064: You have an error in your SQL syntax near 'WHEN fname ="" THEN 1 ELS
E 0 END as fnameempty, CASE WHEN lname ="" THEN 1 ELSE ' at line 1

after I fix this bug, I'd actually like to select all the fields but it seems strange to add something after the select :
select , CASE WHEN...
but I'm not getting and error message with that.

Thank you all for the help so far.

Mark