sub select 2

[russland]

Hi,
I fail to reduce the values from table A. Why does it return 3(all) records instead of only 2? I though the exists check every row for true or false?!


query statement
--------------------------------------------------
select WBT_ID, fk_user_id, Score, Lesson_Time, Modified_Time
from TestResults A
where exists
(
select max(modified_time) as Modified_Time, WBT_ID
from TestResults B
where FK_User_ID = 'michael' and B.Modified_Time = A.Modified_Time
group by wbt_id
)



query results
--------------------------------------------------------
5 michael incomplete NULL 0000:03:40.00 2005-06-06 13:01:42.107
5 michael incomplete NULL 0000:04:00.00 2005-06-06 13:02:03.467
1 michael incomplete NULL 0000:01:00.00 2005-06-06 13:02:59.000

 

[JamesLean]

Do you want to return only the latest rows for each unique combination of wbt_id and user_id?

SELECT wbt_id, user_id, score, lesson_time, modified_time
FROM testresults a
WHERE modified_time = (
    SELECT MAX(modified_time)
    FROM testresults
    WHERE user_id = a.user_id
      AND wbt_id = a.wbt_id
  )

--James

 

[PHV]

Something like this ?
select A.WBT_ID, A.fk_user_id, A.Score, A.Lesson_Time, A.Modified_Time
from TestResults A inner join (
select WBT_ID, fk_user_id, max(modified_time) as Last_Time
from TestResults
group by WBT_ID, fk_user_id
) B on A.WBT_ID = B.WBT_ID and A.fk_user_id = B.fk_user_id and A.Modified_Time = B.Last_Time


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[russland]

neat ones! cheers for the quick help. (answer in 7/8 minutes, holy moley)