Do get a column in a specific order

Question

Do get a column in a specific order

ammit.it2006

Ten Centuries

Points: 1280
More actions
December 10, 2011 at 7:30 am

#253278

Recently I have gone through an interview.
There I was asked that I have a table and I have to get another column in the table.
This column values should be like.
1
2
3
4
5
1
2
3
4
5
1
2
3
4
5
...............
He asked me that there is some functionality in SQL server by which we can do that.
I don't think that there is something like that in Sql Server. Can anyone tell me how can we develop such column in a table.
Thanks
Amit

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

look up SQL server ranking functions, specifically ROW_NUMBER() and the OVER(PARTITION BY) predicate; that can do it, as well as

RANK and DENSE RANK;

if it was just an arbitrary # of rows, ie in groups of 5, you'd probably use integer modulus to determine one of the partition by columns based on another row_number(), ie SomeColumn % 5

if you can show us some example data, we can show you some better row_number examples, but any decent example depends on the data it will manipulate, which we really need first.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

The display order of this problem is a bit deceiving. I believe the interviewer was looking for NTILE().

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)