CODEWITHSUNDEEP
mysql
user3790467
user3790467

Reputation: 27

Get latest record on group by

I have a table structure like this:

ID  Win     Bet    UserID       Date
1   win     1000     1      2014-11-12 19:15:34
2   win     1000     1      2014-11-12 19:15:49
3   loose   1000     1      2014-11-12 19:21:52

and I run a query like this:

SELECT 
    u.Username,
    SUM(b.Bet) AS totalWin,
    b.date,
    b.win
FROM blackjack_history AS b
INNER JOIN users_main AS u ON b.UserID = u.ID
WHERE b.win = 'win' GROUP BY b.UserID ORDER BY totalWin DESC, LIMIT 10

The Probem is when i run this query, it returns the row for ID 1 regarding Date. How do i return the date for ID 2 instead? I have tried using Order By but this does not seem to do the job

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You don't want just the latest record. You are also doing aggregation. So, just use max():

SELECT u.Username, SUM(b.Bet) AS totalWin, max(b.date) as date, b.win 
FROM blackjack_history b INNER JOIN
     users_main u
     ON b.UserID = u.ID
WHERE b.win = 'win'
GROUP BY b.UserID
ORDER BY totalWin DESC
LIMIT 10;

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

You are misusing the pernicious nonstandard MySQL extension to GROUP BY. Read this. http://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html In a standard aggregate query (such as one with SUM() in it) you need to mention every item in the SELECT clause in GROUP BY clause unless it is an aggregate function. In nonstandard MySQL, you get unpredictable results when you leave out items from GROUP BY. In standard SQL, you get an error message.

Try the following GROUP BY clause instead of the one you have shown.

 GROUP BY u.Username, b.date, b.win

Upvotes: 0

Related Questions