Click here to Skip to main content
11,565,672 members (47,754 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Consider my tables

table 1: assignmentquestion
select* from assignmentquestion
Output:
id     cid      aid Question
27      1       21  what is bca?
28      1       21  what is eee?
29      2       22  what is ba ?
30      2       22  what is aa?
31      4       23  what is bsc?
32      4       23  what is bbb?
35      1       24  what is dddd?
36      1       24  what is ggg?
37      4       23  what is ccc?
38      9       25  what is v?
39      1       21  what is ggg?
40      1       21  what is sas?
41      1       21  What is Inheritance?

table 2: assignmentanswer

select * from assignmentanswer

OutPut:

id   sid   aid  ans      date
138  1     21   1        2013-01-11 12:49:56.717 27
139  1     21   2        2013-01-11 12:49:57.123 28
140  6     24   ddd1     2013-01-11 12:50:44.610 35
141  6     24   ggg2     2013-01-11 12:50:45.010 36
142  6     21   sd       2013-01-11 15:27:24.427 27
143  6     21   asd      2013-01-11 15:27:24.850 28
144  6     21   fgdfgf   2013-01-11 15:27:25.277 39
145  6     21   dghfgfh  2013-01-11 15:27:25.700 40

My Query to get the last record from table assignmentquestion where the student have not attended the question.
41  1   21  What is Inheritance?
select asm.id,asm.aid,asm.question,asn.id,asn.aid,asn.answer from assignmentquestion asm
inner join assignmentanswer asn on asm.aid=asn.aid and asm.id=asn.qid where asn.sid=6 and asn.aid=21

Output:
id aid Question id aid ans
27 21 what is bca? 142 21 sd
28 21 what is eee? 143 21 asd
39 21 what is ggg? 144 21 fgdfgf
40 21 what is sas? 145 21 dghfgfh


My expected output is to get the last record of assignmentquestion table as:
 
41 1 21 What is Inheritance?
Posted 11-Jan-13 0:26am
sahmed4567
Edited 11-Jan-13 5:38am
RedDk11.5K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

If the last record means that you want the greatest id, then you can order the set in reverse order and take TOP 1 record. So something like:
select top (1)
       asm.id,
       asm.aid,
       asm.question,
       asn.id,
       asn.aid,
       asn.answer 
from       assignmentquestion asm
inner join assignmentanswer asn 
on     asm.aid=asn.aid 
   and asm.id=asn.qid 
where asn.sid = 6 
and   asn.aid = 21
order by asm.id desc
  Permalink  
Comments
Maciej Los at 11-Jan-13 12:52pm
   
Good idea, +5!
Mika Wendelius at 11-Jan-13 13:20pm
   
Thanks :)
sahmed4 at 12-Jan-13 0:16am
   
Hi Mika Wendelius,Maceij Los...I hope i am near to achieve my solution from your help.Let me give you a clear explanation of my requirement.
Whenever any student(s1) logs in and attend an exam from online.At first time in my question table if i have only three questions and who ever the student logs in a system to attend an exam he may be able to attend only these 3 questions which ever is in my question table. In future i have added two more questions in my question table and now totally there are 5 questions and the student s1 has already attended first 3 questions so when next time the student s1 logs in to attend an exam he should be able to view only the updated questions and not those questions (first 3) which he has already attended. I hope it's my requirement is clear enough to understand. Please provide the solution. In above solution of your's will give me the top most record from my question table but if i have added three new questions then still it will highlight me only the latest record from those newly added 3 questions.
Mika Wendelius at 12-Jan-13 2:44am
   
If I understood correctly, I'd suggest that you put a date field to the table where you record the student login which defines when the user has last logged in and a date field to the question which defines when the question is added.

Using these two dates you can select the records which have been added after the last login of the student.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If time is essential, try this:

DECLARE @maxDate DATE
 
SELECT @maxDate = MAX([date])
FROM assignmentanswer AS asn
WHERE asn.sid=6 and asn.aid=21
 

SELECT asm.id,asm.aid,asm.question,asn.id,asn.aid,asn.answer
FROM assignmentquestion asm
    INNER JOIN assignmentanswer asn on asm.aid=asn.aid and asm.id=asn.qid
WHERE asn.sid=6 and asn.aid=21 and asn.date = @maxDate
  Permalink  
Comments
Mika Wendelius at 11-Jan-13 13:21pm
   
That would work fine in case of the date field, 5+
Maciej Los at 11-Jan-13 17:13pm
   
Thank you, Mika ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi this is my table structure for assignmentanswer which stores questionId yesterday i forgot to highlith the Qid column please go through it..
Select * from assignmentanswer

id  sid aid ans         date                    Qid  
138 1   21  1     2013-01-11 12:49:56.717       27
139 1   21  2     2013-01-11 12:49:57.123       28
140 6   24  ddd1      2013-01-11 12:50:44.610   35
141 6   24  ggg2      2013-01-11 12:50:45.010   36
142 6   21  sd    2013-01-11 15:27:24.427       27
143 6   21  asd   2013-01-11 15:27:24.850       28
144 6   21  fgdfgf    2013-01-11 15:27:25.277   39
145 6   21  dghfgfh   2013-01-11 15:27:25.700   40
146 1   23  gfj   2013-01-12 11:20:18.800       31
147 1   23  gf    2013-01-12 11:20:23.430       32
148 1   23  fg    2013-01-12 11:20:29.257       37
149 1   22  sda   2013-01-12 11:26:40.590       29
150 1   22  ertr      2013-01-12 11:26:40.973   30
  Permalink  
v2

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

  Print Answers RSS


Advertise | Privacy | Mobile
Web03 | 2.8.150624.2 | Last Updated 12 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100