Re: [SQLDownUnder] Table Design (Names)

[Rob Farley]

Isn't that the same as:

ORDER BY COALESCE(GivenNames, FamilyName)



On 9/8/06, Peter Ward <peter@xxxxxxxxxxx> wrote:
> The sorting by surname/FamilyName could be achieved by using a CASE statement in the ORDER BY clause as illustrated below.  ie. if FamilyName IS NULL then ORDER BY GivenName
>
> CREATE TABLE Employees
>     (
>  FamilyName NVARCHAR(50) NULL,
>  GivenNames NVARCHAR(50) NOT NULL
>     )
> INSERT Employees SELECT 'Ward', 'Peter'
> INSERT Employees SELECT NULL, 'Rob'
> INSERT Employees SELECT 'Doe', 'John'
> INSERT Employees SELECT 'Smith', 'Mary'
> INSERT Employees SELECT NULL, 'Alice'
>
> SELECT  GivenNames, FamilyName
> FROM  Employees
> ORDER BY
>    CASE
>  WHEN GivenNames IS NULL THEN FamilyName
>  ELSE GivenNames
>    END
>
>
> Peter Ward
> WARDY IT Solutions Chief Technical Architect
> Phone: +61 7 3856 5411      Mobile: +61 0 403 177 761
> WARDY IT Solutions - Specialising in building Microsoft SQL Server Solutions
>
> - The course 'Writing Queries for Microsoft SQL Server' is being held on the 25/09/2006. For more details refer to the course outline <http://www.wardyit.com/courses/WritingQueriesforMicrosoftSQLServer.pdf>
> - Are you attending the September WARDY IT Community Dinner on Monday the 11th of September? Add this event to your Outlook Calendar <http://www.wardyit.com/calendar/SeptemberWARDYITCommunityDinner.ics>
>
> ________________________________
>
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx on behalf of Bill McCarthy
> Sent: Fri 8/09/2006 2:17 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: RE: [SQLDownUnder] Table Design (Names)
>
>
>
> Good points by all :)
>
> So let's say for a basic user details we have :
>
> FamilyName (not required - some people only have one name)
> GivenNames (required)
> And
> FriendlyName or PreferredName (required)
> And
> DisplayName or FullName(required)
>
> Ideally, PreferredName would be pre-populated using the fist part of
> GivenNames.   Is that PC ?
>
> FullName (or DisplayName) would be either GivenNames FamilyName, or
> FamilyName GivenName (omitting the separating space if the first field was
> empty)  Is that PC ?  Are there any other alternatives there (other than
> comma sepertion) ?
>
>
>
> As to sorting by surname, I don't see how you can do that unless you
> actually make that sort by a computed field which is FamilyName or GivenName
> in the absence of FamilyName
>
>
>
>
> to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net <http://www.readify.net/>
>
>
>
>
>
>
>
> to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net