Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Question Unanswered: How to SELECT the latest records?

    Hello!

    I have a table, where one of the columns is the date/timestamp of when each row was inserted. I want to be able to extract the most recently inserted rows.

    With Sybase (a not so distant cousin of MS SQL) the following works:
    Code:
    select * from TABLE having date = max(date)
    With MS SQL, however, the same query does not work:
    Code:
    Column 'TABLE.date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
    What's the solution? Thanks!

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by teterin
    Hello!



    With Sybase (a not so distant cousin of MS SQL) the following works:
    Code:
    select * from TABLE having date = max(date)
    Try
    Code:
    SELECT * FROM TABLE WHERE DATE IN (SELECT MAX(DATE) FROM TABLE)
    I prefer using IN when using a subquery so that it doesn't fail if for some reason you get more than one record in the subquery result (though in this case you should not).

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Apr 2003
    Posts
    64
    Thanks for responding!

    Well, yeah, that would be a "bullet-proof" method, but somewhere I learned, that subqueries are to be avoided, whenever possible...

    I wonder, what's wrong with my original plan -- that works with Sybase...

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by teterin
    Thanks for responding!

    Well, yeah, that would be a "bullet-proof" method, but somewhere I learned, that subqueries are to be avoided, whenever possible...

    I wonder, what's wrong with my original plan -- that works with Sybase...
    I can't know why subqueries should be avoided. If used too much they make the code unreadable and may (if really complex perhaps) throw SQL for a loop when trying to compute a plan, but if you keep them simple (like the example), they're not bad.

    HAVING is a clause in T-SQL that can only be used on aggregate computed columns.

    So, for example:

    SELECT CustomerNumber, Count(*)
    FROM dbo.Orders
    GROUP BY CustomerNumber
    HAVING Count(*) > 1

    would find all the CustomerNumbers for customers who had placed more than one order. HAVING must appear after the GROUP BY clause and is only valid in queries having (no pun intended) an aggregate.

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Sep 2007
    Posts
    12
    well what i would use

    select top 1 * from TABLE order by date desc

    i think it should work fine

  6. #6
    Join Date
    Apr 2003
    Posts
    64
    Quote Originally Posted by sefak169
    select top 1 * from TABLE order by date desc
    There could be more than one here -- a batch of records inserted at once... I want them all.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    FIRST of all

    Do you want to CLEARLY tell us what you want before we enter this dance?

    Read the hint link at the top of the board and post what it asks for

    Sounds like you need a batch window
    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
    Apr 2003
    Posts
    64
    Quote Originally Posted by Brett Kaiser
    Do you want to CLEARLY tell us what you want before we enter this dance?
    I want to extract all records, which were entered on the most recent date available in the table. I want to do that without subqueries.

    For example, if the table has:

    Code:
    DATE|VALUE
    -----------
    1   |red
    1   |blue
    2   |green
    3   |orange
    3   |purple
    -----------
    I want the "orange" and the "purple". I don't know up-front, what the most recent date is, nor do I know, how many records where entered on it. But I want them all -- and in one query.

    If that's not "CLEARLY" enough, I ought to be looking for another forum

  9. #9
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by teterin
    I wonder, what's wrong with my original plan -- that works with Sybase...
    Then you have 2 options:
    1. Use Sybase if you want your database to understand the Sybase dialect.
    2. Use the solution that was provided to you 3 days ago.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by teterin
    I want to do that without subqueries
    And what's wrong with subqueries?!

    In your example, date appears to be an integer... That's not very clear
    I think what you're saying is that you want the "Top" record, by date, for each different value.
    Code:
    DATE     | VALUE
    20070101 | red
    20070202 | red
    20070303 | red
    20070101 | blue
    20070202 | orange
    20070404 | orange
    Expected results
    Code:
    DATE     | VALUE
    20070303 | red
    20070101 | blue
    20070404 | orange
    Quote Originally Posted by teterin
    If that's not "CLEARLY" enough, I ought to be looking for another forum
    Not everyone understands your question off the bat - what seems obvious to you is not as obvious to everyone.

    oh, and don't bite off the hand that feeds you!
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2003
    Posts
    64
    And what's wrong with subqueries?!
    I think, they are "ugly". If something can not be done without them, I'll use them, but if they can be avoided, I'd rather do the avoidance.

    I think what you're saying is that you want the "Top" record, by date, for each different value.
    No, that's wrong. Even the Subject of the tread says: "How to SELECT the latest records?"

    I want all records for the top date.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    "ugly" is not a reason to discount such a powerful assett - I mean... Look at the England football team! *ahem*

    That aside, Max() is an aggregate function - so without being able to aggregate or use subqueries I don't know what choice you have.

    I' go with post #2; except that I'd note you use = instead of IN for this situation - simply because the aggregated function will only return a single value.
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    arrogance, ah how refreshing

    DID YOU READ THE DAMN STICKY AT THE TOP OF THE BOARD TO TELL YOU EXACTLY HOW TO POST A GD QUESTION TO SUPPLY US WITH THE INFO WE NEED

    Don't want to use sub queries because they are ugly

    What a moron

    I'm done
    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.

  14. #14
    Join Date
    Apr 2007
    Posts
    183
    Quote Originally Posted by teterin
    There could be more than one here -- a batch of records inserted at once... I want them all.
    Heard of TOP 1 WITH TIES?

  15. #15
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You have a column named "DATE" ?

    I believe this is a reserved word...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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