Professional Web Applications Themes

Ordering by a variable - Microsoft SQL / MS SQL Server

use dynamic sql to do this (I guess ovariable consist of column name) ex: declare o varchar(20) set o= ' name' exec('select top 5 * from MyTable order by ' + o ) -- -Vishal "Amos Cividalli" <com> wrote in message news:#phx.gbl...  variable  a ...

  1. #1

    Default Re: Ordering by a variable

    use dynamic sql to do this (I guess ovariable consist of column name)
    ex:
    declare o varchar(20)
    set o= ' name'
    exec('select top 5 * from MyTable order by ' + o )

    --
    -Vishal

    "Amos Cividalli" <com> wrote in message
    news:#phx.gbl... 
    variable 



    Vishal Guest

  2. #2

    Default Re: Ordering by a variable

    For that sort of thing I think you're going to have to build the SQL
    statement as a string and then execute the string.

    See "execute" in the BOL.


    "Amos Cividalli" <com> wrote in message
    news:#phx.gbl... 
    variable 



    J Guest

  3. #3

    Default Re: Ordering by a variable

    If all the columns you want to order by can be casted to compatible data
    types then you can do it with a CASE statement:

    SELECT TOP 5 *
    FROM MyTable
    ORDER BY
    CASE o
    WHEN 'name' THEN [name]
    WHEN 'somecol' THEN somecol
    END

    Otherwise use dynamic SQL.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  4. #4

    Default Ordering by a variable

    I try the following SQL statement:

    declare o varchar(20)
    set o= ' name'

    select top 5 *
    from MyTable
    order by o

    In return, I get this error message:
    Server: Msg 1008, Level 15, State 1, Line 7
    The SELECT item identified by the ORDER BY number 1 contains a variable
    as part of the expression identifying a column position.
    Variables are only allowed when ordering by an expression referencing a
    column name.


    As far as I can tell, I AM ordering by column name, and not by position.
    What am I doing wrong?
    Thanks, Amos


    Amos Guest

  5. #5

    Default Re: Ordering by a variable

    Thank you, guys. This was very quick.

    I have tried it before with dynamic SQL as most of these replies suggested,
    and it worked fine, of course, but I thought there should be a way to do it
    without executing an 'on the fly' built string. Am I wrong when I understand
    the SQL Server error message as suggesting that my initial ("Order By o")
    should work when the o variable contains a column name? Can someone throw
    some light here?

    Thanks again,
    Amos


    Amos Guest

  6. #6

    Default Re: Ordering by a variable

    Thanks a lot, David.
    This clarify things a bit.




    "David Portas" <org> wrote in message
    news:phx.gbl... 
    SQL. 


    Amos Guest

Similar Threads

  1. table ordering
    By patbegg in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 25th, 12:17 AM
  2. Ordering by date ???
    By TomT in forum ASP Database
    Replies: 21
    Last Post: August 17th, 08:11 PM
  3. Ordering of recordsets
    By Ken Schaefer in forum ASP Database
    Replies: 2
    Last Post: August 20th, 12:45 PM
  4. [PHP] File ordering
    By Lowell Allen in forum PHP Development
    Replies: 1
    Last Post: August 12th, 02:04 PM
  5. Grouping and Ordering
    By Damian in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 16th, 07:08 AM

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