How to get last item for GROUP BY

Question

I have a table like this:

id transaction_id auto_recurring paid_amount package_customerid
37              0              1           0                  4
45             37              1           0                  4
51              0              1           0                  4
57             51              1           0                  4
62              0              1           0                  4
67             62              1           0                  4

There are 6 records of package_customer_id = 4. Now I want to get the last record of 4. in this case id = 67 is my desired record. I try this SELECT * FROM transactions GROUP BY package_customer_id. But I got first record of package_customer_id = 4. i.e: id = 4 is my fetched result. How can I get id = 67 (my desired record) modifying this sql?

Be aware that when using "Group By" you aren't necessarily getting a record back. You will get a collection of fields that match the selection criteria. The fields in the selection (where your "*" is) need to either be represented in a where clause or be using aggregate functions like MAX https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html Newer version of MySQL will give you an error if you try to do this. — Jaydee, Jul 25 '17 at 13:17

Arnas Veberis · Answer 1 · 2017-07-25T13:39:59.300

2

SELECT * FROM transactions WHERE package_customer_id = 4 ORDER BY id DESC LIMIT 1;

That would be my shot at it. Sorry but i haven't tested it, i leave it up to you:)

EDIT:

~~Dont forget the quotes " ` " on columns name's:)~~

Check you column name package_customer_id OR package_customerid ?

edited Jul 25 '17 at 13:39

answered Jul 25 '17 at 13:11

Arnas Veberis

75
3

1

backticks only needed if the field is a "reserved word" – xQbert Jul 25 '17 at 13:17
And apostrophes are only needed if possessive - so "column's name" or leave it out, as in "column names" – Strawberry Jul 25 '17 at 13:25
@Strawberry what about contractions? I generally don't use them but... :P Note: Keywords do not need backticks – xQbert Jul 25 '17 at 13:26
@xQbert Likewise, I ain't never used one, so don't know what you're talking about. – Strawberry Jul 25 '17 at 13:28
This will never work as required – vjy tiwari Jul 25 '17 at 13:32
This query will give the exact result as required SELECT temp.* FROM (SELECT * FROM `transactions` WHERE package_customer_id = 4 order by id DESC LIMIT 1 ) AS temp GROUP BY temp.package_customer_id – vjy tiwari Jul 25 '17 at 13:33
@xQbert thanks for the edit:) i'm not really grammar friendly:D thats why i look there first when debugging :D – Arnas Veberis Jul 25 '17 at 13:46

Gordon Linoff · Answer 2 · 2017-07-25T13:40:29.237

0

Don't use group by. Use where:

SELECT t.*
FROM transactions t
WHERE t.id = (SELECT MAX(t2.id)
              FROM transactions t2
              WHERE t2.package_customer_id = t.package_customer_id
             );

You can filter this for whichever package customer ids you like, in the outer query.

edited Jul 25 '17 at 13:40

answered Jul 25 '17 at 13:00

Gordon Linoff

1,242,037
58
646
786

Doesn't handle package_CustomerID = 4 – xQbert Jul 25 '17 at 13:25

vjy tiwari · Answer 3 · 2017-07-25T13:30:33.173

0

You may try this .

SELECT temp.* FROM (SELECT * FROM `transactions` WHERE package_customer_id = 4 order by id DESC LIMIT 1 ) AS temp GROUP BY temp.package_customer_id

edited Jul 25 '17 at 13:30

answered Jul 25 '17 at 13:03

vjy tiwari

843
1
5
17

Doesn't handle package_CustomerID = 4 – xQbert Jul 25 '17 at 13:25
@xQbert OH !! i skiped this point , anyways i can add where condition inside the subquery – vjy tiwari Jul 25 '17 at 13:30

Omar Faruk · Answer 4 · 2017-07-25T13:42:10.697

-1

You can use like this:

SELECT * FROM transactions WHERE id IN (SELECT MAX(id) FROM transactions GROUP BY package_customerid)

edited Jul 25 '17 at 13:42

answered Jul 25 '17 at 13:10

Omar Faruk

298
5
19

Doesn't handle package_CustomerID = 4 – xQbert Jul 25 '17 at 13:25

RaMeSh · Answer 5 · 2017-07-25T13:50:23.293

-1

try this query

if you need with GROUP BY clause: use this query

SELECT * FROM transactions where GROUP BY package_customerid ORDER BY package_customerid DESC LIMIT 1;

OR if you DON'T need with GROUP BY clause: use this query

SELECT MAX(id),transaction_id,auto_recurring,paid_amount,package_customerid FROM transactions where package_customerid=4;

Output:

id transaction_id auto_recurring paid_amount package_customerid
67             62              1           0                  4

edited Jul 25 '17 at 13:50

answered Jul 25 '17 at 13:32

RaMeSh

3,330
2
19
31

In the question , mentioned that use Group by clause – vjy tiwari Jul 25 '17 at 13:38
I never understood using aggregation w/o a group by when the field values were different. so what transaction_ID would be selected here? why is it 62? and not 0 51 or 37? – xQbert Jul 25 '17 at 13:53

How to get last item for GROUP BY

5 Answers5