Results 1 to 2 of 2
  1. #1
    Join Date
    May 2016

    Unanswered: Creating a sort_order field with group by

    Hi All

    SET @x = 0;
    UPDATE table SET sort_order = (@x:=@x+1) ORDER BY f1, f2;

    This sets the sort_order field on record 1 to1, 2 to 2 and so on...

    What I need is to reset @x when a new group begins, so GROUP BY field1, ORDER BY field2

    What I want is

    f1 f2 sort_order
    1 3 1
    1 4 2
    1 7 3
    2 1 1
    2 5 2

    I just can't think of a way to do this with SQL!

    Thanks for any ideas

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    If your Oracle version supports the OVER() function you could use:
    SELECT f1, f2
    ,  Row_Number() OVER (PARTITION BY f1 ORDER BY f2) AS sort_order
       FROM table;
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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