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 buggin me: > > I have a recordset that contains contact info, nearly all of which is > optional, unfortunately. What I am doing is sorting by the last name, > then the first name, then the email address and plunking these into a > select list. > > What I end up with (in ascending order) is a bunch of email addresses at > the top (those without first or last names in the db), then first names > (no last name in the db) then the neatly ordered first and last names. > > What I'd like is to have the records with a first and last name come > first, then the ones with just first names, then the ones with only an > email address. > > My code looks like this: > > select contactID, (fname + ' ' + lname) as fullname, email > from contacts > where active = 1 > order by lname, fname, email > > would someone kindly throw me a bone? > > Thanks, > Stephen > http://www.mechavox.com > > ____ . The WDVL Discussion List from WDVL.COM . ____ > To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] > Send Your Posts To: [EMAIL PROTECTED] > To set a personal password send an email to [EMAIL PROTECTED] with the words: "set WDVLTALK pw=yourpassword" in the body of the email. > To change subscription settings to the wdvltalk digest version: > http://wdvl.internet.com/WDVL/Forum/#sub > > ________________ http://www.wdvl.com _______________________ > > You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] > To unsubscribe send a blank email to %%email.unsub%% ____ � The WDVL Discussion List from WDVL.COM � ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To set a personal password send an email to [EMAIL PROTECTED] with the words: "set WDVLTALK pw=yourpassword" in the body of the email. To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
