Order by "string not empty"?

[Klaus Petersen]

Hi ng.

I have a varchar field in my table, called Name.

I wanna do a selection, which is ordered by whether this field is empty or
not.

E.g. something like:

SELECT
UserID
ORDER BY
Name <> '';

- - -

How can I accomplish this?

TIA.
Klaus.

[David Portas]

This will put the non-empty rows first:

SELECT userid
FROM SomeTable
ORDER BY name DESC

--
David Portas
SQL Server MVP
--

[Klaus Petersen]

> This will put the non-empty rows first:

SELECT userid
FROM SomeTable
ORDER BY name DESC

It just doesnt seem to be a wise way to do it - why do string sorting when
the only thing needed is whether its empty or not....?

[Hugo Kornelis]

On Tue, 8 Jun 2004 15:21:36 +0200, Klaus Petersen wrote:

This will put the non-empty rows first:

SELECT userid
FROM SomeTable
ORDER BY name DESC

It just doesnt seem to be a wise way to do it - why do string sorting when
the only thing needed is whether its empty or not....?

Hi Klaus,

Maybe because ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 2 END is a lot
harder to read and maintain?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

In addition to Hugo's point, using a CASE expression would require that the
expression be evaluated for every single row. Compare performance of the two
approaches and see which works best for you.

--
David Portas
SQL Server MVP
--