Complex query

Hi everybody,

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

FRANK?? (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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Sommarskog, thanks for your posting. I may not clearly describe my
situation. I want to show the previous quiz result with the current
quiz detail. If I show the 1st quiz result, there will be no previous
quiz date and time; If I show the 2nd quiz result, I can show 1st quiz
date and 1st quiz time as well, and so on. Is your solution still works
in this situation?|||It will be much better if u can give me some idea if I would like to do
this:

Previous quiz date | Previous quiz grade | Date | Time | Name | Grade |
Next quiz date | Next quiz grade

That is, if I show the 4th quiz detail, it will display the 3rd quiz
date and grade and 5th quiz date and grade as well. Is that impossible?

Please let me know if u want more information. Many thanks.

FRANK|||?? (lokalun@.gmail.com) writes:
> It will be much better if u can give me some idea if I would like to do
> this:
> Previous quiz date | Previous quiz grade | Date | Time | Name | Grade |
> Next quiz date | Next quiz grade
> That is, if I show the 4th quiz detail, it will display the 3rd quiz
> date and grade and 5th quiz date and grade as well. Is that impossible?
> Please let me know if u want more information. Many thanks.

It is at this point that I will ask to provide:

o CREATE TABLE statements for your table.
o INSERT statemetns with sample data.
o The desired data given the sample.

This makes it easy to copy-and-paste test data into a query tool, and
develop a tested solution.

Moreover, if you don't give us a clear specification of what you want,
you cannot not get what you want.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You also do not know that rows are not records, what reserved words are
or how to name data elements. In T-SQL, there is s DATETIME data type
which is like the Standard SQL's TIMESTAMP.

CREATE TABLE QuizScores
(student_name CHAR(20) NOT NULL,
quiz_date DATETIME NOT NULL,
quiz_grade INTEGER NOT NULL,
PRIMARY KEY (student_name, quiz_date));

We already posted several verisions of this problem. Look for a posting
about the 2nd edition of my Puzzles book.