generate sequential number using SQL

Question

I have a table that has 200 records. The columns in the table are

Client_id, Name, age, dob

sample data:

Now I have a select statement that needs to generate sequential number but not from the table.

So my select statement is:

select row_number, client_id, name from #temp1....

I don't want to get the number from the #temp1 table rather I want to generate dynamically on the fly.

any help?!

Are you using MySQL or SQL Server? (Or something completely different?) You tagged both, maybe by accident? — sticky bit
– sticky bit, Commented Oct 8, 2018 at 22:07

sticky bit · Accepted Answer · 2018-10-08 22:12:26Z

2

Try using row_number(). For example:

SELECT row_number() OVER (ORDER BY client_id) row_number,
       client_id,
       name
       FROM #temp1;

Possibly change the ORDER BY clause, if you don't want the numbers to be ordered by the client ID.

answered Oct 8, 2018 at 22:12

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

1 Comment

Thanks. This is what I needed.