Get the last row

Posted on 2011-10-12
Last Modified: 2012-06-27

Let's say I have the following rows

MyAutoinc     GroupId    Amount
1                     A             2000
2                     A            3000
3                     A            2000
4                     A            5000
5                    A            2000
6                    B            2000
7                    B            8000
8                    C            4000
9                   C            9000
10                    C            4000
11                    C            2000

I want to get the last row per every group so the result should be:

5                    A            2000
7                    B            8000
11                    C            2000

Thank you
Question by:starhu
    LVL 2

    Accepted Solution

    this should do what you want

    select *  from table  where MyAutoinc  in( select max(MyAutoinc ) from table group by GroupId )

    LVL 59

    Assisted Solution

    by:Kevin Cross
    starhu, I was passing through the zone and noticed this was still open. The above comment should solve this for you. However, for performance reasons, you may want to go with JOIN.

    SELECT t.MyAutoinc, t.GroupId, t.Amount
    FROM your_table t
    JOIN (
       SELECT GroupId, MAX(MyAutoinc) AS MAX_MyAutoinc
       FROM your_table
       GROUP BY GroupId
    ) m ON m.GroupId = t.GroupId AND m.MAX_MyAutoinc = t.MyAutoinc

    Anyway, since this is still open, I just wanted to support the above http:#36958894 should work.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API ( …
    I have been using r1soft Continuous Data Protection ( for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now