Post reply

SQL - MAX minus 1

  • November 27, 2020 at 6:19 pm

    #3814524

    Hi Guys,

     

    Wondering if any1 can help i need a Sql statement to obtain the one before max record for each ID

    I have showed the DATA set attached with the results i need is this possible?

    Thanks

     

     

    Attachments:
    You must be logged in to view attached files.

  • November 27, 2020 at 6:50 pm

    #3814534

    If I am understanding you correctly, you want to know the second largest record.  If so, one way you could do this is to do a nested SELECT TOP query.  Do a SELECT TOP (2) as the inner query and a SELECT TOP (1) as the outer query.  Inner query ordered by ID DESCENDING (thus getting the two largest values) and outer query by ID ASCENDING (thus grabbing the smallest of the 2 values).

    So something like:

    SELECT TOP (1) ID
    FROM (SELECT TOP (2) ID FROM table ORDER BY ID DESC) AS RawData
    ORDER BY ID ASC

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 27, 2020 at 7:23 pm

    #3814540

    Sounds like a case for row number and = 2

     

    ;with cte as (

    Select row_number() over (partition by id order by datetime desc) as rownum,

    id, datetime, checkpoint

    from rawdata )

    select * from cte where rownum = 2

  • November 27, 2020 at 7:33 pm

    #3814543

    Hi

    The "as" before rownum is flaggin as incorrect syntax.

    any ideas?

    Thanks.

     

     

     

     

  • November 27, 2020 at 7:39 pm

    #3814544

    I missed a bracket which I have updated

     

    also just don’t copy and paste from the internet try and work out what has gone wrong and feed in the solution and investigate what the answer is doing.  You will learn more that way.  I certainly did.

  • November 27, 2020 at 7:45 pm

    #3814546

    Thanks SSC.

     

    worked perfect. yeah ive been trying for a while! appreciated for the help

Viewing 6 posts - 1 through 5 (of 5 total)

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