Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Question Unanswered: Last record in 10,000,000 rows

    Hey gang, I got a table that has about 10,000,000 row and I need a test of the last 10 rows. Isn't the statement: Select bottom 10 from table?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Rows in a table don't have any order. You can order the result set any way you want. Take the TOP 10 with the order "upside down" and you are in business!

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    101
    SELECT TOP 1 * from test order by id desc
    OR
    select * from TEST a
    where 1>(select count(*) from TEST where ID>a.id)
    -SS

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you have an ADD_ROW_TS or IDENTITY Column?
    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
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Talking Last record in 10,000,000 rows

    Gentlemen, I dont beleive I've been clear on what I'm attempting to do. I have a table with 10,000,000 records. (which happend to reflect 6 months historical data) that I need to confirm it is there. I can do a 'select top 10 from table" to get the first 10 record of this new table. I need to see the last 10 to confirm the month,date,time etc... What I've come up with in the sql anaylser is
    select * from dbo.INCOMING_TEMP
    where RECORD_NUMBER > 10453700
    according to my records the total number of rows at 10453747

    It's running but taking all day to get there.
    Last edited by garrydawkins; 05-05-04 at 14:26. Reason: update

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm assuming RECORD_NUMBER is defined like

    RECORD_NUMBER int IDENTITY(1,1) NOT NULL

    Yes?

    SELECT TOP 10 * FROM yourTable99 ORDER BY RECORD_NUMBER DESC

    Will get you the official last 10 records...

    If that's truly the case....

    No?
    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.

  7. #7
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Last record in 10,000,000 rows

    Thanks that works fine. I havent been my self since I got the damn cast on my foot. So bear with me. Not I get to compare radomn samples of the data before blowing the original table away.

Posting Permissions

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