Max (entry_date) doesn't return last date

Question

I have a table like this:


    entry_time student class grade
    ------------------------------
    1433793600 Dave    1      A
    1433793600 Sue     1      B
    1434994097 Dave    1      C
    1434994097 Sue     1      B

I'm using this command:

SELECT *, MAX(entry_time) from records GROUP BY student

I'm getting the first records for each student instead of the last. I've tried using MIN (entry_time) and get the same thing.

Are all classes '1'? If so, why bother storing that information at all? — Strawberry, Jun 26 '15 at 07:02

score 0 · Accepted Answer · answered Jun 26 '15 at 07:16

0

Try it using this query:

select * from 
record r 
where entry_time in 
(select max(entry_time) from record s where s.student = r.student group by student)

answered Jun 26 '15 at 07:16

Nikhil Batra

3,118
14
19

Looks okay for me if I understand the opener correctly: http://www.sqlfiddle.com/#!9/5b9a5/1 – bish Jun 26 '15 at 07:32
Note that a correlated subquery will be slow for larger data sets, but techincally I can see no problem with this solution (other than it ignores 'class', obviously) – Strawberry Jun 26 '15 at 08:56
Yea it has performance impact, but I tried my best to come up with correct solution. thanks @Strawberry pointing out and guiding :) – Nikhil Batra Jun 26 '15 at 09:22
Well, take a look at the link Barmar's provided above. The uncorrelated query solution is likely to be as fast as anything. – Strawberry Jun 26 '15 at 09:23

Mukesh Kalgude · Answer 2 · 2015-06-26T07:18:10.420

-1

try this query

select *,mdate from records a
join (
SELECT student, MAX(entry_time) as mdate
from records GROUP BY student) a on a.student=b.student and a.entry_time=b.entry_time

edited Jun 26 '15 at 07:18

answered Jun 26 '15 at 06:42

Mukesh Kalgude

4,814
2
17
32

Because your answer is slightly wrong (actually, several errors). It's a prompt for you to correct it – Strawberry Jun 26 '15 at 07:03
@MukeshKalgude: give space after join and ( and change one alaias as b instead of both a. – Zafar Malik Jun 26 '15 at 07:16
Yes. Those are some of the errors. – Strawberry Jun 27 '15 at 21:24

score -1 · Answer 3 · answered Jun 26 '15 at 06:44

-1

try below to get latest row (your query will provide only latest date in date column but not latest record)-

select student,grade,entry_time 
from (select student,grade,entry_time from records order by entry_time desc) a group by student

answered Jun 26 '15 at 06:44

Zafar Malik

6,734
2
19
30

may i know the reason of -ve vote. – Zafar Malik Jun 26 '15 at 06:59
Your solution relies upon an undocumented behaviour of MySQL (one which recent documentation suggests will be unavailable in the future) – Strawberry Jun 26 '15 at 07:12
i know it is not good solution for bulky tables but it will provide desired results if your table size is no too large. It can be a concern related with optimization but we can't say that it is not a solution. – Zafar Malik Jun 26 '15 at 07:28
The problem is not (primarily) about optimization. It's about guaranteeing the return of a correct result. While, in practice, all recent versions (pre 5.6 anyway) may return the expected result. The indication is that this will not continue. – Strawberry Jun 26 '15 at 08:48
@Strawberry: may you let me know (just for my knowledge) what can be change in next version as here no any specific function is using that functionality can be change here we are just doing reverse ordering in sub-query and then fetching results from this sub-query. – Zafar Malik Jun 26 '15 at 08:51
You're grouping the subquery on the assumption that this will net the 'first' row result for each student. While in practice that may work, according to the documentation, this behaviour cannot be guaranteed. – Strawberry Jun 26 '15 at 08:54
thanks @Strawberry for clarification. – Zafar Malik Jun 26 '15 at 09:01

Max (entry_date) doesn't return last date

3 Answers3