SELECT max value with each group without summing

I have the following SQLite table:

id  date        ticker  side value  qnt
1   21/07/2022  AGRO3   buy 23.65   600
2   02/09/2022  AGRO3   buy 28.9    200
3   11/11/2022  AGRO3   buy 27.09   100 *
4   30/08/2021  BBAS3   buy 30.48   100
5   29/09/2021  BBAS3   buy 29.43   200
6   26/11/2021  BBAS3   buy 30.85   200 *
7   20/05/2022  BRKM5   buy 42.83   300
8   04/07/2022  BRKM5   buy 36.46   100
9   22/08/2022  BRKM5   buy 32.97   200
10  08/09/2022  BRKM5   buy 30.56   100 *
11  25/06/2021  CPLE6   buy 6.16    1900 *
12  20/05/2022  EGIE3   buy 43.15   200
13  22/09/2022  EGIE3   buy 40.33   100 *
14  02/12/2019  ENBR3   buy 19.89   1000
15  19/03/2020  ENBR3   buy 14.9    100
16  23/11/2020  ENBR3   buy 18.43   200 *

What I need to do is select the MAX id from each GROUP (ticker), the ones flaged with a *. I tried:

SELECT max(id), ticker, qnt FROM calculos GROUP BY ticker

But somehow SQL is summing the qnt column. My desire is to generate the following table:

id  ticker  qnt
3   AGRO3   100
6   BBAS3   200
10  BRKM5   100
11  CPLE6   1900
13  EGIE3   100
16  ENBR3   200

Tks a lot!

CodePudding user response：

You could achieve this using a subquery. For example:

SELECT c.*
FROM calculos AS c
INNER JOIN
(
    SELECT MAX(id) AS max_id, ticker
    FROM calculos
    GROUP BY ticker
) AS subquery
ON c.id = subquery.max_id AND c.ticker = subquery.ticker

CodePudding user response：

GROUP BY will try to aggregate the columns like qnt. A subquery can help here so you're only getting one row's value.

SELECT c.*
FROM calculos c
JOIN (
  SELECT ticker, MAX(id) as max_id
  FROM calculos
  GROUP BY ticker
) m ON c.ticker = m.ticker AND c.id = m.max_id;

CodePudding user response：

You are misusing MySQL's notorious GROUP BY extension. The query you have here returns the qnt value for one of the rows in each group. The MySql server arbitrarily and unpredictably chooses which of the rows in the group to use.

Because your id values are unique, you can use this query to get what you want.

SELECT id, ticker, qnt 
FROM calculos
WHERE id IN (
  SELECT MAX(id)
   FROM calculos
  GROUP BY ticker);

And the index that goes with the PK will make this query use a ridiculously fast loose index scan.

Pro tip Do this before you test your queries. It will disable that very confusing extension, and instead throw a error for queries like the one you showed us.

SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');