CODEWITHSUNDEEP
sql-servert-sql
Andrew Simpson
Andrew Simpson

Reputation: 7314

using row_number with no unique key

I want to get the record number from my select data.

At present I am aware of something like this:

SELECT max(ROW_NUMBER() OVER(ORDER BY aUniqueField)) FROM aTable

But this assumes that aUniqueField is a unique field and that it is the order I am returning it in. Unfortunately, this is not the case for me. I need a way to 'affix' a row number that is independent of the values of any fields in my select statement.

Is there a way to do this?

These are my 2 tables:

enter image description here

This is my SQL:

SELECT                 
    FeatureName,       
    GF.[Enabled] AS [Enabled],       
    GroupId         
FROM       
    Feature F       
    JOIN GroupFeature GF ON GF.FeatureId = F.FeatureId          
WHERE   
    GroupId = 1 
OR 
    GroupId = 4 
OR  
    GroupId = 2 
Order By 
    Case GroupId  
        When 1 Then 1 
        When 4 Then 2 
        When 2 Then 3

By popular request :) I attach more info. I really should have done this from the beginning.

Data output as it stands:

Feature1, true, 1
Feature2, true, 4
Feature3, true, 4
Feature4, true, 4
Feature5, true, 2

What I would like is:

1,Feature1, true, 1
2,Feature2, true, 4
3,Feature3, true, 4
4,Feature4, true, 4
5,Feature5, true, 2

I have found if I remove the Order By/When clause then using the many suggestions here it works. When I reinstate it I get something like this:

1,Feature1, true, 1
3,Feature2, true, 4
4,Feature3, true, 4
5,Feature4, true, 4
2,Feature5, true, 2

Upvotes: 0

Views: 2760

Answers (4)

paparazzo
paparazzo

Reputation: 45096

Not fully clear to me what you are looking for
Affix a row number that is independent of the values of any fields is odd and you have correct answers that you state are are not working 

DECLARE @map table(mapIn int primary key, mapout int)
insert into @map values (1,1), (4,2), (2,3)
select * from @map
SELECT FeatureName, GF.[Enabled] AS [Enabled], GroupId, 
       ROW_NUMBER() OVER(ORDER BY map.mapout, GF.GroupFeatureID) RowNum         
  FROM Feature F       
  JOIN GroupFeature GF 
    ON GF.FeatureId = F.FeatureId  
  join @map map 
    on map.mapIn = GF.GroupId
 order by map.mapout, GF.GroupFeatureID

Upvotes: 2

Dave.Gugg
Dave.Gugg

Reputation: 6771

You can use a NewID() to get the row number without reordering the data:

SELECT max(ROW_NUMBER() OVER(ORDER BY NewID())) FROM aTable

Upvotes: 0

podiluska
podiluska

Reputation: 51494

You can use 

ROW_NUMBER() over (order by (Select 1))

to get an independent row number

Upvotes: 0

JNK
JNK

Reputation: 65147

The ORDER BY in the windowing function doesn't need to be unique, and if it's not you will still get a unique ROW_NUMBER() for each row:

DECLARE @t TABLE (id int)

INSERT INTO @t
VALUES
    (1)
    ,(1)
    ,(1)
    ,(1)

SELECT ROW_NUMBER() OVER (ORDER BY id), id
FROM @t

Returns:

1   1
2   1
3   1
4   1

Upvotes: 2

Related Questions