niche Posted September 24, 2014 Share Posted September 24, 2014 (edited) there's a bug when in MIN() when it comes to dates according to: http://bugs.mysql.com/bug.php?id=54784 The work around doesn't work on this code: SELECT id, MIN(dob AS CHAR) FROM vot_sim GROUP BY addr I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS CHAR) FROM vot_sim GROUP BY addr' at line 1 Any suggestions? EDIT: I've also unsuccessfully tried converting dob to a unix timestamp. Edited September 25, 2014 by niche Link to comment Share on other sites More sharing options...
justsomeguy Posted September 24, 2014 Share Posted September 24, 2014 You left out the cast function. Link to comment Share on other sites More sharing options...
niche Posted September 24, 2014 Author Share Posted September 24, 2014 Thanks. SELECT id, MIN(CAST(dob) AS CHAR) FROM vot_sim GROUP BY addr produces this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS CHAR) FROM vot_sim GROUP BY addr' at line 1 What do you think? Link to comment Share on other sites More sharing options...
Ingolme Posted September 24, 2014 Share Posted September 24, 2014 It probably has to do with the use of the AS operator. I suspect that having it outside the MIN() function will solve that. "CHAR" might be a keyword as well, so put it between backticks `CHAR` Link to comment Share on other sites More sharing options...
niche Posted September 24, 2014 Author Share Posted September 24, 2014 (edited) Tried with backticks: SELECT id, MIN(CAST(dob)) AS `CHAR` FROM vot_sim GROUP BY addr with a similar error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) AS `CHAR` FROM vot_sim GROUP BY addr' at line 1 I wonder if it's a version issue? The computer I'm working on is running mysql v 5.1.33 EDIT the ref in the OP is for v 5.5.3. Edited September 24, 2014 by niche Link to comment Share on other sites More sharing options...
justsomeguy Posted September 24, 2014 Share Posted September 24, 2014 It should be MIN(CAST(dob AS CHAR)) 1 Link to comment Share on other sites More sharing options...
Ingolme Posted September 24, 2014 Share Posted September 24, 2014 I'm not sure, but I thought the AS operator also worked on functions. The query SELECT COUNT(*) AS amount seemed to work for me. The last thing I can think of, given the position of the error, is that maybe "dob" is also a reserved word in SQL, so wrapping it in backticks `dob` would be a solution if that were the problem. Link to comment Share on other sites More sharing options...
niche Posted September 24, 2014 Author Share Posted September 24, 2014 (edited) that's good news the ref in the op said: MIN(CAST(x) AS CHAR) jsg wrote: MIN(CAST(x AS CHAR)) jsg's code doesn't throw an error. However, the work produces inaccurate results. These the dob: id fn ln addr dob Edit Delete 1 j smith 123 main 2014-09-24 Edit Delete 2 r smith 123 main 2014-09-23 Edit Delete 3 a jones 789 z st 1958-05-24 Edit Delete 4 b jones 789 z st 1958-05-25 SELECT id, MIN(CAST(dob AS CHAR)) FROM vot_sim GROUP BY addr returns id 1 and 3. I expected 2 and 3. What do you thinK? Edited September 24, 2014 by niche Link to comment Share on other sites More sharing options...
Ingolme Posted September 24, 2014 Share Posted September 24, 2014 You might want to order it differently. GROUP BY seems to take the first result of each distinct set. The first result of the first set is 1, the first result of the second set is 3. Try ORDER BY dob Link to comment Share on other sites More sharing options...
niche Posted September 24, 2014 Author Share Posted September 24, 2014 (edited) That selects all 4 rows. I need to GROUP BY addr and SELECT the dob within each group of the oldest person. Also, I just learned that MAX produces the same results as MIN! What are your thoughts? Edited September 24, 2014 by niche Link to comment Share on other sites More sharing options...
Ingolme Posted September 24, 2014 Share Posted September 24, 2014 What I meant was to add ORDER BY onto your existing query, in addition to GROUP BY. 1 Link to comment Share on other sites More sharing options...
justsomeguy Posted September 24, 2014 Share Posted September 24, 2014 I think the column in the group by needs to be in the select list also.SELECT id, addr, MIN(CAST(dob AS CHAR)) FROM vot_sim GROUP BY addrorSELECT id, addr, MIN(CAST(dob AS CHAR)) FROM vot_sim GROUP BY addr, id Link to comment Share on other sites More sharing options...
niche Posted September 24, 2014 Author Share Posted September 24, 2014 what about the min / max issue? I get the same results whether i use min or max? Link to comment Share on other sites More sharing options...
justsomeguy Posted September 24, 2014 Share Posted September 24, 2014 When you select the addr column also? Just out of curiosity, what is the data type of the dob column? Link to comment Share on other sites More sharing options...
justsomeguy Posted September 24, 2014 Share Posted September 24, 2014 That bug report indicates that it works correctly on 5.1: [24 Jun 2010 17:19] Valeriy KravchukAlso it works with 5.1, so this is a regression bug: 1 Link to comment Share on other sites More sharing options...
niche Posted September 25, 2014 Author Share Posted September 25, 2014 (edited) the data type for dob is date EDIT: Changing it to datetime had no effect. Edited September 25, 2014 by niche Link to comment Share on other sites More sharing options...
Ingolme Posted September 25, 2014 Share Posted September 25, 2014 Did you try this? SELECT id, MIN(CAST(dob AS CHAR)) FROM vot_sim GROUP BY addr ORDER BY dob 1 Link to comment Share on other sites More sharing options...
niche Posted September 25, 2014 Author Share Posted September 25, 2014 Yes it does! Great work per usual Fox and jsg. This site continues to be quite a backstop for me and I'm sure many others. Thanks again to both of you. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now