Display cities in specified order

Question

Display cities in specified order

Bhavesh_Patel

SSCrazy

Points: 2259
More actions
December 15, 2009 at 2:43 am

#143676

I have a table of Indian cities, see the sample table below:
IDCName
1Delhi
2Agra
3Mumbai
4Chennai
5Bangalore
6Surat
7Thane
I need a Query to display the table in an such an order that my first city is 'Mumbai', second 'Bangalore', third is 'Chennai'. Followed by rest of the cities in ascending order.
The output should be:
CName
Mumbai
Bangalore
Delhi
Agra
Chennai
Surat
Thane
I need to use only one 'UNION ALL' in my query. Can anyone help me?

[font="System"]Bhavesh Patel[/font]
http://bhaveshgpatel.wordpress.com/

Viewing 15 posts - 1 through 15 (of 19 total)

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

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

Do you have a column that defines the order?

Why the limit of one union all? That sounds like something that would be given as a homework/exam question. Is that what this is?

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 2

Hi Gail,

I could construct a query which would give me the desired output, but it is using THREE 'UNIONS'. See the Query below:

SELECT * FROM

(SELECT TOP 1 CName FROM cities WHERE CName='Mumbai'

UNION ALL

SELECT TOP 1 CName FROM cities WHERE CName ='Bangalore'

UNION ALL

SELECT TOP 1 CName FROM cities WHERE CName ='Delhi'

UNION ALL

SELECT TOP 10 CName FROM cities WHERE CName NOT IN ('Mumbai','Bangalore','delhi') ORDER BY CName)

AS A

But is there any way to restrict the use of only one 'UNION' in the above Query? Are there any other methods to get the required output?

Bhavesh

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

http://bhaveshgpatel.wordpress.com/

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

Is there a column that defines the order that you want to cities displayed?

Why is there a limit of one Union All. A nonsensical restriction like that makes this look like homework/exam question/interview question. Is it?

Here's a hint. Work out how to display the first 3 in the order wanted (using IN) then work out how to display the rest in the order wanted (using NOT IN). Then union them together. You should take steps then to ensure that the correct order is ALWAYS returned (meaning another Order By)

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

No, there is no column that defines the order of the cities ...

we have to sort the table based on cName (cities) column.

Yes its an interview question ... and I am desperately looking for an solution.

Getting to display the first 3 in the order without using 'Union' is the real problem. The order should be - 'Mumbai', ,'Bangalore', 'Delhi' and then followed by rest of the cities in alphabetical order.

Help Needed.

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

http://bhaveshgpatel.wordpress.com/

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

Bhavesh_Patel (12/15/2009)
No, there is no column that defines the order of the cities ...

Then, make a sister table (either permanent or temporary) and use it.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Bhavesh_Patel (12/15/2009)
Yes its an interview question ... and I am desperately looking for an solution.
Help Need.

I'm sorry, I don't see how we should help here. This is not that difficult a task if you think about it. In fact, I think I know a way to do it without any UNION or UNION ALL statements.

Think CASE for another option.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Lynn Pettis

SSC Guru

Points: 442467

December 15, 2009 at 6:40 am

#1092468

Yep, I can do it without using a UNION.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 8

I agree with Lynn. A combination of case and order by should do the trick.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

Bhavesh_Patel (12/15/2009)
Yes its an interview question ... and I am desperately looking for an solution.

So if I give you the answer do I get the job? The point of an interview is to tell what the candidate knows or can figure out, not what some random person at a keyboard half way around the world knows or can figure out.

We've given you more than enough hints.

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 10

Gail Shaw (12/15/2009)
So if I give you the answer do I get the job?

No you won't, its already gone.

The point of an interview is to tell what the candidate knows or can figure out, not what some random person at a keyboard half way around the world knows or can figure out.

Thanks, now I figured out what are interviews for. But poor guy like me who doesn't have any Offline resources to fall back on, was looking for an online help to gain more knowledge and find more effective and efficient solutions.

Isn't it good idea to prepare yourself by learning new tricks for your future opportunity?

Anyways, thanks for the help Gail.

Bhavesh Patel

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

http://bhaveshgpatel.wordpress.com/

Bhavesh_Patel SSCrazy Points: 2259 More actions · Answer 11

Adi (12/15/2009)
I agree with Lynn. A combination of case and order by should do the trick.

Thanks Lynn and Adi, for your valuable help.

Yes, it did the trick. I could construct a new query with 'CASE' and 'ORDER BY'

and avoiding 'UNIONS' which was my primary objective.

This is how my new Query looks which gives me the desired output:

-- Query Start

SELECT cname FROM

(SELECT cname, TempID = CASE

WHEN cname='Mumbai' THEN '1'

WHEN cname='Bangalore' THEN '2'

WHEN cname='Delhi' THEN '3'

ELSE '4'

END

FROM Cities) AS A

ORDER BY TempID, Cname

-- Query End

Adi, this is what you were suggesting? Or, am I missing some thing?

Bhavesh Patel

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

http://bhaveshgpatel.wordpress.com/

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

What you're missing with the CASE method in the ORDER BY is that if another city is required, you now have to change code.

Make a table with the city names and the sort order. Join to that to get the sort order. If you need to make a change, it's a very simple effort to just update the table.

Don't hardcode this stuff and don't fall into the trap of thinking they won't add cities.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Jeff Moden (12/15/2009)
What you're missing with the CASE method in the ORDER BY is that if another city is required, you now have to change code.
Make a table with the city names and the sort order. Join to that to get the sort order. If you need to make a change, it's a very simple effort to just update the table.
Don't hardcode this stuff and don't fall into the trap of thinking they won't add cities.

Jeff,

I actually agree with you, it really should be done that way. I was just suggesting another way that it "could" be done.

Bhavesh_Patel SSCrazy Points: 2259 More actions · Answer 14

Jeff Moden (12/16/2009)
Make a table with the city names and the sort order. Join to that to get the sort order.

Hi Jeff,

As per your suggestion I made a new table (SortedCities) with City names and sort order. Now I have Two Table:-

1. Cities - Table with unsorted cities

ID CName

1Delhi

2Agra

3Mumbai

4Chennai

5Bangalore

6Surat

7Thane

8Pune

2. SortedCities - Table with city names and the sort order

SortOrderSortedCName

1Mumbai

2Bangalore

3Delhi

4Agra

5Chennai

6Pune

7Surat

8Thane

Now I can display the desired result by joining the Cities Table with the SortedCities Table. My Query is:

-- Query Start

SELECT a.CID, a.CName

FROM cities AS a INNER JOINSortedCities AS b

ON a.CName=b.SortedCName

ORDER BY b.SortOrder

-- Query End

The above Query will give me the required output. i.e.-

CIDCName

3Mumbai

5Bangalore

1Delhi

2Agra

4Chennai

8Pune

6Surat

7Thane

Jeff Moden (12/16/2009)
If you need to make a change, it's a very simple effort to just update the table.

Now I have a problem over here. When I have to add new city in the SortedCities table Say 'Hyderabad' at SortOrder '4'. So that my updated SortedCities table should be:

SortOrderSortedCName

1Mumbai

2Bangalore

3Delhi

4Hyderabad

5Agra

6Chennai

7Pune

8Surat

9Thane

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?

Bhavesh

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

http://bhaveshgpatel.wordpress.com/