Avatar of firekiller15
firekiller15 asked on

How to query max date in mysql

MYSQL

how to write a select query to get max date??

can i write like this

select studentID from student where TO_CHAR(enrolldate,'YYYYMMDD');

or

select studentID from studnet where MAX(enrolldate,'YYYYMMDD');


which one is correct?
MySQL Server

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

neither syntax are valid.

possibly one of these is what you are looking for:
select studentid, max(enrolldate) from student group by studentid; 
 
select studentid,enrolldate from student order by enrolldate desc limit 1;

Open in new window

Guy Hengel [angelIII / a3]

if neither works as you expect, you will need to explain more detailled what exactly you do expect.
ASKER
firekiller15

i want to pull the enrolled date that student enrolled a unit
i only want to check specific student so i need to pull studentID
i also interested only the maximum enrolled date only

the enroll date and student id is not in the same table as i do like this


 SELECT U.ENROLLDATE
 FROM STUDENT S, UNIT U
 WHERE S.STUDENTID = 1234567 AND U.COURSEID= C.COURSEID AND TO_CHAR(U.ENROLLDATE,'YYYYMMDD')=
      (SELECT MAX(TO_CHAR(U.ENROLLDATE,'YYYYMMDD'))
        FROM UNIT U, STUDENT S
        WHERES S.STUDENTID = 1234567 AND U.COURSEID= C.COURSEID);
 
But this i receive error saying that
TO_CHAR does not exist
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
firekiller15

TO_CHAR is to get date in YYYYMMDD format or other type of format  but in mysql the syntax to get such format is not TO_CHAR, what is it
ASKER
firekiller15

>>SELECT MAX(U.ENROLLDATE) ENROLLDATE)
what is this mean
you mean select max(u.enrolldate) as enrolldate?
Guy Hengel [angelIII / a3]

>TO_CHAR is to get date in YYYYMMDD format or other type of format  but in mysql the syntax to get such format is not TO_CHAR, what is it

that would be date_format function:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format

>>SELECT MAX(U.ENROLLDATE) ENROLLDATE)
what is this mean you mean select max(u.enrolldate) as enrolldate?

that will return the Max() value of enrolldate for that studentid.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
firekiller15

How about if i want to add age to the query as follow

SELECT MAX(U.ENROLLDATE) AS ENROLLDATE, AGE
 FROM STUDENT S
 JOIN UNIT U
   ON U.COURSEID= C.COURSEID
 WHERE S.STUDENTID = 1234567
 
IS it possible
ASKER
firekiller15

I mean the syntax
>>SELECT MAX(U.ENROLLDATE) ENROLLDATE)
correct
or typo
Guy Hengel [angelIII / a3]

it's possible to add the age of the student:
SELECT MAX(U.ENROLLDATE) AS ENROLLDATE, S.AGE
 FROM STUDENT S
 JOIN UNIT U
   ON U.COURSEID= C.COURSEID
 WHERE S.STUDENTID = 1234567 
GROUP BY S.AGE

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck