Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL Query for Top 1 for each date

Posted on 2012-03-13
2
Medium Priority
?
534 Views
Last Modified: 2012-08-13
Hello Experts,

I have a table called [Exam_Results], and it has to following colums:
[ID] int
[UserID] int
[ExamID] int
[Score] float
[Date] datetime

Users can take exams multiple times and the data looks as follows:

ID     UserID     ExamID     Score      Date
----------------------------------------------------------------------------
1          20            1               75          2012-02-15 13:30:00
2          20            1               83          2012-02-17 10:30:00
3          20            2               80          2012-02-15 13:30:00
4          54            3               64          2012-02-16 13:30:00
5          54            3               78          2012-02-20 14:30:00

What I am trying to get is the first time each user took the exam, so I want my select to return the top 1 result for each user.

So with the data above, my query would return:
UserID     ExamID     Score
-------------------------------------------
20                1              75
20                2              80
54                3              64

So, I need help to write a select query that for each User and Exam ID, will return the score from the first time the exam was completed by each user.
0
Comment
Question by:NursingCorp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 8

Accepted Solution

by:
fundacionrts earned 2000 total points
ID: 37717576
SELECT ER1.UserID, ER1.ExamID, ER1.Score
FROM Exam_Results AS ER1
WHERE ER1.Date <= (
SELECT MIN( ER2.Date )
FROM Exam_Results AS ER2
WHERE ER2.UserID = ER1.UserID
AND ER2.ExamID = ER1.ExamID )
0
 

Author Closing Comment

by:NursingCorp
ID: 37717665
Worked Perfectly - Thanks for the clear, quick solution!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question