Professional Web Applications Themes

sort is kinda weird - Microsoft SQL / MS SQL Server

I am not a DBA< so forgive me is this is too easy, but it's not easy for me. Doing ASP, I run this query to build a dropdown list (also known as a <select> box): SELECT EmployeeID, FirstName + ' ' + Lastname FROM Employee ORDER BY FirstName It gives me a long list, most of which is displayed in the correct alphabetical order. However, if there are two different employees with the same first name, it does not display them in the right order. Here is an example: <option value="200" selected>James Williams</option> <option value="195">James Kemp</option> <option value="283">James Kozane</option> ...

  1. #1

    Default sort is kinda weird

    I am not a DBA< so forgive me is this is too easy, but it's not easy for me.

    Doing ASP, I run this query to build a dropdown list (also known as a
    <select> box):

    SELECT EmployeeID, FirstName + ' ' + Lastname FROM Employee
    ORDER BY FirstName

    It gives me a long list, most of which is displayed in the correct
    alphabetical order. However, if there are two different employees with the
    same first name, it does not display them in the right order. Here is an
    example:


    <option value="200" selected>James Williams</option>
    <option value="195">James Kemp</option>
    <option value="283">James Kozane</option>


    What can I do to make it sort by last name after it sorts by first name?


    middletree Guest

  2. #2

    Default Re: sort is kinda weird

    SELECT EmployeeID, FirstName + ' ' + Lastname FROM Employee
    ORDER BY 2


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "middletree" <com> wrote in message
    news:phx.gbl... 
    me. 


    oj Guest

  3. #3

    Default Re: sort is kinda weird


    "middletree" <com> wrote in message
    news:phx.gbl... 
    me. 

    SELECT EmployeeID, FirstName + ' ' + Lastname FROM Employee
    ORDER BY FirstName, LastName


    David


    David Guest

  4. #4

    Default Huh?

    What does the ORDER BY 2 do?

    Bob Castleman
    SuccessWare Software


    "oj" <com> wrote in message
    news:%phx.gbl... 
    > me. [/ref]
    the 
    >
    >[/ref]


    Bob Guest

  5. #5

    Default Re: Huh?

    It orders by the value of the second column of the SELECT list.

    As per Books Online:

    Syntax
    [ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ]

    Arguments
    order_by_expression

    Specifies a column on which to sort. A sort column can be specified as a
    name or column alias (which can be qualified by the table or view name), an
    expression, or a nonnegative integer representing the position of the name,
    alias, or expression in select list.

    Multiple sort columns can be specified. The sequence of the sort columns in
    the ORDER BY clause defines the organization of the sorted result set.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com



    "Bob Castleman" <nomailhere> wrote in message
    news:#phx.gbl... [/ref][/ref]
    for [/ref]
    > the [/ref][/ref]
    an [/ref][/ref]
    name? 
    > >
    > >[/ref]
    >
    >[/ref]


    Kalen Guest

  6. #6

    Default Re: Huh?

    Dang! Thanks. I would never have read the Books Online excerpt and
    understood that I could solve this by putting a 2 in there.


    "Kalen Delaney" <com> wrote in message
    news:phx.gbl... 
    an 
    name, 
    in [/ref]
    > for [/ref][/ref]
    a [/ref][/ref]
    with [/ref][/ref]
    is [/ref]
    > name? 
    > >
    > >[/ref]
    >
    >[/ref]


    middletree Guest

  7. #7

    Default Re: sort is kinda weird

    Thanks!


    "oj" <com> wrote in message
    news:#phx.gbl... 
    > me. [/ref]
    the 
    >
    >[/ref]


    middletree Guest

  8. #8

    Default Re: sort is kinda weird

    You are sorting by FirstName, and this is exactly what SQL-Server does.

    The easiest solution here, is to alias the Firstname+Lastname and order
    on that column.

    SELECT EmployeeID,
    FirstName + ' ' + LastName As FullName
    FROM Employee
    ORDER BY FullName

    Gert-Jan

    middletree wrote: 
    Gert-Jan Guest

Similar Threads

  1. Weird Sort criteria
    By Rathtap in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: July 2nd, 06:43 PM
  2. Weird bug....sort of.
    By Scott_K.@adobeforums.com in forum Adobe Indesign Windows
    Replies: 4
    Last Post: May 10th, 03:10 PM
  3. Replies: 1
    Last Post: September 9th, 07:16 AM
  4. what kinda mouse?
    By Terri Underhill in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 25
    Last Post: September 8th, 03:29 PM
  5. kinda OT video ?
    By Alwinch in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 15th, 08:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139