|
|
Posted by Erland Sommarskog on 04/02/06 16:45
?? (lokalun@gmail.com) writes:
> I have a table storing the quiz data of the students like this:
>
> Date | Time | Name | Grade
>
> I can show the data of specific student by the query below:
>
> SELECT *
> FROM tbl_quiz
> WHERE name ='xxx'
>
> Suppose I have 10 records for each student, and I wanna display the
> data like this:
>
> Previous quiz date | Previous quiz time | Date | Time | Name | Grade
>
> How can I make the "Previous quiz date" and "Previous quiz time"? How
> can I query it? Please advice
Is that date as 2006-04-02 and time as 14:30:21, or does any of them
include the other? Normally you store datetime values in one column in
SQL Server as there are no pure date and time data types. To make it
simpler, I'm assuming that time includes both date and time.
SELECT predate = c.date, prevtime = c.time,
a.date, a.time, a.name, a.grade
FROM tbl_quiz a
JOIN (SELECT time = MAX(time), name
FROM tbl_quiz
GROUP BY name) AS b ON a.name = b.name
JOIM tbl_quiz c ON c.name = b.name
AND c.time = (SELECT MAX(d.time)
FROM tbl_quiz d
WHERE d.name = b.name
AND d.time < b.time)
WHERE a.name = 'xxx'
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|