select first occurance in sql query

I have a table named Audit data  (all fields defined as vharchar(50))

Here is some sample data

IDNum   FirstName   LastName  Department       Day worked   DeptHead
Chi01      John            Doe            Accounting         Monday         Jim Smith
Chi01      John            Doe            Accounting         Tuesday         Jim  Smith
Chi01      John            Doe            Accounting         Monday         Jim Smith
Chi02      Jane            Doe            Maintenance      Tuesday         Joe Smith


I am trying to write a query that returns the first occurence of IDNum  regardless of other field values

Using above example

IDNum   FirstName   LastName  Department       DayWorked
Chi01      John            Doe            Accounting         Monday
Chi02      Jane            Doe            Maintenance      Tuesday

(Note: DayWorked value returned in the single row for chi01 doesn't matter...)

I tried several combinations of group by,  min, max   etc but not working

For records with same IDNUM, FIRSTNAME, LASTNAME,  The Department and DayWorked values may or may not be the same
johnnyg123Asked:
Who is Participating?
 
Ken ButtersConnect With a Mentor Commented:
Yep... forgot the order by... added it here.

select idnum, firstname, lastname, department, dayworked from
     (select row_number() over (partition by idnum, firstname, lastname order by department, Dayworked) as rn,
     idnum, firstname, lastname, department, dayworked from AuditData)
where rn = 1;
0
 
Ken ButtersCommented:
Something like this should work.

select idnum, firstname, lastname, department, dayworked from
     select row_number() over (partition by idnum, firstname, lastname) as rn,
     idnum, firstname, lastname, department, dayworked from AuditData
where rn = 1;
0
 
djsharmaTechnical ConsultantCommented:
Have you tried Distinct Statement ?

Please check
http://www.w3schools.com/sql/sql_distinct.asp
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Aneesh RetnakaranDatabase AdministratorCommented:
select idnum, firstname, lastname, department, dayworked from
(
     select row_number() over (partition by idnum, firstname, lastname ORDER BY CASE(dayworked) WHEN 'Monday' THEN 1 WHEN 'Tuesday' THEN 2 WHEN 'Wednesday' THEN 3 WHEN 'Thursday' THEN 4 ELSE 5 END asc ) as rn,
     idnum, firstname, lastname, department, dayworked from AuditData
)A    
where rn = 1;
0
 
johnnyg123Author Commented:
Thanks for the responses!


buttersk,

Tried the above query and got the following errors

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Msg 4112, Level 15, State 1, Line 2
The ranking function "row_number" must have an ORDER BY clause.

dssharma,

Distinct will not solve my problem because as I mentioned above the data may or may not be same so not a matter of filtering out duplicate rows
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>buttersk
 it still wont work, you need to specify a case statement in the order by part as I have done
0
 
Ken ButtersCommented:
You don't need the case statement... because from what I understand... it doesn't matter which "day worked" row is returned.
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.