Selecting a Value of the Order Within a Group

Question

Post reply

Selecting a Value of the Order Within a Group

dkrumholz

SSC Veteran

Points: 211
More actions
April 30, 2008 at 3:42 pm

#185034

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Something like this:

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

From Students

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

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[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

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.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

yeah, I got burned by that a few times before I started checking... LOL 😀

______________________________________________________________________

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

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

Jason L. Selburg

dkrumholz SSC Veteran Points: 211 More actions · Answer 5

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 SSC Guru Points: 143329 More actions · Answer 6

Just add an "ORDER BY Student, AttendDate" to the end of Jason's (Joel's?) query.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

dkrumholz SSC Veteran Points: 211 More actions · Answer 7

dkrumholz

SSC Veteran

Points: 211

May 1, 2008 at 4:46 am

#809476

Thank you all. I love you.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 8

rbarryyoung (4/30/2008)
Just add an "ORDER BY Student, AttendDate" to the end of Jason's (Joel's?) query.

😛

______________________________________________________________________

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

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

Jason L. Selburg

dineshrajan SSC Journeyman Points: 77 More actions · Answer 9

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Better late than never. Last I heard, dkrumholtz was growing organic root crops on a plot outside Seattle.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden