Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: Want to pull last three entries from DB

    I am trying to pull the last three entries from a table in my database but I am having trouble writing the correct query. The database has multiple entries for each item in my database but I want to pull just the last three for each item. I have tried the TOP function with the items ordered in descending order but that only works when I define each item seperatley. I need to know how to pull the last three entries for every item I have in the table.

    Thanks,

    Tim

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you describe how you determine the "lastness" of a row? Inside of a table, rows and columns have no order.

    Order is something that appears in result sets, which is what I suspect you are thinking about. An important issue to consider is how do you deal with "ties" where there are 12 rows that all have the same "sequence" in your result set. Do they have any order? How do you find it?

    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    2
    In the table that I am qureying I have a master ID for the item and a unique sequential id althought, the number does not go 1,2,3 it goes more like 10,20,25,27, for the field I am trying to get three of. The master ID is the Key or the field that I am Grouping by and I am ordering by the sequential id number. In some cases the item has at least 7 or more entries and I just want the last three. I hope this kind of helps answer your questions.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This may be slow if you have an index on [master ID], [unique sequential id] and it will be excruciating if you don't have an index on them, but:
    Code:
    SELECT [master ID], [unique sequential id]
       FROM myTable AS a
       WHERE  (SELECT Count(*)
          FROM myTable AS b
          WHERE  b.[master ID] = a.[master ID]
             AND a.[unique sequential id] <= b.[unique sequential id]) <= 3
    -PatP

Posting Permissions

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