order by some records only

Question

Post reply

order by some records only

Radh

Mr or Mrs. 500

Points: 545
More actions
July 15, 2011 at 3:45 am

#239624

HI All

I have a requirement like, we are having one city table in our database in that Records are below format.

CITYNAME

------------

chennai

hyderabad

Bangalore

Bombay

kolkata

Bangalore

cochin

Ahemadabad

delhi

I want display the data is following format.

CITYNAME

-----------

Bangalore

Bangalore

Ahemadabad

Bombay

chennai

cochin

delhi

hyderabad

kolkata

for that I need to apply the sort condition through the order by key word in sql server2005

It sorts all the Records. But I want display the Bangalore records in the Top position. Remaining

all the records are in sort order. this is my requirement, can you please suggest me in which way

i can achieve this switchvation.

Thanks in Advance

Best Regards

Radh

Viewing 7 posts - 1 through 6 (of 6 total)

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

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 1

Mark Cowne

One Orange Chip

Points: 26865

July 15, 2011 at 4:40 am

#1353302

ORDER BY CASE WHEN CITYNAME='Bangalore' THEN 0 ELSE 1 END,CITYNAME

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Radh Mr or Mrs. 500 Points: 545 More actions · Answer 2

Radh

Mr or Mrs. 500

Points: 545

July 15, 2011 at 5:44 am

#1353336

Hi SSC Eights!

Thanks for your suggestion, It's working fine.

now i finished that requirement.

Thank you very much

Best Regards

Radh

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

CELKO (7/15/2011)

The mess you posted is not a table at all! There is no key and it has duplicate rows so it can never have a key.

Correct. It's a post of sample data. Learn the difference.;-)

This is a 1950's deck of punch cards written with SQL.

So what? The application of older technology frequently overcomes the missing functionality and poor performance of the some of the new stuff. You should know that since you're (correctly) an advocate of the "sequence" table which is nearly identical to a deck of sequentially numbered cards. 😛 In fact, you're (again, correctly) an advocate of Nested Sets which require the equivalent of 1950's style numbered cards to establish a certain "order" (up, down, lateral) without the use of an ORDER BY. 😀

INSERT INTO CityList

VALUES

('Chennai', 1),

('Hyderabad', 1),

('Bangalore', 2),

('Bombay', 1),

('Kolkata', 1),

('Cochin', 1),

('Ahemadabad', 1),

('Delhi', 1);

SELECT city_name

FROM City_list

ORDER BY city_cnt, city_name;

There's no need for such hardcoding for such a simple problem especially since several parallel requirements might just as easily require a different city to be first on the list. 😉 Besides, your suggestion would put 'Bangalore' last. :sick:

Tables do not have an ordering.

That's precisely why the previously posted solution has an ORDER BY in it.

All information is encoded as scalar values in the columns of rows of tables.

Absolutely not true. "Sort Order" qualifies as "business information" or "business rules" and such “information” is frequently and correctly manifested in layers other than the data layer.

Please read any book on RDBMS.

Please read any book on "Attention to Detail" and get over the "1950's" rant... parts of your books would not be possible without "1950's" technology especially when it comes to the "push stack" RBAR you use to migrate Adjacency Lists to Nested Sets. :w00t:

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Correct me if I'm wrong, Joe, but the SEQUENCE construct is only new to SQL Server... it's been around in products, like Oracle, for a very long time.

--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)
Intro to Tally Tables and Functions

Paul White SSC Guru Points: 150457 More actions · Answer 5

This is another way of writing a query to suit the requirement:



CREATE TABLE #Cities

(

	city_name VARCHAR (25) NULL

);

CREATE INDEX nc1 ON #Cities (city_name);

INSERT #Cities

	(city_name)

VALUES

	('Chennai'),

	('Hyderabad'),

	('Bangalore'),

	('Bombay'),

	('Kolkata'),

	('Cochin'),

	('Ahemadabad'),

	('Bangalore'),

	(NULL),

	('Delhi');



SELECT

	ranked.city_name

FROM

(

	SELECT

		city_rank = CONVERT(BIGINT, -1),

		c.city_name

	FROM #Cities AS c

	WHERE

		c.city_name IS NULL

	UNION ALL

	SELECT

		city_rank = CONVERT(BIGINT, 0),

		c.city_name

	FROM #Cities AS c

	WHERE

		c.city_name = 'Bangalore'

	UNION ALL

	SELECT

		city_rank =

			DENSE_RANK() OVER (

				ORDER BY c.city_name),

		c.city_name

	FROM #Cities AS c

	WHERE

		c.city_name <> 'Bangalore'

) AS ranked

ORDER BY

	city_rank;

GO

DROP TABLE #Cities

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

CELKO (7/16/2011)

Jeff, I now call the list of integers table the Series(seq) table because SEQUENCE is a reserved word for a new construct.

Hmmm... Joe, I have to ask... Since the are many different types of "Series", why isn't using the table name of "Series" a violation of ISO standards when using it to name a table of sequential numbers? It could just as easily be a series of dates, letters, Fibonacci numbers, non-random GUID's, etc.

--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)
Intro to Tally Tables and Functions