SQL Server : generate a sequential number based on each condition

Question

I have the following SQL and trying to generate a number next to each condition it meets for each record it produces.

SELECT TOP 10 
    [Account],
    [Account Name],
    generate #
FROM
    Supplier
WHERE
    Account Name IN ('JP', 'TC', 'KA')

So it would be something like this,

Account    Account Name      Generate #
---------------------------------------
T1000          JP                 1
T1001          TC                 2
T1033          KA                 3
T1039          KA                 4

So Generate # is what I'm trying to do in SQL Server.

Thanks for your time.

you've got a top 10 without an order by. This will result in a random top 10 — KeithL
– KeithL, Commented Feb 20, 2019 at 17:22

Gordon Linoff · Accepted Answer · 2019-02-20 16:55:16Z

2

Use ROW_NUMBER():

select top 10 [Account], [Account Name],
       row_number() over (order by Account) as seqnum
from Supplier
where [Account Name] in ('JP','TC','KA')

answered Feb 20, 2019 at 16:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

SQL Server : generate a sequential number based on each condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related