#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    24
    Rep Power
    0

    Hide Redundant Field?


    Here is my sql:
    select
    ResFirst, Reslast, '<a href="mailto:'||eMail||'">'||ResLast||'</a>' as eLast,
    Nickname, Bldg, Unit, Cell, Landline, Birthday from Residents
    Order by ResLast, ResFirst

    <...> is html to include link to the person's email address.

    Works fine, but I want to omit ResLast from the result set.

    eLast already contains the last name, but I cannot sort on that because the entire string is sorted, not just the last name, which is the desired sorting.

    Any ideas?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    43
    Rep Power
    5
    Have you tried omitting the ResLast field from the select list? Because this should work.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    24
    Rep Power
    0
    Originally Posted by tsteinmaurer
    Have you tried omitting the ResLast field from the select list? Because this should work.
    Sure, I actually had the query running without that, but the result set sorts strangely: persons without email addresses have no last names, which is a bit weird. That led me to include last name in the query. I would like to sort on last name without showing it.

    Since my fb supports a web-hosted app, I was actually able to do a dodge in the output generator, which is working OK: sort on the extra last-name field, make the last name "hot" if it has associated an email address, but hide the extra field. Hope that makes sense.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    Originally Posted by jwcane
    Here is my sql:
    select
    ResFirst, Reslast, '<a href="mailto:'||eMail||'">'||ResLast||'</a>' as eLast,
    Nickname, Bldg, Unit, Cell, Landline, Birthday from Residents
    Order by ResLast, ResFirst

    <...> is html to include link to the person's email address.

    Works fine, but I want to omit ResLast from the result set.

    eLast already contains the last name, but I cannot sort on that because the entire string is sorted, not just the last name, which is the desired sorting.

    Any ideas?
    Here is what you want:

    select
    a.ResFirst, a.eLast, a.Nickname, a.Bldg, a.Unit, a.Cell, a.Landline, a.Birthday from
    (select
    ResFirst, Reslast, '<a href="mailto:'||eMail||'">'||ResLast||'</a>' as eLast,
    Nickname, Bldg, Unit, Cell, Landline, Birthday from Residents
    Order by ResLast, ResFirst) a

IMN logo majestic logo threadwatch logo seochat tools logo