By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

SQL query

P: 1
Hi All,

I have table as below

Table name : Name
Roll_No Name
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
6 fff

Table name : Marks

Roll_No Marks Exam_dt
1 40 6/05/2006
1 50 5/05/2006
1 60 2/03/2006
1 37 2/02/2006
2 45 1/03/2006
2 67 6/05/2006
2 89 5/05/2006
3 67 2/03/2006
3 45 2/02/2006
3 90 1/03/2006
4 78 6/05/2006
4 59 5/05/2006
4 60 2/03/2006
5 40 1/03/2006
5 30 6/05/2006

How to write SQL query to get fields Roll_No, Name and Marks of last two exams (based on date) given by each candidate.

Thanks.
Oct 7 '06 #1
Share this Question
Share on Google+
1 Reply


PEB
Expert 100+
P: 1,418
PEB
Hi,

Use the query builder add the 2 tables.

IN SQL View Assign New Names of both tables
[Name] AS [A]
[Marks] AS [b]

Make join btwn Roll_No

Under Exam_dt specify in the criteria:
IN (SELECT TOP 2 [Exam_dt] FROM [Marks] WHERE Roll_No=[b].[Roll_No] ORDER BY [Exam_dt] DESC)

Hope this helps

:)

Hi All,

I have table as below in the 2 tables

Table name : Name
Roll_No Name
1 aaa
2 bbb
3 ccc
4 ddd
5 eee
6 fff

Table name : Marks

Roll_No Marks Exam_dt
1 40 6/05/2006
1 50 5/05/2006
1 60 2/03/2006
1 37 2/02/2006
2 45 1/03/2006
2 67 6/05/2006
2 89 5/05/2006
3 67 2/03/2006
3 45 2/02/2006
3 90 1/03/2006
4 78 6/05/2006
4 59 5/05/2006
4 60 2/03/2006
5 40 1/03/2006
5 30 6/05/2006

How to write SQL query to get fields Roll_No, Name and Marks of last two exams (based on date) given by each candidate.

Thanks.
Oct 8 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.