October 3, 2012 at 11:32 am
Hello
I need one help.
I need to display RowNumber based on Inserting record for individual Students
create table #temp1
(
[cStudentId] [char](9) NOT NULL,
[iSchoolCode] [int] NOT NULL,
[dtEnrollmentDate] [datetime] NOT NULL,
[cGradeCode] [char](2) NOT NULL,
[cAbsenceCode] [char](1) NOT NULL
)
Insert into #temp1 values ('003259124',312,'2012-08-30','12','P')
Insert into #temp1 values ('003259124',312,'2012-08-31','12','P')
Insert into #temp1 values ('003259124',312,'2012-09-01','12','A')
Insert into #temp1 values ('003258432',218,'2012-08-30','11','P')
Insert into #temp1 values ('003258432',218,'2012-08-31','11','A')
Insert into #temp1 values ('003258432',218,'2012-09-01','11','P')
Insert into #temp1 values ('003243125',301,'2012-08-30','10','P')
Insert into #temp1 values ('003243125',301,'2012-08-31','09','P')
so from this i got output as below
cStudentIdiSchoolCodedtEnrollmentDatecGradeCodecAbsenceCode
0032591243122012-08-30 00:00:00.00012P
0032591243122012-08-31 00:00:00.00012P
0032591243122012-09-01 00:00:00.00012A
0032584322182012-08-30 00:00:00.00011P
0032584322182012-08-31 00:00:00.00011A
0032584322182012-09-01 00:00:00.00011P
0032431253012012-08-30 00:00:00.00010P
0032431253012012-08-31 00:00:00.00009P
but i need to display like this
cStudentIdiSchoolCodedtEnrollmentDatecGradeCodecAbsenceCodeRawNumber
0032591243122012-08-30 00:00:00.00012P1
0032591243122012-08-31 00:00:00.00012P2
0032591243122012-09-01 00:00:00.00012A3
0032584322182012-08-30 00:00:00.00011P1
0032584322182012-08-31 00:00:00.00011A2
0032584322182012-09-01 00:00:00.00011P3
0032431253012012-08-30 00:00:00.00010P1
0032431253012012-08-31 00:00:00.00009P2
so how i insert RawNumber when i inserting data
Please help me to do this
Thanks
October 3, 2012 at 11:42 am
This should do it.
ROW_NUMBER() over (PARTITION BY cStudentId, Order by tEnrollmentDate)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2012 at 11:47 am
Sean Lange (10/3/2012)
This should do it.
ROW_NUMBER() over (PARTITION BY cStudentId, Order by tEnrollmentDate)
Thanks for Your reply
My question is,
Is it possible in SQL, I increment Row Number while i am inserting data
means something like below,
Insert into #temp1 values ('003243125',301,'2012-08-31','09','P', ROW_NUMBER() OVER(PARTITION BY cStudentId Order by dtEnrollmentDate))
October 3, 2012 at 11:51 am
October 3, 2012 at 11:54 am
surma.sql (10/3/2012)
Sean Lange (10/3/2012)
This should do it.
ROW_NUMBER() over (PARTITION BY cStudentId, Order by tEnrollmentDate)
Thanks for Your reply
My question is,
Is it possible in SQL, I increment Row Number while i am inserting data
means something like below,
Insert into #temp1 values ('003243125',301,'2012-08-31','09','P', ROW_NUMBER() OVER(PARTITION BY cStudentId Order by dtEnrollmentDate))
Not like you are doing. ROW_NUMBER() is a windowed function, meaning it is part of a query, not a list of values. I would recommend you NOT store this information. It is calculated. Just add that to your select query when you get data out of the table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2012 at 11:58 am
select *, ROW_NUMBER() over (PARTITION BY cStudentId Order by dtEnrollmentDate) as RowNum
from #temp1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2012 at 12:08 pm
@SQLFRNDZ (10/3/2012)
Can you tell me where is the difference in your 2 outputs above ?
The difference is an additional column(RowNumber) in second output.
October 3, 2012 at 12:09 pm
Sean Lange (10/3/2012)
select *, ROW_NUMBER() over (PARTITION BY cStudentId Order by dtEnrollmentDate) as RowNum
from #temp1
ok, i got it.
But in my requirement, I insert the output to another table and i need to insert rowNumber based on studentID.
so is that possible?
October 3, 2012 at 12:16 pm
surma.sql (10/3/2012)
Sean Lange (10/3/2012)
select *, ROW_NUMBER() over (PARTITION BY cStudentId Order by dtEnrollmentDate) as RowNum
from #temp1
ok, i got it.
But in my requirement, I insert the output to another table and i need to insert rowNumber based on studentID.
so is that possible?
Did you try it?
Of course it is possible.
Insert SomeOtherTable
select *, ROW_NUMBER() over (PARTITION BY cStudentId Order by dtEnrollmentDate) as RowNum
from #temp1
or
select *, ROW_NUMBER() over (PARTITION BY cStudentId Order by dtEnrollmentDate) as RowNum
into SomeOtherTable
from #temp1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2012 at 1:34 pm
Sean Lange (10/3/2012)
surma.sql (10/3/2012)
Sean Lange (10/3/2012)
select *, ROW_NUMBER() over (PARTITION BY cStudentId Order by dtEnrollmentDate) as RowNum
from #temp1
ok, i got it.
But in my requirement, I insert the output to another table and i need to insert rowNumber based on studentID.
so is that possible?
Did you try it?
Of course it is possible.
Insert SomeOtherTable
select *, ROW_NUMBER() over (PARTITION BY cStudentId Order by dtEnrollmentDate) as RowNum
from #temp1
or
select *, ROW_NUMBER() over (PARTITION BY cStudentId Order by dtEnrollmentDate) as RowNum
into SomeOtherTable
from #temp1
That will work if you don't need the row number to reference back to anything in the first table, and don't mind if the numbers change if you re-run the query after the data has been updated a few times. (It's non-deterministic in that sense.)
If you need the data in the first table, for one of those reasons, then you'll need to generate it when you insert into that table. You do that when you do Insert Select, or when you generate your values for Insert Values. Which are you doing in this case?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply