order by some records only
-
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
-
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
-
Hi SSC Eights!
Thanks for your suggestion, It's working fine.
now i finished that requirement.
Thank you very much
Best Regards
Radh
-
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) -
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) -
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
-
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)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply