Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Looper
Yak Posting Veteran
68 Posts |
 Posted - 2010-10-08 : 08:26:27
|
| I have a table with the following fieldsRowNumber, EmployeeId, Complete, UpdateAddress.The RowNumber is unique to the table i.e 1, 2, 3 etc.The employeeId is unique to an employee but many rows can exist in the table for an employee.Many rows can exist for other employeeIds also.I want to write a query that will select the last row for an employee where complete = 1 and UpdateAddress = 1. Nb - this might not be the last row in the table for that employeeId.eg. row 1 might have complete = 1 and UpdateAddress = 1 but row 2 might be Complete = 0 and UpdateAddress = 0 for that employee.I have no idea how to do this can anyone help? |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2010-10-08 : 11:33:29
|
if you can post some sample data it would be great. otherwise you should select last record based on either rownumber or by updateaddress.try this:selectemployeeID,max(rownumber) as last_recordfrom MyTablewhere complete = 1 and updateAddress = 1group by employeeID |
 |
|
|
kunal.mehta
Yak Posting Veteran
83 Posts |
Posted - 2010-10-11 : 03:22:27
|
| sample data pls |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2010-10-11 : 08:40:15
|
| try like this too select * from(select *, row_number() over(partition by employeeid order by row_number) as ridfrom tablename where complete = 1 and UpdateAddress = 1)s where rid = 1 |
|
|
|
|
|
|
Complex Query