Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selecting a Value of the Order Within a Group


Selecting a Value of the Order Within a Group

Author
Message
dkrumholz
dkrumholz
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 9
I have a table of transactions that includes student ids and dates. I need to select all records from the table and include a new value that represents the sequential transaction number for each student with the oldest transaction for each student being numbered one, the next oldest numbered two and so on. So the result should look like student1, 10/1/2000, 1, student1, 10/15/2000, 2, student1, 2/12/2001, 3, student2, 9/1/1999, 1, student2 10/2/2000, 2, student2 , 12/15/2000, 3, student2, 11/4/2001, 4 and so on.
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Something like this:

Select *, row_number() Over(Partition by StudentID Order by EntryDate)
From Students



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 Visits: 4101
Sorry RBarry, but this is the SQL 2000 forum. ROW_NUMBER() isn't supported in 2000.

this solution will work though ...


DECLARE @students TABLE (student VARCHAR(20), attendDate DATETIME)

INSERT @students
SELECT 'student1','1/1/2008' UNION
SELECT 'student1','2/1/2008' UNION
SELECT 'student1','3/1/2008' UNION
SELECT 'student2','2/1/2008' UNION
SELECT 'student2','3/1/2008' UNION
SELECT 'student2','4/1/2008' UNION
SELECT 'student2','5/1/2008' UNION
SELECT 'student3','3/1/2008' UNION
SELECT 'student3','5/1/2008'

SELECT
(SELECT COUNT(*) FROM @students WHERE student = s.student AND attendDate < s.attendDate) + 1 AS row
,student
,attendDate
FROM
@students AS s



______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Jason Selburg (4/30/2008)
Sorry RBarry, but this is the SQL 2000 forum. ROW_NUMBER() isn't supported in 2000.


Rats! I forgot to check, sorry.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 Visits: 4101
yeah, I got burned by that a few times before I started checking... LOL BigGrin

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
dkrumholz
dkrumholz
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 9
I don't think that solves the problem. I guess I didn't exaplin it clearly.

My data looks like

Student Date
Student1 12/5/2000
Student2 1/5/2000
Student1 1/7/2001
Student1 6/5/2001
Student2 4/14/2000
Student1 6/7/2002

I need the data grouped by student in date order with the records for each student sequentially numbered:

Student1 12/5/2000 1
Student1 1/7/2001 2
Student1 6/5/2001 3
Student1 6/7/2002 4
Student2 1/5/2000 1
Student2 4/14/2000 2

How can I get that result?
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Just add an "ORDER BY Student, AttendDate" to the end of Jason's (Joel's?) query.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
dkrumholz
dkrumholz
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 9
Thank you all. I love you.
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 Visits: 4101
rbarryyoung (4/30/2008)
Just add an "ORDER BY Student, AttendDate" to the end of Jason's (Joel's?) query.


Tongue

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
dineshrajan
dineshrajan
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 212
declare @tab table
(
id int identity(1,1),
names varchar(100),
dates datetime
)


insert into @tab(names,dates)
values('student1',DATEADD(day,10,getdate()))
,('student1',DATEADD(day,5,getdate()))
,('student1',DATEADD(day,15,getdate()))
,('student2',DATEADD(day,10,getdate()))
,('student2',DATEADD(day,5,getdate()))
,('student3',DATEADD(day,11,getdate()))
,('student4',DATEADD(day,15,getdate()))
,('student4',DATEADD(day,10,getdate()))

select id,names,dates,inc=pt.inc
from
@tab t
cross apply
(
select [inc]= COUNT(1)+1 from @tab pt where pt.id <t.id and pt.names = t.names
)pt
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search