Select issue - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select issue

Author

Topic

ramsesiitr
Starting Member

22 Posts

Posted - 2009-01-21 : 09:14:47

Hi all,
I have a table which keeps employees and their transactions. The transactions for each employee is listed as numbers. The table looks like this:

+-------------------------------+
|emloyee_id | trans_id | number |
|--------------------------------
| 1000 | 10 | 1 |
|--------------------------------
| 1000 | 11 | 2 |
|--------------------------------
| 1000 | 12 | 3 |
|--------------------------------
| 1001 | 10 | 1 |
|--------------------------------
| 1002 | 15 | 1 |
|--------------------------------
| 1002 | 18 | 2 |
+-------------------------------+

"SELECT employee_id,trans_id,number
FROM dbo.employeeTransaction_tb GROUP BY employee_id,trans_id,number ORDER BY emloyee_id asc,number desc"

When I execute the query above, I get the employees with their last transaction is on top of the other transactions. What I need to do is, getting only the last transaction of each employee. How can I do that?

Any suggestion would be appreciated
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:17:50

[code]
SELECT employee_id,trans_id,number
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY trans_id DESC) AS Seq,employee_id,trans_id,number
FROM dbo.employeeTransaction_tb
)t
WHERE Seq=1
[/code]