Quick Search:
 
 Oracle PL/SQL: ROW_NUMBER Jump to:  
Category: >> Oracle PL/SQL >> ROW_NUMBER  

<< lastnext >>

Snippet Name: ROW_NUMBER

Description: Assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order by clause, beginning with 1.

Also see:
» RANK
» REGR_SLOPE
» VARIANCE
» VAR_SAMP
» VAR_POP
» SUM
» STDDEV_SAMP
» STDDEV_POP
» STDDEV
» REGR_SYY
» REGR_SXY
» REGR_SXX
» REGR_R2
» REGR_INTERCEPT
» REGR_COUNT
» REGR_AVGY
» REGR_AVGX
» Number Functions: RATIO_TO_REPORT
» Number Functions: RANK
» PERCENTILE_DISC
» PERCENTILE_CONT
» PERCENT_RANK
» OVER PARTITION BY
» NTILE
» MIN
» MAX
» LEAD
» LAST_VALUE
» LAST
» LAG

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009

ROW_NUMBER(<value>) OVER (<partition_clause> ORDER BY <order_by_clause>)
 
CREATE TABLE test (
id      NUMBER(1),
degrees NUMBER(3));
 
INSERT INTO test VALUES (0,235);
INSERT INTO test VALUES (0,276);
INSERT INTO test VALUES (1,211);
INSERT INTO test VALUES (1,250);
INSERT INTO test VALUES (1,255);
INSERT INTO test VALUES (2,55);
INSERT INTO test VALUES (2,277);
INSERT INTO test VALUES (2,69);
INSERT INTO test VALUES (3,25);
INSERT INTO test VALUES (3,166);
INSERT INTO test VALUES (3,262);
INSERT INTO test VALUES (4,47);
INSERT INTO test VALUES (4,238);
INSERT INTO test VALUES (4,40);
COMMIT;
 
SELECT * FROM test;
 
-- choose the starting cell
SELECT id, degrees s
FROM (
  SELECT id, degrees, (360 - degrees) d360,
  ROW_NUMBER() OVER(PARTITION BY id
  ORDER BY CASE
    WHEN (degrees < 360 - degrees) THEN degrees
    ELSE 360 - degrees
    END) rn
  FROM test) t
WHERE rn = 1;
 
-- order the rest clockwise
SELECT *
FROM (
  SELECT t.id, t.degrees,
  ROW_NUMBER() OVER(PARTITION BY t.id
  ORDER BY CASE
    WHEN (t.degrees < starting_cell.degrees) THEN t.degrees + 360
    ELSE t.degrees
    END) rn
  FROM test t
JOIN (
  SELECT id, degrees, (360 - degrees) d360,
  ROW_NUMBER() OVER(PARTITION BY id
  ORDER BY CASE
    WHEN (degrees < 360 - degrees) THEN degrees
    ELSE 360 - degrees
    END) rn
  FROM test) starting_cell
  ON t.id = starting_cell.id
  WHERE starting_cell.rn=1)t
ORDER BY id, rn;
 


 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org