> 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
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 Tuesd
Thanks Guys! I guess I should have noted that I'm using SQL server. I really liked
the idea of your first solution, Rudy (NULLS LAST), but it isn't supported in MS SQL
Server. Right now I'm leaning toward either splitting it into multiple SQL statements
or using the sort option to put the n
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 techni
> "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
somethin
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
The first thing I'm thinking of is making 2 queries. One with
WHERE fname <> "" and another one with WHERE
fname = "". 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'. I'm
sure somebody on th
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 e
