Get last 3 rundates and userid from runlog table

I need to get the last 3 users and their run dates for the programs we created.

For example: I have the data as shown below in the table

LogKey UserId   Program                    DateRun
-------------------------------------------------------------------
147715 john     L:\ApplicationReports4.rpt 2017-03-27 20:16:09.000
147796 marta    P:\ApplicationReports4.rpt 2017-03-29 11:11:15.000
147798 dealo    P:\ApplicationReports7.rpt 2017-03-29 11:15:15.000
149339 rubyo    K:\ApplicationReports4.rpt 2017-05-12 09:57:53.000
149340 runia    P:\ApplicationReports8.rpt 2017-05-12 09:58:02.000
150925 luna     P:\ApplicationReports6.rpt 2017-07-04 12:51:23.000
151969 rubyo    P:\ApplicationReports9.rpt 2017-08-09 16:09:23.000
152061 luna     M:\ApplicationReports4.rpt 2017-08-10 11:54:19.000
154722 john     P:\ApplicationReports2.rpt 2017-11-10 16:01:11.000
154723 sam      P:\ApplicationReports4.rpt 2017-11-10 16:01:17.000
154742 sam      P:\ApplicationReports1.rpt 2017-11-13 09:00:44.000


The Resultset should be as shown below:

154722 john     P:\ApplicationReports2.rpt 2017-11-10 16:01:11.000
154723 sam      P:\ApplicationReports4.rpt 2017-11-10 16:01:17.000
154742 sam      P:\ApplicationReports1.rpt 2017-11-13 09:00:44.000
Jen RAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Okay. Please use this-

SELECT p.* FROM 
(
	SELECT DISTINCT Program 
	FROM YourLobTable
)b
CROSS APPLY
(
	SELECT TOP 3 *
	FROM YourLobTable k
	WHERE b.Program = k.Program
)p

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Please use this -

TOP 3 with ORDER BY on date...DESC

SELECT TOP 3 * 
FROM YourLobTable
ORDER BY DateRun DESC

Open in new window

0
 
Jen RAuthor Commented:
Sorry i forgot to add that it i need to get the last 3 rundates and userid for each program....

For example:
The Resultset should be as shown below:

154722 john     P:\ApplicationReports2.rpt 2017-11-10 16:01:11.000
154723 sam      P:\ApplicationReports2.rpt 2017-11-10 16:01:17.000
154742 sam      P:\ApplicationReports2.rpt 2017-11-13 09:00:44.000

154721 john     P:\ApplicationReports4.rpt 2017-11-10 16:01:11.000
154726 sam      P:\ApplicationReports4.rpt 2017-11-10 16:01:17.000
154743 sam      P:\ApplicationReports4.rpt 2017-11-13 09:00:44.000

154728 john     P:\ApplicationReports1.rpt 2017-11-10 16:01:11.000
154727 sam      P:\ApplicationReports1.rpt 2017-11-10 16:01:17.000
154744 sam      P:\ApplicationReports1.rpt 2017-11-13 09:00:44.000
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jen RAuthor Commented:
My requirement is that I need to get the last 3 users of each program and when they last ran it...
0
 
Jen RAuthor Commented:
Thank you very much for the solution.
Is it possible to get the resultset to be displayed like shown below?

Program                                     LastDateRun1   LastRunUser1  LastDateRun2    LastRunUser2  LastDateRun3   LastRunUser3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

P:\ApplicationReports2.rpt      2017-11-10         john                    2017-11-11       sam                   2017-11-13         sam
P:\ApplicationReports4.rpt      2017-11-12         john                    2017-11-14       sam                   2017-11-15         luna
P:\ApplicationReports1.rpt      2017-11-14         john                    2017-11-15       luna                   2017-11-16         sam
0
 
Pawan KumarDatabase ExpertCommented:
Yes it is possible. This is follow up question so could you please open a new question....
Thanks
0
 
Jen RAuthor Commented:
Thank you very much !
I will open a follow up question for this. Thanks!
0
 
Pawan KumarDatabase ExpertCommented:
Welcome glad to help as always :)

. Please comment here the url of the next question. Thanks
0
 
Pawan KumarDatabase ExpertCommented:
working. :)
0
 
Jen RAuthor Commented:
Yes...I have created the follow up question. Thank you:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.