How to get a row_number to have the behavior of dense_rank

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 t

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. :-)

StackExchange Database Administrators Q#42841, answer score: 7