Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Selecting a Value of the Order Within a Group Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2008 3:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 21, 2011 7:00 AM
Points: 5, Visits: 8
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.
Post #493309
Posted Wednesday, April 30, 2008 4:22 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:19 PM
Points: 9,294, Visits: 9,491
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."
Post #493325
Posted Wednesday, April 30, 2008 4:36 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
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
Post #493330
Posted Wednesday, April 30, 2008 6:10 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:19 PM
Points: 9,294, Visits: 9,491
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."
Post #493347
Posted Wednesday, April 30, 2008 6:25 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
yeah, I got burned by that a few times before I started checking... LOL :D

______________________________________________________________________

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
Post #493350
Posted Wednesday, April 30, 2008 9:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 21, 2011 7:00 AM
Points: 5, Visits: 8
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?
Post #493393
Posted Wednesday, April 30, 2008 10:44 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 1:19 PM
Points: 9,294, Visits: 9,491
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."
Post #493410
Posted Thursday, May 1, 2008 4:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 21, 2011 7:00 AM
Points: 5, Visits: 8
Thank you all. I love you.
Post #493470
Posted Thursday, May 1, 2008 7:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:01 PM
Points: 2,717, Visits: 3,856
rbarryyoung (4/30/2008)
Just add an "ORDER BY Student, AttendDate" to the end of Jason's (Joel's?) query.


:P


______________________________________________________________________

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
Post #493536
Posted Tuesday, December 7, 2010 4:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 4:16 AM
Points: 21, Visits: 207
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



Post #1031134
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse