counting rows while selecting?

Last Post 28 Jan 2008 02:38 AM by SwePeso. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
fcaserio
New Member
New Member

--
23 Jan 2008 10:45 AM
Is it posible on a query ordered by employee name, for exemple, that MSSQL gives me a position of which employee is contained on the recordset?

that's my query (simplified)

SELECT name, payment, category FROM employee_payments ORDER BY name

John, 1000, sallary
Peter, 1500, sallary
Peter, 500, other
Zeus, 1000, sallary

I want to add another field giving me the position of the employee on the recordset:

John, 1000, sallary, 1
Peter, 1500, sallary, 2
Peter, 500, other, 2
Zeus, 1000, sallary, 3

Is it posible?
SQLUSA
New Member
New Member

--
23 Jan 2008 01:04 PM
Yes, SQL Server 2005 has the ROW_NUMBER() feature.


Here is how you use it: http://www.sqlusa.com/bestpractices...anagement/



Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005highperformance/ The Best SQL Server 2005 Training in the World!

SwePeso
New Member
New Member

--
28 Jan 2008 02:38 AM
SELECT name, payment, category , dense_rank() OVER (order by name) AS Position
FROM employee_payments ORDER BY name
SwePeso
New Member
New Member

--
01 Feb 2008 02:36 PM
Thanks
SwePeso
New Member
New Member

--
04 Feb 2008 02:09 AM
There is RANK also.

Besides ROW_NUMBER(), my new favorit is NTILE() function.
You are not authorized to post a reply.

Acceptable Use Policy