[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

select first occurance in sql query

Posted on 2012-08-31
8
Medium Priority
?
657 Views
Last Modified: 2012-08-31
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
0
Comment
Question by:johnnyg123
8 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 38355654
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
 
LVL 9

Expert Comment

by:djsharma
ID: 38355667
Have you tried Distinct Statement ?

Please check
http://www.w3schools.com/sql/sql_distinct.asp
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38355693
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:johnnyg123
ID: 38355732
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 38355809
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 1000 total points
ID: 38355812
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38355884
>buttersk
 it still wont work, you need to specify a case statement in the order by part as I have done
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 38355912
You don't need the case statement... because from what I understand... it doesn't matter which "day worked" row is returned.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

829 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