CODEWITHSUNDEEP
sqldatabaseinformix
Passionate Engineer
Passionate Engineer

Reputation: 10422

Group by on custom table not working

I have below query:

SELECT bt.app_id, MAX(bt.id) as user_id
FROM (SELECT bt.*
  FROM user bt
  WHERE bt.app_id IN (SELECT bs.id
                                FROM
                                  (SELECT max(id) as id, app_id, count(id) AS count
                                   FROM user_turn bt
                                   WHERE  bt.state = 'pending'
                                   GROUP BY app_id) as bts
                                  INNER JOIN user bs ON bs.id = bts.user_id
                                  INNER JOIN club c ON c.id = bs.club_id
                                WHERE count > 1)
    ORDER BY bt.app_id DESC, bt.id ASC) bt
WHERE bt.state = 'finished'
GROUP BY bt.app_id, bt.id
ORDER BY bt.app_id DESC, bt.id DESC;

This however does not seem to be working with GROUP BY bt.app_id, bt.id

As I get something like:

app_id         user_id
10010          25024
10010          25022
10008          24795
10008          24784
10006          24783

I thought it would GROUP BY app_id but it clearly has duplicate for example 10010

What would be the reason for this not to work?

Upvotes: 1

Views: 37

Answers (2)

Abdul Hannan Ijaz
Abdul Hannan Ijaz

Reputation: 844

Your grouping is correct according to query . But rename the outer alias so that it can be clear.. as you are using subquery and both have same name BT and remove the bt_id

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522751

Would there be anything wrong with simply doing a GROUP BY only on bt.bet_setting_id ? Change your query to the following:

SELECT bt.bet_setting_id, MAX(bt.id) as bet_turn_id
...
GROUP BY bt.bet_setting_id
ORDER BY bt.bet_setting_id DESC, MAX(bt.id) DESC

Because you were grouping by the combination of bet_setting_id and id, taking the MAX of the latter was meaningless, since there was only ever a single value for the id in each group.

Upvotes: 1

Related Questions