Display cities in specified order

Question

Display cities in specified order

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Bhavesh_Patel (12/16/2009)
But in order to insert the new city at SortOrder 4, the subsequent SortOrder also needs to be changed. This is the problem.
Is there a simple way, which will allow us to make changes in SortedCities table?

Uh-huh... and what kind of problem would that pose in a CASE statement in the ORDER BY?

Start thinking... if you want to "insert" a new city in the middle of all the other cities, how should it be done as a human would do it? The answer is, add "1" to all of the numbers >= to the number you want to insert and then insert the new row. For practice, you get to write the code to do that. 😀 It should be general purpose where the only thing you pass to the script or proc is the name of the new city and the sort order number you want it to have.

Think, man, think.... you're going on an interview. They're going to ask simple but thoughtful questions like this. If you can't think in such a manner, the interview will not go well because they need someone who can think of such simple things as this. 😉

--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)

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

Once you get done with the code for a new city, write some code to change the sort order of a city. Think and keep it simple... no need for complexity here.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Jeff Moden (12/16/2009)
Bhavesh_Patel (12/16/2009)
But in order to insert the new city at SortOrder 4, the subsequent SortOrder also needs to be changed. This is the problem.
Is there a simple way, which will allow us to make changes in SortedCities table?
Uh-huh... and what kind of problem would that pose in a CASE statement in the ORDER BY?

You know, I can think of a solution that combines CASE and UNION and requires no modification when a new city is added (unless it's added to the specially ordered cities)

Actual code left as an exercise for the reader. 😉

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhavesh_Patel SSCrazy Points: 2259 More actions · Answer 4

Yes, I get it now. I shall create a proc which will update the SortedCities table.

Thanks Jeff, it was really a great help.

Bhavesh

[font="System"]Bhavesh Patel[/font]

http://bhaveshgpatel.wordpress.com/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Bhavesh_Patel (12/16/2009)
Yes, I get it now. I shall create a proc which will update the SortedCities table.
Thanks Jeff, it was really a great help.
Bhavesh

Thanks for taking it the right way, Bhavesh.

--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)