Results 1 to 9 of 9

Thread: Opposite of TOP

  1. #1
    Join Date
    Oct 2003
    Posts
    16

    Unanswered: Opposite of TOP

    Is there an opposite of TOP in sqlsvr? Only thing I can think to do is sort the opposite direction and use TOP but I have a big dynamically generated order by clause that I'd need to parse to reverse the sorting.

    I wrote it in C# (http://www.dbforums.com/showthread.p...65#post3579565) but I'm thinking now I need it in T-SQL.

    Does anyone have the code for this or a better way of handling it? TIA

  2. #2
    Join Date
    Nov 2003
    Posts
    48
    You already go the half way,
    I think you can do this in T-SQL and let SQL reverse the sorting for you

    select * from (
    select top 10 * from T order by f1 desc, f2 desc, f3 desc
    ) a
    order by f1, f2, f3
    Last edited by shianmiin; 11-22-03 at 02:26.
    Shianmiin

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Do you mean something like:

    select top x field from table order by field desc

  4. #4
    Join Date
    Oct 2003
    Posts
    16
    Originally posted by rnealejr
    Do you mean something like:

    select top x field from table order by field desc
    Yes but I don't know the OrderBy field list ahead of time (since its passed in as a parameter) so I was looking for some tsql code to parse it and add the "DESC" or "ASC"

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    When you say tsql do you mean you want to make it a stored procedure - otherwise you can dynamically create the statement and change it based on the parameter (which you can do in a stored procedure as well).

  6. #6
    Join Date
    Oct 2003
    Posts
    16
    Originally posted by rnealejr
    When you say tsql do you mean you want to make it a stored procedure - otherwise you can dynamically create the statement and change it based on the parameter (which you can do in a stored procedure as well).
    Yes, a stored proc or maybe a function which I can use like this...
    Code:
    SELECT @REVsql = ReverseOrderBy(@pSQL)
    ...and the ReverseOrderBy function is what I'd like to know how to write.

  7. #7
    Join Date
    Nov 2003
    Posts
    48
    declare @orderby varchar(100)
    declare @ind int

    select @orderby = 'f1 asc, f2 desc, f3 asc'

    select @orderby = replace(@orderby, ' asc', ' asc1')
    select @orderby = replace(@orderby, ' desc', ' asc')
    select @orderby = replace(@orderby, ' asc1', ' desc')

    select @orderby

    will return "f1 desc, f2 asc, f3 desc"
    Shianmiin

  8. #8
    Join Date
    Nov 2003
    Posts
    48
    I wrote the function for you, it should work.

    create function ReverseOrderBy (@orderby varchar(200))
    returns varchar(200)
    as
    begin
    declare @reverse_orderby varchar(100)
    declare @sort_item varchar(100)

    declare @ind_start int
    declare @ind int

    select @ind_start = 1
    select @reverse_orderby = ''
    select @orderby = @orderby + ','
    while (1=1)
    begin
    select @ind = charindex(',', @orderby, @ind_start)

    if @ind = 0
    begin
    break
    end

    select @sort_item = substring(@orderby, @ind_start, @ind - @ind_start)

    if charindex(' asc', @sort_item) > 0
    begin
    select @sort_item = replace(@sort_item, ' asc', ' desc')
    end
    else
    begin
    if charindex(' desc', @sort_item) > 0
    begin
    select @sort_item = replace(@sort_item, ' desc', '')
    end
    else
    begin
    select @sort_item = @sort_item + ' desc'
    end
    end

    select @reverse_orderby = @reverse_orderby + @sort_item + ', '
    select @ind_start = @ind + 1

    end
    select @reverse_orderby = substring(@reverse_orderby, 1, len(@reverse_orderby)-1)

    return( @reverse_orderby)
    end


    usage:
    declare @orderby varchar(100)
    select @orderby = 'f1, f2 desc, f3 asc, f4, f5, d6 desc'
    select dbo.ReverseOrderBy(@orderby)

    will return

    f1 desc, f2 , f3 desc, f4 desc, f5 desc, d6
    Shianmiin

  9. #9
    Join Date
    Oct 2003
    Posts
    16
    Ahh, that is cool. Thanks!

Posting Permissions

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