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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.