Sql-server – How to get a row_number to have the behavior of dense_rank

sql serverwindow functions

I have a stored procedure will be used to page through data. One of the requirements of the procedure is to have a parameter that will be used to sort two columns of data, the sorting should be used when applying the row_number().

Some sample data:

CREATE TABLE grp 
(
  [grp_id] uniqueidentifier primary key, 
  [grp_nm] varchar(5) not null, 
  [grp_owner] varchar(200) not null
);

INSERT INTO grp ([grp_id], [grp_nm], [grp_owner])
VALUES
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 'test1', 'me'),
    ('1F52A713-EFAC-E211-9C26-78E7D18E1E84', 'test2', 'me'),
    ('D123B48A-63AB-E211-9C26-78E7D18E1E84', 'test3', 'me'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 'test4', 'me'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 'test5', 'me'),
    ('D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84', 'test6', 'me'),
    ('9A07EA21-1AAD-E211-9C26-78E7D18E1E84', 'test7', 'me');


CREATE TABLE mbr
(
  [grp_id] uniqueidentifier, 
  [mbr_id] int not null primary key, 
  [acct_id] varchar(7) not null, 
  [cst] varchar(4) null
);

INSERT INTO mbr ([grp_id], [mbr_id], [acct_id], [cst])
VALUES
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 10, '1', 'AA'),
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 11, '2', 'BB'),
    ('1F52A713-EFAC-E211-9C26-78E7D18E1E84', 12, '1234578', 'blah'),
    ('D123B48A-63AB-E211-9C26-78E7D18E1E84', 13, '78', 'test'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 14, 'x', 'mbr1'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 15, 'a', 'mbr2'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 16, 'b', 'mbr1'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 17, 'c', 'mbr2'),
    ('D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84', 18, 'a', 'mbr1'),
    ('9A07EA21-1AAD-E211-9C26-78E7D18E1E84', 19, 'a', 'mbr1');

The procedure and the sorting is currently working as written.

But an issue has cropped up with the way that we are applying the row_number(). The row_number() should almost simulate the ranking that occurs with dense_rank but it does not work as expected due to the sorting with the parameter.

For example, if I run the following query:

declare @sort_desc bit = 0

select g.grp_id, g.grp_nm,
    m.mbr_id, m.acct_id, m.cst,
    row_number() over(order by case when @sort_desc = 0 then g.grp_nm end
                              , case when @sort_desc = 0 then m.acct_id end
                              , case when @sort_desc = 1 then g.grp_nm end desc
                              , case when @sort_desc = 1 then m.acct_id end desc) rn,
    dense_rank()  over(order by case when @sort_desc = 0 then g.grp_nm end
                              , case when @sort_desc = 0 then m.acct_id end
                              , case when @sort_desc = 1 then g.grp_nm end desc
                              , case when @sort_desc = 1 then m.acct_id end desc) dr
from grp g
inner join mbr m
  on g.grp_id = m.grp_id;

See SQL Fiddle with Demo (a trimmed down stored proc is also present)

I get a result of:

|                               GRP_ID | GRP_NM | MBR_ID | ACCT_ID |  CST | RN | DR |
-------------------------------------------------------------------------------------
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     10 |       1 |   AA |  1 |  1 |
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     11 |       2 |   BB |  2 |  2 |
| 1F52A713-EFAC-E211-9C26-78E7D18E1E84 |  test2 |     12 | 1234578 | blah |  3 |  3 |
| D123B48A-63AB-E211-9C26-78E7D18E1E84 |  test3 |     13 |      78 | test |  4 |  4 |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     15 |       a | mbr2 |  5 |  5 |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     14 |       x | mbr1 |  6 |  6 |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     16 |       b | mbr1 |  7 |  7 |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     17 |       c | mbr2 |  8 |  8 |
| D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84 |  test6 |     18 |       a | mbr1 |  9 |  9 |
| 9A07EA21-1AAD-E211-9C26-78E7D18E1E84 |  test7 |     19 |       a | mbr1 | 10 | 10 |

But the desired result is:

|                               GRP_ID | GRP_NM | MBR_ID | ACCT_ID |  CST | RN | expR|
-------------------------------------------------------------------------------------
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     10 |       1 |   AA |  1 |  1  |
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     11 |       2 |   BB |  2 |  1  |
| 1F52A713-EFAC-E211-9C26-78E7D18E1E84 |  test2 |     12 | 1234578 | blah |  3 |  2  |
| D123B48A-63AB-E211-9C26-78E7D18E1E84 |  test3 |     13 |      78 | test |  4 |  3  |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     15 |       a | mbr2 |  5 |  4  |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     14 |       x | mbr1 |  6 |  4  |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     16 |       b | mbr1 |  7 |  5  |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     17 |       c | mbr2 |  8 |  5  |
| D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84 |  test6 |     18 |       a | mbr1 |  9 |  6  |
| 9A07EA21-1AAD-E211-9C26-78E7D18E1E84 |  test7 |     19 |       a | mbr1 | 10 |  7  |

As you can see the expR column values are incrementing based on the grp_id but the rows are still in the correct sort order. I am at a loss of how to get this result and any suggestions would be great.

Best Answer

This is about as concise as I could get it without squeezing my brain to disastrous effects. SQLFiddle.

DECLARE @sort_desc BIT = 0;

;WITH x AS
(
  SELECT g.grp_id, g.grp_nm, m.mbr_id, m.acct_id, m.cst,
    o1 = ROW_NUMBER() OVER (ORDER BY 
         CASE @sort_desc WHEN 0 THEN g.grp_nm END,
         CASE @sort_desc WHEN 0 THEN m.acct_id END,
         g.grp_nm DESC, m.acct_id DESC)
  FROM dbo.grp AS g
  INNER JOIN dbo.mbr AS m
  ON g.grp_id = m.grp_id
), 
y AS
(
  SELECT *, o2 = ROW_NUMBER() OVER (PARTITION BY grp_id ORDER BY o1)
  FROM x
)
SELECT *, expected = DENSE_RANK() OVER (ORDER BY o1-o2)
FROM y
ORDER BY o1;

You'll of course want to extrapolate out all the * references to the actual columns you need to carry forward and ultimately output. I'm lazy but do as I say, not as I do. :-)

Better performance with altered table layout

Arrays have an overhead of 24 bytes (slight variations depending on type). Also, you seem to have quite a few items per array and many repetitions. For a huge table like yours it would pay to normalize the schema. Example layout:

CREATE TABLE combo ( 
  combo_id serial PRIMARY KEY
 ,combo    int[] NOT NULL
);

CREATE TABLE perm ( 
  perm_id  serial PRIMARY KEY
 ,perm     int[] NOT NULL
);

CREATE TABLE value (
  perm_id  int REFERENCES perm(perm_id)
 ,combo_id int REFERENCES combo(combo_id)
 ,value numeric NOT NULL DEFAULT 0
);

If you don't need referential integrity you can omit the foreign key constraints.

The connection to combo_id could also be placed in the table perm, but in this scenario I would store it (slightly de-normalized) in value for better performance.

This would result in a row size of 32 bytes (tuple header + padding: 24 bytes, 2 x int (8 byte), no padding), plus the unknown size of your numeric column. (If you don't need extreme precision, a double precision or even a real column might do, too.)

More on physical storage in this related answer on SO or here:
Configuring PostgreSQL for read performance

Anyway, that's only a fraction of what you have now and would make your query a lot faster by size alone. Grouping and sorting on simple integers is also a lot faster.

You would first aggregate in a subquery and then join to perm and combo for best performance.

How to Get Latest Five Scores Per User Per Subject in PostgreSQL

Do you want the ones with less than 5 scores in mix. If you want only the 5 scores one, you can add a having to your query:

    SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
    FROM  (
       SELECT *
             ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
      FROM mg.scores
      WHERE score IS NOT NULL) as score
       ) AS x
    WHERE  x.rn <= 5
    GROUP  BY user_id,dept
    HAVING count(*) > 4;

Best Answer

Related Solutions

How to Get Aggregate of a Window Function in PostgreSQL

Better performance with altered table layout

How to Get Latest Five Scores Per User Per Subject in PostgreSQL

Related Question