Timothy G. Timothy G. - 2 months ago 17
SQL Question

How to get an incremental "RowId" column in SELECT using ROW_NUMBER()

I've been trying to update a query on the DataExplorer that we use on our Gaming SE Site for keeping track of tags without excerpts to include an incremental row number in the results to make reading the returned values easier. There are a number of questions on here that discuss how to do this, such as this one and this one which appear to have worked for those users, but I can't seem to get it work for my situation.

To be clear, I would like something like this:

RowId | TagName | Count | Easy List Formatting
----------------------------------------------
1 | Tag1 | 6 | 1. [tag:tag1] (6)
2 | Tag2 | 6 | 1. [tag:tag2] (6)
3 | Tag3 | 5 | 1. [tag:tag3] (5)
4 | Tag4 | 5 | 1. [tag:tag4[ (5)


What I've come up with so far is this:

SELECT ROW_NUMBER() OVER(PARTITION BY TagInfo.[Count] ORDER BY TagInfo.TagName ASC) AS RowId, *
FROM
(
SELECT
TagName,
[Count],
concat('1. [tag:',concat(TagName,concat('] (', concat([Count],')')))) AS [Easy List Formatting]
FROM Tags
LEFT JOIN Posts pe on pe.Id = Tags.ExcerptPostId
LEFT JOIN TagSynonyms on SourceTagName = Tags.TagName
WHERE coalesce(len(pe.Body),0) = 0 and ApprovalDate is null
) AS TagInfo
ORDER BY TagInfo.[Count] DESC, TagInfo.TagName


This yields something close to what I want, but not quite. The
RowId
column increments, but once the
Count
column changes, it resets (presumably because of the
PARTITION BY
). But, if I remove the
PARTITION BY
, the
RowId
column becomes what appear to be random numbers.

Is what I want to do achievable given the way the tables are structured? If so, what should the SQL be?

To access the forked query, you can use this link. The original query (before my changes) can be found here if it helps in anyway.

Answer

Removing the PARTITION BY is exactly what is needed. The reason that your numbers look random is that the ORDER BY of the outer query is different from the ORDER BY of your ROW_NUMBER(). All you have to do is make those the same, and the output of the sequence project will have the monotonically increasing value you expect.

Specifically:

SELECT ROW_NUMBER() OVER (ORDER BY TagInfo.[Count] DESC, TagInfo.TagName) AS RowId, * 
FROM
(
   ...
) AS TagInfo
ORDER BY TagInfo.[Count] DESC, TagInfo.TagName

Now you aren't partitioning, and the two ORDER BY clauses match, so you'll get your expected output.

For what it's worth, you technically don't really even care about having an ORDER BY in the ROW_NUMBER(), you just want the same order as the final result set. In that case, you can trick the query engine like so by providing a meaningless ORDER BY clause in the ROW_NUMBER():

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RowId

Boom, done!