Question 20616 in Mysql Question posted in
The official MySQL documentation can be found here.

Including All Fields in MIN/Group By Query – Mysql

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
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    You can use your query result to select from the tabe again in order to get the complete rows:

    SELECT *
FROM checktable 
WHERE (marketname, selectionname, ltp) IN
(
  SELECT marketname, selectionname, MIN(ltp) AS min_ltp
  FROM checktable 
  WHERE marketstate = 'Not In Play' 
    AND selectionid <> 0 
    AND closetime <> ''
    AND result <> '' 
  GROUP BY marketname, selectionname 
  HAVING COUNT(*) > 900
)
ORDER BY marketname, selectionname;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search