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 |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-04-09 : 18:55:29
|
| Hiya,Can somebody help me with this: I'd like to number the rows in a query sequentially, but only within groups. i.e. ordinarily, to number a resultset, a temp table with an identity column would be used, but I'd like to start the numbering again when a group value e.g. a date column, changes.(In addition, I'd like to have a column in the resultset which is computed from the numbered column above, so the numbered column must be a column which may be referenced in another column, not a computed column with an alias)P.S. The solution needn't be a single SELECT. A stored proc is fine.Sarah Berger MCSD |
|
|
marileng
Starting Member
28 Posts |
Posted - 2002-04-09 : 21:12:00
|
| If you are doing this for the purpose of a report It can be done in crystal reports.Make a running total field and type of summary is countthen evaluate for each record and reset on change of group like the date |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-04-09 : 22:08:49
|
| Actually, I am using ActiveReports by Data Dynamics, and thanks for the tipoff. I could use client-side logic for this, as I can code the report. (An unbound field doesn't completely solve the problem) I anyone has a SQL answer, though, I'm all ears.Sarah Berger MCSD |
|
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2002-04-09 : 22:42:40
|
quote:
I anyone has a SQL answer, though, I'm all ears.
Since you did not provide a schema, I used the employees table in northwind grouping by title.use Northwindselect LastName , FirstName , Title , (select count(*) from Employees where Title = Emp.Title and EmployeeId <= Emp.EmployeeId) as TitleGroupfrom Employees as Empwhere Title is not nullorder by EmployeeId |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-10 : 00:35:11
|
We call it the Rank Operator  . Sqlteam's second reader's challenge was exactly on this lines. Check this http://www.sqlteam.com/item.asp?ItemID=6398 quote:
use Northwind select LastName , FirstName , Title , (select count(*) from Employees where Title = Emp.Title and EmployeeId <= Emp.EmployeeId) as TitleGroup from Employees as Emp where Title is not null order by EmployeeId
-------------------------------------------------------------- |
|
|
|
|
|
|
|
|
Numbering rows in query, within groups