How to get the last occurrence of a field

Discussion in 'General MSSQL Discussion' started by tim8w, Feb 23, 2021.

  1. tim8w

    tim8w

    Joined:
    Feb 23, 2021
    Messages:
    2
    Likes Received:
    1
    Hi,

    I have a simple query that returns two fields. I need to modify that query so that it only returns the last or highest number for each pair of fields.

    It's easier to show than explain. Here's my query:

    SELECT WONum, OpNum
    FROM tblHistory
    ORDER BY WONum, OpNum

    It returns something like this:

    WONum OpNum
    1396026 40.00
    1396026 90.00
    1396026 90.00
    1396026 120.00
    1427600 130.00
    1456392 120.00
    1456392 130.00
    1458018 230.00
    1458018 280.00
    1460224 40.00
    1460224 80.00

    I need it to return:
    WONum OpNum
    1396026 120.00
    1427600 130.00
    1456392 130.00
    1458018 280.00
    1460224 80.00

    Just the last row of each WONum, OpNum pair
     
    tim8w, Feb 23, 2021
    #1
    1. Advertisements

  2. tim8w

    SkeerNC

    Joined:
    Apr 19, 2021
    Messages:
    2
    Likes Received:
    0
    I was hoping that someone had answered this question, because it's exactly what i need help with.
    BY chance, did you get help someplace else for this issue?
    Thanks in advance.
     
    SkeerNC, Apr 19, 2021
    #2
    1. Advertisements

  3. tim8w

    tim8w

    Joined:
    Feb 23, 2021
    Messages:
    2
    Likes Received:
    1
    Ended up using something like this:

    SELECT WONum,OpNum FROM (SELECT *,RANK() OVER(PARTITION BY WONum ORDER BY OpNum DESC) rr
    FROM #test) t
    WHERE rr=1
     
    tim8w, Apr 19, 2021
    #3
    SkeerNC likes this.
  4. tim8w

    ericvanier

    Joined:
    May 10, 2021
    Messages:
    1
    Likes Received:
    0
    The LAST() function is only supported in MS Access.
    SQL LAST() Workaround in SQL Server, MySQL and Oracle
    SELECT TOP 1 column_name FROM table_name
    ORDER BY column_name DESC;
    Example
    SELECT TOP 1 CustomerName FROM Customers
    ORDER BY CustomerID DESC;
     
    ericvanier, May 10, 2021
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.