How to Select row with maximum date

Question

this is my table name tblPE

PEID |idnum  | PE_DATE_EXAM   | ATTENDANCE       
1    | 39    | 2014-08-01     | PRESENT       
2    | 42    | 2014-08-10     | ABSENT            
3    | 39    | 2014-08-12     | PRESENT           
4    | 43    | 2014-08-05     | PRESENT        
5    | 42    | 2014-07-15     | NULL           
6    | 39    | 2014-07-03     | ABSENT       
7    | 41    | 2014-08-01     | PRESENT

i want to select the maximum PE_DATE_EXAM value where idnum = 39 and ATTENDANCE=PReSENT

the result should be:

PEID |idnum  | PE_DATE_EXAM   | ATTENDANCE
-------------------------------------------
3    | 39    | 2014-08-12     | PRESENT

sagi · Answer 1 · 2016-08-16T11:31:16.493

5

For a single record return you can use LIMIT :

SELECT * FROM tblPE t
WHERE t.idnum = 39 AND t.ATTENDANCE = 'PRESENT'
ORDER BY t.PE_DATE_EXAM DESC
LIMIT 1;

If you want it dynamic for all idnum's , you can use NOT EXISTS() :

SELECT * FROM tblPE t
WHERE NOT EXISTS(SELECT 1 FROM tblPE s
                 WHERE t.idnum = s.idnum AND s.ATTENDANCE = 'PRESENT')
  AND t.attendance = 'PRESENT'

edited Aug 16 '16 at 11:31

answered Aug 16 '16 at 11:21

sagi

40,026
6
59
84

1

Accidentally you put `ORDER BY t.PE_DATE_EXAM DESC` before `WHERE ..` – 1000111 Aug 16 '16 at 11:26
i did waht u said but it got error.. – Strong Man Aug 16 '16 at 11:28
1

Error SQL query: Documentation SELECT * FROM tblPE t ORDER BY t.PE_DATE_EXAM DESC WHERE t.idnum = 39 AND t.ATTENDANCE = 'PRESENT' LIMIT 1 LIMIT 0, 25 MySQL said: Documentation #1064 - 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 'WHERE t.idnum =39 AND t.ATTENDANCE = 'PRESENT' LIMIT 0, 25' at line 1 – Strong Man Aug 16 '16 at 11:30

score 2 · Answer 2 · answered Aug 16 '16 at 11:57

2

You could use a correlated subquery to find the max dates for specific id(s). I personally find this to be more readable and much more straightforward. Also, this approach is typically language agnostic (assuming we are referring to relational SQL databases) https://en.wikipedia.org/wiki/Correlated_subquery

SELECT * 
FROM tblPE t1
WHERE t1.Date = (
                 SELECT MAX(Date)
                 FROM tblPE t2
                 WHERE t1.Id = t2.Id
                )
AND t1.Id = 39;

answered Aug 16 '16 at 11:57

Payne Miller

134
1
8

1

Yep, But it's just a bit slower than an uncorrelated query – Strawberry Aug 16 '16 at 13:17
@Strawberry true, you do take a bit of a performance hit, that's a good thing to point out! In my experience it only really becomes painful when you start to reach a fairly large data set size. But for small/medium size data sets, one should be ok. – Payne Miller Aug 16 '16 at 13:19

score 1 · Answer 3 · answered Aug 16 '16 at 11:59

1

For a more generic solution:

SELECT PEID, idnum, PE_DATE_EXAM, ATTENDANCE
FROM tblPE
WHERE idnum = 39 AND ATTENDANCE = 'PRESENT' AND PE_DATE_EXAM in (
   SELECT MAX(PE_DATE_EXAM)
   FROM tblPE
   WHERE idnum = 39 AND ATTENDANCE = 'PRESENT')

answered Aug 16 '16 at 11:59

Vivi Tsoumaki

121
5

score 0 · Answer 4 · answered Aug 16 '16 at 11:32

0

  SELECT top 1 * FROM tblPE t
  WHERE t.idnum = 39 AND t.ATTENDANCE = 'PRESENT' ORDER BY t.PE_DATE_EXAM DESC;

answered Aug 16 '16 at 11:32

Clar Cleetus

281
1
12

1

`TOP 1` Is SQL-Server syntax. – sagi Aug 16 '16 at 11:40

score 0 · Answer 5 · answered Aug 17 '16 at 04:48

select PEID,IDNUM,PE_DATE_EXAM,ATTENDANCE
from
(select PEID,IDNUM,PE_DATE_EXAM,ATTENDANCE,dense_rank() over(order by PE_DATE_EXAM desc) rnum
from TBLPE
order by PE_DATE_EXAM desc)
where rnum = '1';

This query is written based on your query result. If you need any clarifications let me know.

How to Select row with maximum date

5 Answers5

Linked