CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functionscreate-table
user2692768
user2692768

Reputation: 61

Run PERCENT_RANK() and save result to table

I have a table that looks something like this

instID    xx     yy    zz
   2       2     9     6
   3      11     5     9
   4      13     3     14

I want to run a PERCENT_RANK() query for the fields xx,yy,zz and then save them to a table with the same instID numbers.

Right now I run a Percent_Rank query for each of them, but I can't either figure out how to save data I get from the query, and/or if its possible to do the ranking for each of them separately but in the same query?

Here is the simple code I'm running now

select 
xx,
yy,
zz,
ROUND(
    PERCENT_RANK() OVER (
        ORDER BY xx desc
    ) 

,2) percentile_rank
FROM table;

Upvotes: 1

Views: 421

Answers (1)

GMB
GMB

Reputation: 222582

If I follow you correctly, you can use the select ... into syntax, and percent rank each column:

select xx, yy, zz,
    percent_rank() over(order by xx) xx_pct_rn,
    percent_rank() over(order by yy) yy_pct_rn,
    percent_rank() over(order by zz) zz_pct_rn
into newtable
from mytable

This creates a new table, called newtable, that contains as many rows as the original table, with three additional columns which store the percent rank of the three original columns.

Upvotes: 2

Related Questions