March 23, 2017 at 11:22 am
I'm having a little trouble trying to a simple thing..
We have a surrogate key column that I don't want to show to users, instead, I want to simplify the keys returned by the query in just 1,2,3.. format.
For instance, I have a query that returns theses values:
ID
9990
9990
9995
9995
9999
I want the query to return the following:
ROW ID
1 9990
1 9990
2 9995
2 9995
3 9999
The queries I've tried, using ROW_NUMBER don't seem to want to partition/number these correctly.. Any ideas?
This query does NOT give me the results I'm hoping for..
WITH [a] AS (
SELECT 9990 [col1] UNION ALL SELECT 9990 UNION ALL SELECT 9995 UNION ALL SELECT 9995 UNION ALL SELECT 9999
)
SELECT
ROW_NUMBER() OVER (PARTITION BY [col1] ORDER BY [col1]) [row]
, [col1]
FROM [a]
March 23, 2017 at 12:11 pm
Use DENSE_RANK instead of ROW_NUMBER. From the data given, you do not want a partition.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2017 at 12:19 pm
I would expect Rank or Dense_Rank to work, but they don't :unsure:. This is not very ellegant, but does the work:
; WITH [a] AS
(
SELECT 9990 [col1] UNION ALL
SELECT 9990 UNION ALL
SELECT 9995 UNION ALL
SELECT 9995 UNION ALL
SELECT 9999
)
, RowAssigned AS
(
SELECT [col1],
row = row_number() OVER (ORDER BY [col1])
FROM [a]
GROUP BY [col1]
)
SELECT a.[Col1],RowAssigned.[row]
FROM [a]
JOIN RowAssigned ON RowAssigned.[col1] = a.[Col1]
ORDER BY [row]
;
I hope that this can be solved without GROUP BY in the middle, maybe someone else knows?
March 23, 2017 at 12:24 pm
This gives the exact same results. Why doesn't DENSE_RANK work?
; WITH [a] AS
(
SELECT 9990 [col1] UNION ALL
SELECT 9990 UNION ALL
SELECT 9995 UNION ALL
SELECT 9995 UNION ALL
SELECT 9999
)
SELECT [col1],
row = DENSE_RANK() OVER (ORDER BY [col1])
FROM [a]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 23, 2017 at 12:29 pm
Zidar - Thursday, March 23, 2017 12:19 PMI would expect Rank or Dense_Rank to work, but they don't :unsure:. This is not very ellegant, but does the work:
; WITH [a] AS
(
SELECT 9990 [col1] UNION ALL
SELECT 9990 UNION ALL
SELECT 9995 UNION ALL
SELECT 9995 UNION ALL
SELECT 9999
)
, RowAssigned AS
(
SELECT [col1],
row = row_number() OVER (ORDER BY [col1])
FROM [a]
GROUP BY [col1]
)
SELECT a.[Col1],RowAssigned.[row]
FROM [a]
JOIN RowAssigned ON RowAssigned.[col1] = a.[Col1]
ORDER BY [row]
;I hope that this can be solved without GROUP BY in the middle, maybe someone else knows?
DENSE_RANK() seems to work perfectly
WITH [a] AS (
SELECT 9990 [col1] UNION ALL
SELECT 9990 UNION ALL
SELECT 9995 UNION ALL
SELECT 9995 UNION ALL
SELECT 9999
)
SELECT DR = DENSE_RANK() OVER (ORDER BY col1)
, col1
FROM [a]
March 23, 2017 at 12:31 pm
This gives the exact same results. Why doesn't DENSE_RANK work?
because of my stupidity. I had PARTITION BY, when there was no need for one. This returms 1 for all rows:
; WITH [a] AS
(
SELECT 9990 [col1] UNION ALL
SELECT 9990 UNION ALL
SELECT 9995 UNION ALL
SELECT 9995 UNION ALL
SELECT 9999
)
SELECT [col1],
row = DENSE_RANK() OVER (PARTITION BY [col1] ORDER BY [col1])
FROM [a]
Thank you Drew for helping.🙂
March 23, 2017 at 12:38 pm
Thanks so much for the quick response! It's a difficult thing to Google, and I only use the ranking functions occasionally.
This returned exactly what I wanted:
WITH [a] AS (
SELECT 9990 [col1] UNION ALL SELECT 9990 UNION ALL SELECT 9995 UNION ALL SELECT 9995 UNION ALL SELECT 9999
)
SELECT
DENSE_RANK() OVER (ORDER BY [col1]) [row]
, [col1]
FROM [a]
row col1
1 9990
1 9990
2 9995
2 9995
3 9999
March 23, 2017 at 12:41 pm
Zidar - Thursday, March 23, 2017 12:31 PMThis gives the exact same results. Why doesn't DENSE_RANK work?
because of my stupidity. I had PARTITION BY, when there was no need for one. This returms 1 for all rows:
; WITH [a] AS
(
SELECT 9990 [col1] UNION ALL
SELECT 9990 UNION ALL
SELECT 9995 UNION ALL
SELECT 9995 UNION ALL
SELECT 9999
)
SELECT [col1],
row = DENSE_RANK() OVER (PARTITION BY [col1] ORDER BY [col1])
FROM [a]Thank you Drew for helping.🙂
I did mention that you didn't need the partition in my original response.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply