MS SQL Server selct with repeating row numbers

I need to do a select on a table where a column is added with
1
2
3
1
2
3
etc...
Larry Bristersr. DeveloperAsked:
Who is Participating?
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 Developer, Architect, and AuthorCommented:
Huh?

Please provide a data mockup and expected result set of what you are trying to pull off here.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please review your question. I don't think anybody will understand it besides you.
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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 ,
		t.TourID,
		CAST(t.TourDate AS DATE) TourDate,
		[dbo].[udfStandard12](LEFT(t.Wave,2) + ',' + RIGHT(t.Wave,2)) AS Wave,
		p.PhoneNumber,
		CASE (ABS(Checksum(NewID()) % 3) + 1 )
		WHEN 1 THEN 'ABC'
		WHEN 2 THEN 'DEF'
		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

1

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  )

:-)
0
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,
ClientName,
ClientDirectory,
RANK () OVER (ORDER BY ClientId) as 'theRank',
ROW_NUMBER() OVER (ORDER BY  ClientId ASC) AS 'RowNumber'
 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.
1
Larry Bristersr. DeveloperAuthor Commented:
Ares
My solution I placed above returns me a "workable" solution
0
Ares KurkluSoftware EngineerCommented:
That's ok I submitted before I saw the posts I was just guessing.
0
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
RDS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.