SQL Server: Enumerating table query groups created with ROW_NUMBER() OVER (PARTITION

Question

I have a query like the next:

DECLARE @RETURN_TABLE TABLE(
        MARKET varchar(200),
        NAMES varchar(200),
        PK_IDS VARCHAR (25),
        PFK_MARKET INT,
        IS_ADHOC BIT,
        FK_APPLICATION INT)

...

SELECT *
FROM (
    SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY PK_IDS, MARKET ORDER BY is_adhoc desc) seqnum
    FROM @RETURN_TABLE 
) t
WHERE seqnum = 1 AND MARKET LIKE '%' + @MARKET + '%' 
ORDER BY MARKET

Which -as an example- returns something like this (with many more rows):

MARKET                      NAMES                       PK_IDS      PFK_MARKET  IS_ADHOC    FK_APP  seqnum
AB/West Region              AB - Value Add ...          1/2/39      226         1           0       1
AB/West Region              AB - Other Retail ...       1/3/39      226         1           0       1
AB/West Region - Breakthru  Browns Social ...           1/122/39    281         0           0       1
AB/West Region - Breakthru  Local STREET ...            1/123/39    281         0           0       1
AB/West Region - Breakthru  Moxie's STREET ...          1/124/39    281         0           0       1
adm Promotions              Branding Your POS ...       107/107/39  231         1           NULL    1
adm Promotions              Imagen Publicitaria ...     111/111/39  231         1           NULL    1
adm Promotions              ADM Promotions UK Limited   75/75/39    231         1           NULL    1

And I need to enumerate partitioned records (MARKET groups) so my result will look like the next:

MARKET                      NAMES                       PK_IDS      PFK_MARKET  IS_ADHOC    FK_APP  seqnum  seqnum1
AB/West Region              AB - Value Add ...          1/2/39      226         1           0       1       1
AB/West Region              AB - Other Retail ...       1/3/39      226         1           0       1       1
AB/West Region - Breakthru  Browns Social ...           1/122/39    281         0           0       1       2
AB/West Region - Breakthru  Local STREET ...            1/123/39    281         0           0       1       2
AB/West Region - Breakthru  Moxie's STREET ...          1/124/39    281         0           0       1       2
adm Promotions              Branding Your POS ...       107/107/39  231         1           NULL    1       3
adm Promotions              Imagen Publicitaria ...     111/111/39  231         1           NULL    1       3
adm Promotions              ADM Promotions UK Limited   75/75/39    231         1           NULL    1       3

As you see, I'm grouping by MARKET and need to enumerate groups (I will use then this enumeration to paginate and show N number of groups between a start page and delimiter).

I achieved something that is working, but I'm sure it can be done in a better way:

DECLARE @lastindex int

SET @lastindex = (SELECT TOP 1 seqnum1 
        FROM (
            SELECT *, 
            ROW_NUMBER() OVER (PARTITION BY PK_IDS, MARKET ORDER BY is_adhoc desc) seqnum,
            DENSE_RANK() OVER (ORDER BY MARKET desc) seqnum1 
            FROM @RETURN_TABLE 
        ) t
        WHERE seqnum = 1 AND MARKET LIKE '%' + @MARKET + '%' ORDER BY seqnum1 desc)

--

SELECT *
    FROM (
        SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY PK_IDS, MARKET ORDER BY is_adhoc desc) seqnum,
        DENSE_RANK() OVER (ORDER BY MARKET desc) seqnum1 
        FROM @RETURN_TABLE 
    ) t
    WHERE seqnum = 1 AND MARKET LIKE '%' + @MARKET + '%'
    AND @lastindex BETWEEN @lastindex-((@LIMIT*@PAGE)-1) AND (@lastindex-((@LIMIT*@PAGE)-1))+(@LIMIT-1)
    ORDER BY seqnum1 desc

I'm selecting last index (first select with TOP 1) because DENSE_RANK() is returning the groups counter in reverse order.

I'm sure this can be done with only one query, but not sure how.

Any help?

Answer 1

Ok, for the moment I'll answer my own question in case it helps anyone else.

My final query, where the MARKET groups are enumerated is the next:

SELECT *
    FROM (
        SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY PK_IDS, MARKET ORDER BY is_adhoc desc) seqnum,
        DENSE_RANK() OVER (ORDER BY MARKET) seqnum1 
        FROM @RETURN_TABLE 
    ) t
    WHERE seqnum = 1 AND MARKET LIKE '%' + @MARKET + '%'
    AND seqnum1 BETWEEN (@LIMIT*(@PAGE-1))+1 AND (@LIMIT*(@PAGE-1)) + @LIMIT
    ORDER BY seqnum1

In my post, I didn't realize rows where showing in reverse order because of the "desc" in the INNER "ORDER BY MARKET DESC", removing that desc, the DENSE_RANK() shows seqnum1 (the name of my groups counter column) in the correct order and then I can avoid the first select to get the last index, and so I finally get one select with the MARKET groups enumerated and so, I'll be able to paginate by groups of MARKETS.

I cannot say it's the best way to do it, but it's working.