row number without using the ROW_NUMBER window function

Question

Suppose you have a table with non-unique values such as this:

CREATE TABLE accounts ( fname VARCHAR(20), lname VARCHAR(20)) 
GO 

INSERT accounts VALUES ('Fred', 'Flintstone') 
INSERT accounts VALUES ('Fred', 'Flintstone') 
INSERT accounts VALUES ('Fred', 'Flintstone') 

SELECT * FROM accounts 
GO

Now using a ROW_NUMBER function, you can get a unique incrementing row number.

select *, ROW_NUMBER() over(order by (select null)) as rn
from accounts

But how do we this without using a ROW_NUMBER function. I tried giving each row a unique ID using NEWID() and then counting the rows as given below but it did not work as it gives me a non-unique number which does not start with 1. Note that I do not want to alter the table to add a new column.

;with cte as
(select *
from accounts as e
cross apply (select newid()) as a(id)
)
select *, (select count(*)+1 from cte as c1 where c.id > c1.id) as rn
from cte as c
order by rn

SQL Fiddle for toying around is http://sqlfiddle.com/#!18/c270f/3/0

yeah. What's wrong with row_number()? It's the best thing invented for this thing. — Alex
– Alex, Commented Feb 16, 2018 at 20:31
What is wrong with row_number()? It has been around since SQL 2005. You want to use a cursor? — paparazzo
– paparazzo, Commented Feb 16, 2018 at 21:24
Nothing wrong. But trying to understand set based solution/pattern that will get me the answer without using Window function, cursors, etc. Kind of what you would do before window function were introduced. Anyway, I still don't understand why using NEWID() did not work. — umbersar
– umbersar, Commented Feb 16, 2018 at 22:01
@Paparazzi: window functions have been defined in SQL:2003, there never was a "SQL 2005" standard. — user330315
– user330315, Commented Feb 17, 2018 at 7:12

HABO · Accepted Answer · 2018-02-17 06:25:56Z

2

The following demonstrates why your code fails, but does not provide an alternative to Row_Number().

A column, TopId, is added to the final select that should get the minimum value generated by NewId() and report it in every row. Instead, a new value is generated for each row.

-- Sample data.
declare @Samples as Table ( FName VarChar(20), LName VarChar(20) );
insert into @Samples ( FName, LName ) values
  ( 'Fred', 'Flintstone' ), ( 'Fred', 'Flintstone' ), ( 'Fred', 'Flintstone' );
select * from @Samples;

-- Cross apply   NewId()   in a CTE.
;with cte as
  ( select *
      from @Samples as S
    cross apply ( select NewId() ) as Ph( Id ) )
  select *, ( select count(*) from cte as c1 where c1.Id >= c.Id ) as RN,
    -- The following column should output the minimum   Id   value from the table for every row.
    --   Instead, it generates a new unique identifier for each row.
    ( select top 1 id from cte order by id ) as TopId
    from cte as c
    order by RN;

The execution plan shows that the CTE is treated as a view that is being evaluated repeatedly, thus generating conflicting Id values.

answered Feb 17, 2018 at 6:25

HABO

15.9k5 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

umbersar Over a year ago

That explains it!

ravioli · Accepted Answer · 2018-02-16 20:25:27Z

1

How about this:

SELECT 
  src.*, 
  SUM(DummyVal) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RowId
FROM (
  SELECT a.*, 1 AS DummyVal
  FROM MyTable a
) src

It's still a window function, though, not sure if that matters.

Fiddle me this

answered Feb 16, 2018 at 20:25

ravioli

3,8213 gold badges17 silver badges35 bronze badges

1 Comment

gokaysatir Over a year ago

This approach is useful when you want to number rows according to the order by clause of the query. Otherwise you have to write the same order by clause inside "row_number over(..)".

Pessi S. · Accepted Answer · 2022-01-05 13:00:36Z

You can create an function yourself to compute the row_number,

In this example, I had to calculate an index for a lesson within a course.

Window Function version:

 SELECT *,  ROW_NUMBER() OVER(PARTITION BY courseId) AS row_num FROM lessons;

I created helper-function, to compute the row_number without window function:

DELIMITER $$
CREATE FUNCTION getRowNumber (lessonId int, courseId int) 
RETURNS int
DETERMINISTIC
BEGIN
  DECLARE count int;
select count(l2.id) into count  from lessons l2 where l2.courseId=courseId
and l2.id<=lessonId;
RETURN count;
END$$
DELIMITER ;

so, the final query is:

 SELECT l.*,  getRowNumber(l.id,l.courseId) as row_num  FROM lessons l;

got the same result as the first query!

Volokh · Accepted Answer · 2018-02-16 20:29:19Z

-2

MySQL:

SELECT @rownum := @rownum + 1 AS rank, a.* 
FROM accounts a,(SELECT @rownum := 0) r;

In ORACLE it would simply be

SELECT ROWNUM, a.*
FROM accounts a;

Both without window

answered Feb 16, 2018 at 20:29

Volokh

3904 silver badges18 bronze badges

3 Comments

Eric Over a year ago

OP specifically put sql-server tag on the question. What's the purpose of giving answer on other dbms???

Volokh Over a year ago

it was not there in the beginning as far as I recall

umbersar Over a year ago

My initial tags were 'T-SQL' and 'sql' as I was looking for either a standard sql set based answer or specific to sql server. I changed them to 'T-SQL' and 'sql server' to avoid confusion.

Collectives™ on Stack Overflow

row number without using the ROW_NUMBER window function

4 Answers 4

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related