• Replies 4 replies
  • Subscribers 337 subscribers
  • Views 44 views
  • Users 0 members are here
Related

A little help with SQL

Martin Keen (Past Member)
$organization

Okay folks,

I'm sure this can be done - and I could do it if I were cleverer!  I'm looking to number records in a (local/temp) table but that at the change of the constituent number, restart the numbering at 1.  So, it'd be similar to the row_number() function.  A table (or records in a select statement would look like like the following - it's the ability to create the record_no field I'm interested in.

Customer_no

Event

Record_no

Row_no

40661323

ACO 2006 Launch

1

1

40661323

ACO 2007 Launch

2

2

40661323

ACO 2008 Launch

3

3

40661323

ACO 2009 Launch

4

4

50123456

ACO 2005 Launch

1

5

50123456

ACO 2006 Launch

2

6

50123456

ACO 2008 Launch

3

7

 

If anyone knows how to do this and would spread the joy over my way - I'd be very grateful!

Martin

  • $organization

    Hi Martin

    You can do this with the row_number command just need to use it with over and partition by and order by.

    eg. row_number()over(partition by customer_no order by customer_no,event)

    Hope that helps

    Mark

  • $organization in reply to Mark Ridley

    Mark,

    THANK YOU!  Works like a dream.  I knew it had to be possible, if only some kind soul was prepared to share the syntax!

    Martin

  • $organization


    Okay folks,

    I'm sure this can be done - and I could do it if I were cleverer!  I'm looking to number records in a (local/temp) table but that at the change of the constituent number, restart the numbering at 1.  So, it'd be similar to the row_number() function.  A table (or records in a select statement would look like like the following - it's the ability to create the record_no field I'm interested in.

    Customer_no

    Event

    Record_no

    Row_no

    40661323

    ACO 2006 Launch

    1

    1

    40661323

    ACO 2007 Launch

    2

    2

    40661323

    ACO 2008 Launch

    3

    3

    40661323

    ACO 2009 Launch

    4

    4

    50123456

    ACO 2005 Launch

    1

    5

    50123456

    ACO 2006 Launch

    2

    6

    50123456

    ACO 2008 Launch

    3

    7

     

    If anyone knows how to do this and would spread the joy over my way - I'd be very grateful!

    Martin




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!



    Mae’r ohebiaeth hon at ddefnydd y derbynnydd/derbynyddion bwriadedig yn unig. Os nad chi yw’r derbynnydd/derbynyddion bwriadedig, nodwch fod dosbarthu, copïo neu ddefnyddio’r ohebiaeth hon neu’r wybodaeth ynddi mewn unrhyw ffordd wedi ei wahardd yn gyfangwbl a gall fod yn anghyfreithlon. Os ydych wedi derbyn yr ohebiaeth hon trwy gamgymeriad a fyddech cystal â’i ddychwelyd i’r anfonwr. Yn yr achos hwn byddem yn ddiolchgar pe gallech hefyd anfon yr ohebiaeth at administrator@wmc.org.uk ac yna dileu’r e-bost a dinistrio unrhyw gopïau ohono. Cwmni cyfyngedig dan warrant, cofrestrwyd yng Nghymru a Lloegr. Rhif Cwmni 3221924. Rhif Elusen 1060458. Swyddfa gofrestredig: Plas Bute, Bae Caerdydd, Caerdydd CF10 3AL

    This communication is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful.If you have received this communication in error please return it to the sender. In this event would be grateful if you would also copy the communication to administrator@wmc.org.uk then delete the email and destroy any copies of it. A company limited by guarantee, registered in England and Wales. Company number 3221924. Charity number 1060458. Registered office: Bute Place, Cardiff Bay, Cardiff CF10 5AL
  • $organization in reply to Nick Insell
    Nick -
     
    Try using some form of:
     
     

    select customer_no,
    row_number() OVER(PARTITION BY customer_no order by customer_no, row_no) as record_no
    from blah blah blah

    That's not tried and true SQL (obviously) but I've used a similar one with the results you are looking for. If you look up OVER and PARTITION BY in SQL help that should point you in the right direction!

     

    Heather Laidlaw Kraft
    SEATTLE REPERTORY THEATRE

    Please consider the environment before printing this e-mail

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Nick Insell
    Sent: Monday, May 11, 2009 2:42 AM
    To: Heather Laidlaw Kraft
    Subject: RE: [Tessitura Technical Forum] A little help with SQL



    Okay folks,

    I'm sure this can be done - and I could do it if I were cleverer!  I'm looking to number records in a (local/temp) table but that at the change of the constituent number, restart the numbering at 1.  So, it'd be similar to the row_number() function.  A table (or records in a select statement would look like like the following - it's the ability to create the record_no field I'm interested in.

    Customer_no

    Event

    Record_no

    Row_no

    40661323

    ACO 2006 Launch

    1

    1

    40661323

    ACO 2007 Launch

    2

    2

    40661323

    ACO 2008 Launch

    3

    3

    40661323

    ACO 2009 Launch

    4

    4

    50123456

    ACO 2005 Launch

    1

    5

    50123456

    ACO 2006 Launch

    2

    6

    50123456

    ACO 2008 Launch

    3

    7

     

    If anyone knows how to do this and would spread the joy over my way - I'd be very grateful!

    Martin




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!



    Mae’r ohebiaeth hon at ddefnydd y derbynnydd/derbynyddion bwriadedig yn unig. Os nad chi yw’r derbynnydd/derbynyddion bwriadedig, nodwch fod dosbarthu, copïo neu ddefnyddio’r ohebiaeth hon neu’r wybodaeth ynddi mewn unrhyw ffordd wedi ei wahardd yn gyfangwbl a gall fod yn anghyfreithlon. Os ydych wedi derbyn yr ohebiaeth hon trwy gamgymeriad a fyddech cystal â’i ddychwelyd i’r anfonwr. Yn yr achos hwn byddem yn ddiolchgar pe gallech hefyd anfon yr ohebiaeth at administrator@wmc.org.uk ac yna dileu’r e-bost a dinistrio unrhyw gopïau ohono. Cwmni cyfyngedig dan warrant, cofrestrwyd yng Nghymru a Lloegr. Rhif Cwmni 3221924. Rhif Elusen 1060458. Swyddfa gofrestredig: Plas Bute, Bae Caerdydd, Caerdydd CF10 3AL

    This communication is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful.If you have received this communication in error please return it to the sender. In this event would be grateful if you would also copy the communication to administrator@wmc.org.uk then delete the email and destroy any copies of it. A company limited by guarantee, registered in England and Wales. Company number 3221924. Charity number 1060458. Registered office: Bute Place, Cardiff Bay, Cardiff CF10 5AL


    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!