Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Unanswered: Finding last entries

    Hi, I am searching for the most easy SQL solution:

    Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
    This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?

    Thx for reply.

    dajm

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How's your resume' looking these days? Is it up to date?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The last row updated? The last row inserted? Physical location might not really mean anything.

    Are there any related tables that could help us out.

    You might be screwed if you want to figure this out in your current setup.

    Going forward you could use a trigger to populate a new table for this purpose.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    If you use TOP 1 you get a result that is as good as any.
    Noone can say that it is false, because if they can, they must say why, and if they say why, then you have a rule you could use in your select statement.

    That's what I should've done in your shoes, given that there was noone to ask that is ...

    As Thrasymachus says, there is no way (with the given info) that the solution can be found, atleast not that I can see.

  5. #5
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72

    Finding last entries

    O.K. Here's an example for everybody

    Car-ID |sales price

    1|5
    3|3
    2|6
    1|7
    3|4

    As result I need to get
    2|6
    1|7
    3|4

    With Navision language C/Side it is very easy to catch the last entries of database by setting a filter on car-id, but how do I get this result by using SQL ?

  6. #6
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    Example pasted from earlier post :

    Car-ID |sales price

    1|5
    3|3
    2|6
    1|7
    3|4

    As result I need to get
    2|6
    1|7
    3|4

    Nothing tells you that these three last entries in your result is in fact the three last ones. All you know is that some means of presentation choose to show you those results in that order. Running the same question again or from another client might return other results. Therefore you still haven't presented any useful info to solve the problem, unless you wanna use my TOP solution earlier. In that case replace the TOP 1 with TOP 3 ....

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Data in a database is not guaranteed to be in any order unless specified in the table design. If this table has a clustered index (as it should), then the data is no longer in the order in which it was entered.

    Period.

    If you do not store the datetime the record was entered, you cannot expect to sort or filter on the datetime the record was entered.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    There exists no index, but the no. of cars is dynamic.
    I cannot use a "select top n" statement as I never know "n".
    Can I combine this however with a "select count (distinct car-id)" ?

    dajm

  9. #9
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    Sorry, all back. There is a clustered index on a integer field with incremental no., used as a uniqueidentifier.
    Does this help somehow ?

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well yes. but you said there were no keys earlier no keys....

    and then you said the keys were not dynamic

    "Lets say i have 500000 rows of cars in one table with 30000 different car ID's"

    just use the max() function on your newly discovered field.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  11. #11
    Join Date
    Jan 2002
    Location
    Berlin
    Posts
    72
    A max() on this field returns just the last row, but not every last row of my 30000 vehicles, means 30000 rows.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    See this thread for a solution to your problem:

    http://www.dbforums.com/showthread.p...8&page=1&pp=15
    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
  •