Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    2

    Unanswered: update recnos within groups

    i need to update a table with ascending recnos within a group (with sorted names)

    does anyone know how to solve it in ansi sql ?

    id grp name rec
    -- --- ----- ---
    1 a joe (1)
    2 a john (2)
    3 b james (1)
    4 b jill (2)
    5 b jim (3)
    6 c jack (1)
    7 d jesse (1)

    update tab
    set rec = ...

  2. #2
    Join Date
    May 2003
    Posts
    87
    Ansi SQL ? Which database ? I cant recollect any way to do it but if you can tell us the database you are using, may be using the database feature the query might be possible.

  3. #3
    Join Date
    Jun 2003
    Posts
    2
    after some hours of trying i found this sql query with select


    select a.grp, a.name, count(*) "rec"
    from tab a, tab b
    where b.grp = a.grp
    and b.name <= a.name
    group by a.grp, a.name;


    with this result:


    id grp name rec
    -- --- ----- ---
    1 a joe 1
    2 a john 2
    3 b james 1
    4 b jill 2
    5 b jim 3
    6 c jack 1
    7 d jesse 1


    but how does an update work ?


    PS: i'm using sas, a statistical software, in which i can use 'naked' sql

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •