Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    232

    Arrow Unanswered: last n records simpleand easy

    how do i display last n record on 6.5
    if i have a primary key(number)
    bigfoots

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select pkey
         , foo
      from yourtable xxx
     where n > (
       select count(*)
         from yourtable
         where foo > xxx.foo )
    "foo" is the column that determines the sequence

    without a sequence, "top" has no meaning
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean an identity Column?

    SELECT TOP n * FROM yourTable Order by PKID DESC
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Brett, when did sql server add support for TOP

    7, wasn't it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [homer]
    doooooooh
    [/homer]

    Details...details....

    Yup no TOP
    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.

  6. #6
    Join Date
    Oct 2003
    Posts
    232

    still not working

    No i mean a primary key which is a number

    select top 300 * from tablename
    order by columnname desc (columnname is primary key)

    Primary key is smallint


    when i do

    select clientid, foo
    from client x
    where 300>( select count(*)
    from client
    where foo> x.foo)

    It gives error foo column not there

    here after 6.5 sql became easy
    bigfoots

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sjumma, what are the columns in your table?

    which one do you want to sort by?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2003
    Posts
    232

    i want just 1 column

    let say i want 1 column

    select clientid
    from client


    clientid is smallint and contunious number (1,2,3,....)


    how will i make just the last 300 records be displayed
    bigfoots

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In MS-SQL 6.5, I'd use:
    PHP Code:
    SET ROWCOUNT 300

    SELECT clientid
       FROM dbo
    .client AS a
       ORDER BY clientid DESC

    SET ROWCOUNT 0 
    Note that an index on client.clientid will help performance immensely.

    -PatP
    Last edited by Pat Phelan; 04-13-04 at 16:29.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    <sigh />

    oh yeah, rowcount


    select clientid
    from client x
    where 300>( select count(*)
    from client
    where clientid> x.clientid)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by r937
    oh yeah, rowcount
    Mondo way ugly, but it gets an answer while I'm still young enough to care! I'd hate to contemplate how long it would take to slog the count query through a half million clients, even though it is esthetically more pleasant than the ROWCOUNT solution!

    -PatP

  12. #12
    Join Date
    Sep 2012
    Posts
    1

    Smile

    here is the way to find last n rows without using order by
    n to display in the same order as is in the table

    this diaplays the last 3 rows in the table


    table name is Lms_Books_Details
    n a column in it is Book_Code


    select * from Lms_Books_Details where Book_Code not in (select top((select COUNT(*) from Lms_Books_Details ) -3 ) book_code from Lms_Books_Details)

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ahinay245, did you bother to read the thread before posting?
    Your solution would not work in 6.5.
    It's not even the simplest solution in versions that do implement TOP.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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