Jump to content

min date SOLVED with thanks in the final post


niche

Recommended Posts

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 by niche
Link to comment
Share on other sites

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

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

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 by niche
Link to comment
Share on other sites

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

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 by niche
Link to comment
Share on other sites

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

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 by niche
Link to comment
Share on other sites

the data type for dob is date

 

EDIT:

 

Changing it to datetime had no effect.

Edited by niche
Link to comment
Share on other sites

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...