Query Help

  • 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

  • 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/

  • 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))

  • Can you tell me where is the difference in your 2 outputs above ?

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • 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/

  • 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/

  • @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.

  • 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?

  • 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/

  • 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