Add number to New Column if the row starts at AAA with 1 in the Same Filename

Queennie L
Queennie L used Ask the Experts™
on
Hello Experts,

I have SQL table columns:
UniqueID
Filename
DataName

NewColumn

If the Filename starts with AAA in row then 1, if there are more than AAA in rows with the same filename then starts with 1 then 2 then 3 in NewColumn.

Please see attach file.

Thank you for all your help.
Add-number-to-New-Column-if-the-row.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Team Lead
Commented:
try like:

;with cte as
(
	select *, ROW_NUMBER() over (partition by FileName, DataName order by DataName, UniqueID) idx from yourTable
)
Select UniqueID, FileName, DataName,
case when DataName = 'AAA' then idx else null end NewColumn
from cte
ORder By UniqueID

Open in new window

Author

Commented:
@Ryan Chong,

I will test your code. I will let you know.

Thank you again for your help.

Author

Commented:
@RyanChong:

OMG! It is working.

Thank you and Thank you again and again for your help.

I really appreciate it for you intelligence and expertise.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial