get the last record in each group in MYSQL singular table, with criteria that ignores certain groups

Question

I tried the solution presented here by Mr. Karwin
Retrieving the last record in each group

I have a singular table which contains all the records I need with the FF records.

 --------------------------------------------------
 | Keynum     | PaymentID  | BuyerID | LatestBill |
 |----------- | -----------|---------|------------|
 |   3        |    4       |    4    |  30000     |
 |   2        |    4       |    4    |  10000     |
 |   1        |    4       |    4    |  10000     |
 |   1        |    9       |    9    |  9999      |
 --------------------------------------------------

The desired output is:

 --------------------------------------------------
 | Keynum     | PaymentID  | BuyerID | LatestBill |
 |----------- | -----------|---------|------------|
 |   3        |    4       |    4    |  30000     |
 |   1        |    9       |    9    |  9999      |
 --------------------------------------------------

What I have tried is this:

  Select keynum, PaymentID, BuyerId, LatestBill From  Sales where LatestBill != 0 group by PaymentID, BuyerID order by keynum  Desc;

and this

 SELECT max(keynum), PaymentID, BuyerID, NewInstallmentBal
 FROM aliissales.tblmovedactual
 GROUP BY PaymentID, BuyerID DESC;

However, what I get is this:

 --------------------------------------------------
 | Keynum     | PaymentID  | BuyerID | LatestBill |
 |----------- | -----------|---------|------------|
 |   1        |    4       |    4    |  10000     |
 |   1        |    9       |    9    |  9999      |
 --------------------------------------------------

What I need is the last record of each group, with each record to be group by PaymentID and BuyerID.

if I use Max(keynum), it gets indeed the max keynum for each group, but not the corresponding records that go with it. is a simpler way to have the records go with the Keynum??

Also, if the LatestBill = 0, the entire gorup is now ignored.

ie if

keynum 3 LatestBill = 0, group BuyerID 4 , PaymentID 4 is now ignored.

score 1 · Accepted Answer · answered Mar 11 '13 at 06:54

1

SELECT  a.*
FROM    aliissales.tblmovedactual a
        INNER JOIN
        (
            SELECT  max(keynum) xx, PaymentID, BuyerID
            FROM    aliissales.tblmovedactual
            GROUP   BY PaymentID, BuyerID
        ) b ON  a.PaymentID = b.PaymentID   AND
                a.BuyerID = b.BuyerID AND
                a.keynum = b.xx

SQLFiddle Demo

answered Mar 11 '13 at 06:54

John Woo

258,903
69
498
492

Thanks, but where will I add a condition that will ignore records that meet certain criteria? example if the LatestBill = 0, record will be ignored. I tried adding (where) statement inside the join, but it now shows 2 | 4 | 4 | 10000 – Malcolm Salvador Mar 11 '13 at 07:11
you can add that in the outermost select ex, http://www.sqlfiddle.com/#!2/099c3/3 – John Woo Mar 11 '13 at 07:14
If I may ask, what was the xx next to the max(keynum) for?? Edit: ah, it was an...alias? – Malcolm Salvador Mar 11 '13 at 07:23
@Malky.Kid oh, it's called `ALIAS` of the column. – John Woo Mar 11 '13 at 07:24
Okay I gotta read more into this. This was a great technique thank you very much – Malcolm Salvador Mar 11 '13 at 07:31

score 0 · Answer 2 · answered Apr 01 '13 at 05:33

0

SELECT keynum, PaymentID, BuyerID, NewInstallmentBal FROM aliissales.tblmovedactual WHERE keynum IN ( SELECT max(keynum) FROM aliissales.tblmovedactual GROUP BY PaymentID )

answered Apr 01 '13 at 05:33

user2227393

1
1
2

get the last record in each group in MYSQL singular table, with criteria that ignores certain groups

2 Answers2