none
select data in reverse order

    Question

  • I have query that returns some data, now I need to get data from this query in reverse order

    To explain the problem:

    My query returns some data form first record to last and I like to have result from last to first record, I cannot use ORDER.

    Any ideas?

    Tuesday, August 02, 2011 1:44 PM

Answers

  • I do not think it would be possible.

    >> My query returns some data form first record to last

    Rather not true, when you run a query without order by clause the records are returned in random order, hance there is no such thing like from first to last. Probably you tested on small table and you were the only user of the system, that is why the query returned the data according to the order you inserted it into the table, however to guarantee the return order you have to use ORDER BY clause.


    With kind regards
    Krystian Zieja
    http://www.projectenvision.com
    Follow me on twitter
    My Blog
    Need help with your systems?
    Tuesday, August 02, 2011 1:50 PM

All replies

  • I do not think it would be possible.

    >> My query returns some data form first record to last

    Rather not true, when you run a query without order by clause the records are returned in random order, hance there is no such thing like from first to last. Probably you tested on small table and you were the only user of the system, that is why the query returned the data according to the order you inserted it into the table, however to guarantee the return order you have to use ORDER BY clause.


    With kind regards
    Krystian Zieja
    http://www.projectenvision.com
    Follow me on twitter
    My Blog
    Need help with your systems?
    Tuesday, August 02, 2011 1:50 PM
  • Hi, in this case you can insert in temp table with ID (identity) and use a cursor to display in reverse order, but it's not a good idea ( bad performance), I think Order By is the unique good solution in your case.

     


    Best regards

    Tuesday, August 02, 2011 1:54 PM
  • I don't want to sort my data I just need get in reverse order, I know that I can use temp table but is it possible to do this in some other way.

    Example:

    data returned by query

    This is first row

    And this is second row

    But this is next row

    Last row

    And I need:

    Last row

    But this is next row

    And this is second row

    This is first row

    Tuesday, August 02, 2011 2:09 PM
  •  

    It is not possible without using Order by or Temp Table !!

     


    Best regards
    Tuesday, August 02, 2011 2:12 PM
  • Hi,

    Just thinking - You could use a CTE as in this example...

    CREATE TABLE [dbo].[test](
    	[col1] [int] NULL
    ) ON [PRIMARY]
    go
    with a(col1, rankcol)
    as
    (
    select col1, row_number() over (order by col1 desc) as rankcol
    from dbo.test
    )
    select * from a
    inner join test b
    on a.col1 = b.col1
    order by rankcol
    

     


    Peter Carter http://sqlserverdownanddirty.blogspot.com/
    Tuesday, August 02, 2011 2:18 PM
  • Hi,

    Just thinking - You could use a CTE as in this example...

    CREATE TABLE [dbo].[test](
    
    	[col1] [int] NULL
    
    ) ON [PRIMARY]
    
    go
    
    with a(col1, rankcol)
    
    as
    
    (
    
    select col1, row_number() over (order by col1 desc) as rankcol
    
    from dbo.test
    
    )
    
    select * from a
    
    inner join test b
    
    on a.col1 = b.col1
    
    order by rankcol
    
    

     


    Peter Carter http://sqlserverdownanddirty.blogspot.com/

    Ok, but you have used Order by !

     


    Best regards
    Tuesday, August 02, 2011 2:21 PM
  • LOL - A very good point well made, although I was assuming that the ORDER BY Limitation such as using it in a view, etc. Otherwise, I was guessing we could just have used an ORDER BY on the table...

    If that is the case, with this, you can create your view without an order by, and then join the CTE to the view and rank it.


    Peter Carter http://sqlserverdownanddirty.blogspot.com/
    Tuesday, August 02, 2011 2:26 PM
  • You can also do this by using the curosrs, although this is not a good approach keeping performance in mind

    see the skeleton code below

    DECLARE @PK_column <pk_column_data_type>
    DECLARE A CURSOR SCROLL CURSOR
    FOR SELECT <PK_column> FROM <your_table>
    
    OPEN A
    
    FETCH LAST A INTO @pk_column
    
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    	SELECT * FROM <your_table> where pk_column = @pk_column
    	FETCH PRIOR A INTO @pk_column
    END
    


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 02, 2011 2:42 PM
  • Ok, another possibility, if you create a clustered index and sort the index in descending order, then it should work, as it normally returns rows in table order, but this is not 100% garunteed, as set-based logic states the order of rows is unimportant so does not garuntee.

    If the data is already in the buffer cache, in ascending order, it will not work. Use DBCC FREEPROCCACHE or restart service before testing...


    Peter Carter http://sqlserverdownanddirty.blogspot.com/
    Tuesday, August 02, 2011 2:46 PM
  • thanks all for so quick response, but as I see it is not posible.

    One more question is some command opposite to TOP?

    Tuesday, August 02, 2011 3:20 PM
  • thanks all for so quick response, but as I see it is not posible.

    One more question is some command opposite to TOP?


    No there isn't, TOP work always with Order by
    Best regards
    Tuesday, August 02, 2011 3:26 PM