Most Recent SQL Query

  • cjeans

    SSC Enthusiast

    Points: 107

    Hi,

    I am trying to select the 10 most recent entries from my table. I am not sure how to adjust a query so that instead of looking for the top # or top % it returns the top 10 most recent.

    Can you please help?

    Thanks,

  • mark.humphreys

    SSC Eights!

    Points: 867

    Do you have an incremental ID field or a datetime field with a datetime stamp in it?  If so you could use an ORDER BY clause sorted descending on those fields and then use the TOP n to get your results.

  • cjeans

    SSC Enthusiast

    Points: 107

    I tried using this

    select TOP 10 from DateExpensed where * from dbo.Table_1 order by DateExpensed ASC

    however it has not worked

  • Jonathan AC Roberts

    SSCoach

    Points: 16489

    cjeans - Wednesday, September 5, 2018 10:00 AM

    I tried using this

    select TOP 10 from DateExpensed where * from dbo.Table_1 order by DateExpensed ASC

    however it has not worked

    You need to add some columns to the select!
    SELECT TOP 10 * FROM ...

  • cjeans

    SSC Enthusiast

    Points: 107

    select top 10 * from dbo.Table_1 and order by DateExpensed ASC

    I tried this but again is not working

  • Jonathan AC Roberts

    SSCoach

    Points: 16489

    cjeans - Wednesday, September 5, 2018 10:20 AM

    select top 10 * from dbo.Table_1 and order by DateExpensed ASC

    I tried this but again is not working

    you can't have an "and" before the order by
    select top 10 * from dbo.Table_1 order by DateExpensed ASC

  • drew.allen

    SSC Guru

    Points: 76434

    cjeans - Wednesday, September 5, 2018 10:00 AM

    I tried using this

    select TOP 10 from DateExpensed where * from dbo.Table_1 order by DateExpensed ASC

    however it has not worked

    Either change your TOP to BOTTOM or your ASC to DESC.  ASC will sort the "earliest" records on top.  Since you want the "latest" you either want to reverse your sort or take records from the bottom.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • cjeans

    SSC Enthusiast

    Points: 107

    thanks all. working now

  • Jonathan AC Roberts

    SSCoach

    Points: 16489

    drew.allen - Wednesday, September 5, 2018 10:23 AM

    cjeans - Wednesday, September 5, 2018 10:00 AM

    I tried using this

    select TOP 10 from DateExpensed where * from dbo.Table_1 order by DateExpensed ASC

    however it has not worked

    Either change your TOP to BOTTOM or your ASC to DESC.  ASC will sort the "earliest" records on top.  Since you want the "latest" you either want to reverse your sort or take records from the bottom.

    Drew

    Is BOTTOM valid in T-SQL?

  • drew.allen

    SSC Guru

    Points: 76434

    Jonathan AC Roberts - Wednesday, September 5, 2018 11:13 AM

    drew.allen - Wednesday, September 5, 2018 10:23 AM

    cjeans - Wednesday, September 5, 2018 10:00 AM

    I tried using this

    select TOP 10 from DateExpensed where * from dbo.Table_1 order by DateExpensed ASC

    however it has not worked

    Either change your TOP to BOTTOM or your ASC to DESC.  ASC will sort the "earliest" records on top.  Since you want the "latest" you either want to reverse your sort or take records from the bottom.

    Drew

    Is BOTTOM valid in T-SQL?

    I think it was at one point.  It's not valid in SQL 2016, so I could be wrong.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply