Hi Stephen,
You can use simple union all .
Ex.
select id, fname + space(1)+isnull(lname,'') as fullname from tablename
where lname is not null
union all
select id, fname + space(1)+isnull(lname,'') as fullname from tablename
where lname is null
Thanks,
Elan
I've got one that's really
select id, fname + space(1)+isnull(lname,'') as fullname from tablename
where lname is not null
union all
select id, fname + space(1)+isnull(lname,'') as fullname from tablename
where lname is null
hi elan
i think i know where you're going with that, but you would have to do
something else
rudy suggested:
select contactID, (fname + ' ' + lname) as fullname, email
from contacts
where active = 1
order by
case when lname is null then '999' else lname end
, case when fname is null then '999' else fname end
, email
however, it's a nice
If you do Union them, does that still allow you to 'order by' the sub queries, or does
it just undo the 'order by'. I'm not sure if that's even the best way, just trying to
understand. I definitely don't get Rudy's example below. Sorry to be so thick.
-Stephen
http://www.mechavox.com
On
If you do Union them, does that still allow you to 'order by' the sub
queries
no
a query that includes a UNION is called a fullselect and its components
are called subselects
the subselects are not allowed to have an ORDER BY, only the fullselect
sometimes you will see this --
select foo
you forgot to mention which database, so i shall assume your database is
standards compliant and supports the CASE structure
select contactID, (fname + ' ' + lname) as fullname, email
from contacts
where active = 1
order by
case when lname is null then '999' else lname
The first thing I'm thinking of is making 2 queries. One with
snippetWHERE fname /snippet and another one with snippetWHERE
fname = /snippet. Thw first one would return everything with a
'name' and the second one without the names.
It seems rude but I can't think of something else for the mo'.
