To retrive the data as latest datetime

Question

1.00/5 (1 vote)

See more:

I need to fetch the data whose latest datetime who status 1.

Name | Teststatus | ExamDate | Subject
Johan | 0 | 2019-01-01 25:46 | Maths
Johan | 1 | 2019-01-01 25:48 | Maths
Johan | 0 | 2019-01-01 25:47 | Maths
Ram | 0 | 2019-01-01 25:13 | Maths
Ram | 0 | 2019-01-01 25:45 | Maths
Ram | 1 | 2019-01-01 25:42 | Maths

What I have tried:

select Et.Name, Et.Status, Max(Et.ExamDate)
from Exam Et join Subject S on Et.Code =S.code
group by Et,Name,Et.Status

Name | Teststatus | ExamDate
Johan | 1 | 2019-01-01 25:48
Ram | 0 | 2019-01-01 25:45
Ram | 1 | 2019-01-01 25:42
Johan | 0 | 2019-01-01 25:47

But i need to get latest datetime and who has passed in exam

Name | Teststatus | ExamDate
Johan | 1 | 2019-01-01 25:48

Posted 4-May-19 20:13pm

Member 12640353

Updated 4-May-19 21:14pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2019-05-04T20:23:00

GROUP BY doesn't do what you want - particularly not like that. Have a look here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] which explains what it does do!

You need to think about your data and what you want to retrieve:

"Passed test"

and

"Latest date"

So do that:

SQL

SELECT TOP 1 Name, TestStatus, ExamDate FROM Exam
WHERE TestStatus = 1
ORDER BY ExamDate DESC

TOP 1 says "return only the first value", the WHERE restricts it to passed tests, and the ORDER BY controls the order in which rows are returned.

Christian Graus · Answer 2 · 2019-05-04T20:25:00

Solution 2

SQL

with co
(
Select code, max(examdate) as examdate from exam group by code
)
select et.name, et.status, c.examdate
join co c on et.code = c.code

Why did you join on subject if you don't select from it?

you can also join to another table to pull if they passed, but you didn't show me how

Posted 4-May-19 20:25pm

Christian Graus

Updated 4-May-19 21:49pm

v2