0o0o0 Posted June 6, 2015 Share Posted June 6, 2015 Basically ive followed all the coding for finding duplicates in a database and counting them.. yet Im using... SELECT *, HORSE, COUNT( * ) AS count FROM ULTIMATEHISTORICAL GROUP BY HORSE ORDER BY DATE DESC lets say theres an entry I know is in there 19 times.... and the latest entry was today... how does it grab all count and show me the lastest entry date? the above I would think would work.. but it only shows all entries from today as the singles.. like A 2015-06-06 count 1.. B 2015-06-06count 1 C 2015-06-06 count 1 ,,,, but no D 2015-06-06 count 19 just all the singles?.. if I do find the entrie manually in the database it grabs an entry from 2015-05-05 meanwhile I see 6 indentical entries created today 2015-06-06.. but its not using them. is this confusing?.. any help would be great thanks! Quote Link to comment Share on other sites More sharing options...
Barand Posted June 6, 2015 Share Posted June 6, 2015 Don't use * in the SELECT, specify the required columns. SELECT horse, , MAX(date) as latest , COUNT(*) as count FROM ULTIMATEHISTORICAL GROUP BY horse ORDER BY latest DESC Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted June 7, 2015 Author Share Posted June 7, 2015 (edited) Thanks, I dont know how many times ive said it, this is the best site ever! Edited June 7, 2015 by 0o0o0 Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted June 7, 2015 Author Share Posted June 7, 2015 (edited) Oh and of course it doesn't stop there. lol Im using SELECT HORSE, TRACK, RACE, JOCKEY, TRAINER, OWNER, MAX( DATE ) AS latest, COUNT( * ) AS count FROM ULTIMATEHISTORICAL GROUP BY HORSE ORDER BY latest DESC, TRACK ASC, RACE ASC, COUNT DESC works great..then I noticed.. MAX (DATE) AS latest... how do I get it to grab from that MAX DATE line.. TRACK, RACE, JOCKEY, TRAINER, OWNER from the DB for those lines.. the MAX DATE lines? Its not actually grabbing TRACK, RACE, JOCKEY, TRAINER, OWNER from the same MAX DATE AS latest lines.. Its grabbing them from other lines.. not the same MAX DATE AS LATEST lines.. Like lets say LUCKY count 9 and date 2015-06-07 is today.. the track comes up as BELMONT... meanwhile the last race LUCKY raced was at belmont.. and today 2015-06-07 LUCKY runs at CHURCHILL track.. so "TRACK" isnt grabbing "TRACK" from the line where MAX(DATE) AS lastest is on. get me? need it too do that as well as jockey trainer etc.. also. Thanks again! Edited June 7, 2015 by 0o0o0 Quote Link to comment Share on other sites More sharing options...
Barand Posted June 7, 2015 Share Posted June 7, 2015 Use a table subquery to get the aggregated stuff and match the main table against that on horse and date SELECT uh.HORSE , uh.TRACK , uh.RACE , uh.JOCKEY , uh.TRAINER , uh.OWNER , uh.DATE, , tots.count FROM ULTIMATEHISTORICAL uh INNER JOIN ( SELECT HORSE, , MAX( DATE ) AS DATE , COUNT( * ) AS count FROM ULTIMATEHISTORICAL GROUP BY HORSE ) tots USING (HORSE, DATE) ORDER BY DATE DESC, TRACK ASC, RACE ASC, COUNT DESC Quote Link to comment Share on other sites More sharing options...
0o0o0 Posted June 7, 2015 Author Share Posted June 7, 2015 Thanks!!! and thanks for leaving a few commas in there so I could at least feel semi smart at something in php Your awesome Barand Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.