Including All Fields in MIN/Group By Query

Question

I have created my first MySQL query to get the minimum LTP by MarketName/SelectionName. It works ok but I'm struggling to work out how to get the rest of the fields from the original table (checktable).

I just want the records containing with the minimum LTP.

Can anyone point me in the right direction?

Thanks

SELECT MarketName, SelectionName, MIN(LTP) AS LTP 
FROM checktable 
WHERE MarketState = "Not In Play" AND SelectionID <> 0 AND CloseTime <> "" AND Result <> "" 
GROUP BY MarketName, SelectionName HAVING COUNT(*) > 900

Answer 1

Use window functions ROW_NUMBER() and COUNT():

WITH cte AS (
  SELECT *, 
         ROW_NUMBER() OVER (PARTITION BY MarketName, SelectionName ORDER BY LTP, checktable) AS rn,
         COUNT(*) OVER (PARTITION BY MarketName, SelectionName) AS count 
  FROM checktable 
  WHERE MarketState = 'Not In Play' AND SelectionID <> 0 AND CloseTime <> '' AND Result <> '' 
)
SELECT * -- here you can select only the columns that you need
FROM cte
WHERE rn = 1 AND count > 900;