Try to search your question here, if you can't find : Ask Any Question Now ?

select the latest row with a specific condition

HomeCategory: stackoverflowselect the latest row with a specific condition
bhawya asked 2 weeks ago

I have table 1 of task progress like this
Table 1. Task

ID | TaskID | IsPlanned | MondayOfWeekDate| Progress | UserID | TaskCreationDate
--------------------------------------------------------------------------------------------
18998 9917       1           2019-04-01    0           1150        2019-04-04 10:47:52.177

and I need to pick task progresses which has progress < 100 and TaskCreationDate is latest

I have tried using group by and max but i still get duplicated result

Here is my SQL so far

select TaskProgress.* 
from 
    (select 
         TaskID, Max(Progress) as Progress, Max(TaskCreationDate) as TaskCreationDate 
     from 
         TaskProgress
     group by 
         TaskID) as MaxValueTable 
join 
    TaskProgress on TaskProgress.TaskID = MaxValueTable.TaskID
                 and MaxValueTable.Progress = TaskProgress.Progress
                 and TaskProgress.Progress < 100

I expected to get task with latest date of creation and highest progress point

ID    | TaskID | IsPlanned | MondayOfWeekDate| Progress | UserID | TaskCreationDate 
--------------------------------------------------------------------------------------------
28885   19500       0           2019-03-25       90        1717   2019-03-25 07:57:36.560

But I still get this

ID    | TaskID | IsPlanned | MondayOfWeekDate| Progress | UserID | TaskCreationDate 
--------------------------------------------------------------------------------------------
28885   19500       0           2019-03-25       90        1717   2019-03-25 07:57:36.560
28696   19500       0           2019-03-11       90        1717   2019-03-16 20:57:57.373
28569   19500       0           2019-03-04       90        1717   2019-03-09 11:29:49.010
28485   19500       0           2019-02-25       90        1717   2019-03-02 18:12:25.587
28368   19500       0           2019-02-18       90        1717   2019-02-23 17:59:21.973

Please help me correct. Thank you in advance.

1 Answers
Best Answer
Amit answered 2 weeks ago
Your Answer

4 + 13 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com