0

There are a lot of questions dealing with max values but I can't find any that relate to this issue.

ID | Company | Result
----------------------
1  |    1    |   A
2  |    1    |   C
3  |    1    |   B <--
4  |    2    |   C
5  |    2    |   B
6  |    2    |   A <!--
7  |    3    |   C
8  |    3    |   A
9  |    3    |   B <--

I need to output the Companies whose last Result (based on ID) was "B".

To further complicate the issue, the $query will be used this:

select * from table where Company in ($query)

Any ideas? Thanks!

J--
  • 99
  • 1
  • 2
  • 9
  • https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column It's like this SELECT Company, MAX(ID) FROM YourTable WHERE Result = "B" GROUP BY Company – ihodonald Sep 02 '20 at 02:13
  • @TimBiegeleisen As long as the ID column is set to auto-increment, that should get the latest record. The result should have the ID of the record and the ID of the company. – ihodonald Sep 02 '20 at 02:34
  • 1
    @ihodonald The logic is incorrect, because `WHERE` happens _before_ `GROUP BY`. This means that your query would also return `(5, 2, B)` from the sample data above. – Tim Biegeleisen Sep 02 '20 at 02:39
  • @ihodonald the ID is set to AI. But your suggestion would include Company 2 in the example above, since B is one of their Results... just not the last Result. The output to the example should only return Company 1 & 3... which are the two Companies whose last Result was B. – J-- Sep 02 '20 at 02:39
  • J-- Ah, yes. That may require another query to see what the maximum ID is on a record for that company. Or, you could just use the inner join from @TimBiegeleisen's answer. My apologies. It's been a while since I've used SQL. – ihodonald Sep 02 '20 at 02:44

1 Answers1

1

On MySQL 8+, here is a query you may try using analytic functions:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Company ORDER BY ID DESC) rn
    FROM yourTable
)

SELECT ID, Company, Result
FROM cte
WHERE rn = 1 AND Result = 'B';

Demo

On earlier versions of MySQL, we can try joining to a subquery which finds the most recent record for each company:

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT Company, MAX(ID) AS MAX_ID
    FROM yourTable
    GROUP BY Company
) t2
    ON t1.Company = t2.Company AND
       t1.ID = t2.MAX_ID
WHERE
    t1.Result = 'B';

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • The first one is a bit out of my league... and not sure it would be fit inside a ```select * from table where Company in (________)``` kind of query. But the 2nd one is it!! I did have to change the ```t1.*``` to ```t1.Company``` so it would only output the just Company to fit in the blank above... but that's it!! Thank you!! – J-- Sep 02 '20 at 02:49