MS SQL Server selct with repeating row numbers

I need to do a select on a table where a column is added with
Larry Bristersr. DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:

Please provide a data mockup and expected result set of what you are trying to pull off here.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please review your question. I don't think anybody will understand it besides you.
Dean ChafeeIT/InfoSec ManagerCommented:
Maybe you are looking for the auto increment identity column? Yeah this question needs clarity, but for an example of auto increment of ID:

CREATE TABLE MyTable (id_num int IDENTITY(1,1), column1 varchar(10));

But... your repeating sequence cant be done with ID.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Larry Bristersr. DeveloperAuthor Commented:
Hey guys,
 Sorry for a badly worded question

I actually worked it out myself

It is this section (Not showing actual numbers)
CASE (ABS(Checksum(NewID()) % 3) + 1 )
            WHEN 1 THEN 'ABC'
            WHEN 2 THEN 'DEF'
            ELSE 'HIJ' END HijackedPhoneNUmber

SELECT i.IndividualID ,
		i.FirstName ,
		i.LastName ,
		i.ExternalID ,
		CAST(t.TourDate AS DATE) TourDate,
		[dbo].[udfStandard12](LEFT(t.Wave,2) + ',' + RIGHT(t.Wave,2)) AS Wave,
		CASE (ABS(Checksum(NewID()) % 3) + 1 )
		ELSE 'HIJ' END HijackedPhoneNUmber
FROM   Individuals i WITH ( NOLOCK )
		JOIN IndividualOrigins o WITH ( NOLOCK ) ON i.IndividualID = o.IndividualID
		JOIN dbo.ewSalesSitesToIndividuals s WITH ( NOLOCK ) ON i.IndividualID = s.IndividualID
		JOIN dbo.IndividualPhoneNumbers p WITH ( NOLOCK ) ON i.IndividualID = p.IndividualID
											AND p.PhoneNumberTypeID = 3
		JOIN everyware_t_tour t WITH ( NOLOCK ) ON i.ExternalID = t.ProspectID
WHERE  YEAR(t.TourDate) = 2018
		AND dbo.IsIntegerOnly(i.ExternalID) = 1
		AND LEN(i.ExternalID) < 9
		AND i.Active = 1
		AND i.Dupe = 0
		AND o.OriginID IN ( 143455, 143459 )
		AND CAST(TourDate AS DATE) = '2018-02-16'
		AND t.TourStatus = 'Booked';

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry Bristersr. DeveloperAuthor Commented:
The purpose for this was to be able to add a switch that I can test an SMS send against a bank of our own numbers

Pretty embarrassing when a test SMS goes out to live people.

(Don;t ask how I know that  )

Ares KurkluSoftware EngineerCommented:
This expert suggested creating a Gigs project.
If i get your question right and if you want to add an additional column to your return query to order/number some things you can either use Rank() or Row_Number() to do that MS SQL, in the below example we had a client table

select ClientId,
RANK () OVER (ORDER BY ClientId) as 'theRank',
 from entity_client
order by clientid

ordered by clientId would give
ClientName  ClientId  RowNumber
bbbbb         3              1
ccccc           4              2
aaaaa          6              3
zzzzz           7                   4

if you order by client name and use row number
ClientName  ClientId  RowNumber
aaaaa            6           1
bbbbb            3           2
ccccc            4           3
zzzzz            7           4

the row_number() can go more complex with adding partition and you can get repeating sequence depending on what you are after.
Larry Bristersr. DeveloperAuthor Commented:
My solution I placed above returns me a "workable" solution
Ares KurkluSoftware EngineerCommented:
That's ok I submitted before I saw the posts I was just guessing.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.