Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Question Unanswered: opposite of SELECT TOP?

    Is there an opposite of the TOP keyword in a select statement?

    I have the following query which works fine but it's not in the order I need because I have to use DESC to get the last 40 records.

    Code:
    select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime 
        from product 
        where scarfcode = 'V3' 
        order by creationtime desc
    I need the returned records ordered by creationtime but not descending.

    Randy

  2. #2
    Join Date
    Mar 2007
    Posts
    97
    Does this work
    Code:
    select * from
    (
    select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime 
        from product 
        where scarfcode = 'V3' 
        order by creationtime desc
    )
    order by creationtime asc

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have you tried:
    Code:
    select slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime 
    from (select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime 
        from product 
        where scarfcode = 'V3' 
        order by creationtime desc) a
    order by creationtime

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    and.........what's wrong with that?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Dang it all. Sniped. Why did I go back and replace "select *"? But, Silas missed one important piece. I will leave it to the interested reader to spot it.

  6. #6
    Join Date
    Sep 2003
    Posts
    22
    Code:
    select * from
    (
    select top 40 slabnumber, scarfcode, left(productname,6) as heatid, strandid, heatcutnumber, creationtime 
        from product 
        where scarfcode = 'V3' 
        order by creationtime desc
    )
    order by creationtime asc
    Tried this and get:

    Server: Msg 156, Level 15, State 1, Line 6
    Incorrect syntax near the keyword 'order'.

    I probably should mention this is an old machine I'm running the query against, it's running 7.0. I'm thinking I may just have to do two queries... One to create a temporary table and one to pull the data out of the temporary table in the right order.

    Randy

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    or you could use Matt's

    In any case, you're missing a name for the derived table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by RandyRiegel
    I probably should mention this is an old machine I'm running the query against, it's running 7.0. I'm thinking I may just have to do two queries... One to create a temporary table and one to pull the data out of the temporary table in the right order.
    As Brett pointed out, just use the code provided by MCrowley. It will work in MS-SQL 7.0 (and subsequent releases).

    -PatP

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mcrowley's won't work

    the outer SELECT includes the expression left(productname,6)

    the derived table has no such column



    fix this small error and it will work fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2003
    Posts
    22
    Thanks guys! Working great now!

    Randy

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Yep. I cutted and pasted. Did not even notice the left function in there. The poster is happy, though.

Posting Permissions

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