order by all column names?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

order by all column names??

Author

Topic

Ledge
Starting Member

2 Posts

Posted - 2012-03-19 : 03:04:56

Hey guys.
Not sure if this is possible, but here goes:
Lets say I have a table with Columns that are named as such, with records showing numeric data:
AA12 - 123456
BCA2 - 123456
0000 - 123456
01AB - 123456
ABCD - 123456
DCBA - 123456
EFCA - 123456
EF19 - 123456
Lets call the Column names id's.
Each record added to the table will contain a count for each of those id's.

Now, what if I wanted to execute a SELECT query that pulled in a single record, but ordered the fields in ASC/DESC order of all the Column names?
My Results would be as follows:
0000 - 123456
01AB - 123456
AA12 - 123456
ABCD - 123456
BCA2 - 123456
DCBA - 123456
EF19 - 123456
EFCA - 123456

Why you ask? Well, new columns will be added to this table as time goes on and I will not be able to keep up with the order of the columns within the table.

Is this possible?
Thanks guys.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-03-19 : 06:18:16

If you KNOW you are going to be adding columns as time goes on, you REALLY need to rethink your design.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-03-19 : 06:32:20

ask not what your columns can do for you, rather what you can do for your columns.

This is a bad design. It would be much better to store these in the format you presented:
Code - Value
------------------
AA12 - 123456
BCA2 - 123456
0000 - 123456
01AB - 123456
ABCD - 123456
DCBA - 123456
EFCA - 123456
EF19 - 123456

Probably with another column with the key information (pointing to the entity that these code / values represent.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Ledge
Starting Member

2 Posts

Posted - 2012-03-19 : 07:39:55

Thanks very much for your honesty, although I had a feeling that would be the case.
So if I am stuck with additional id's being added to my table/database, any suggestions on how I should accommodate them, knowing that I will need to make sure I can sort them correctly?
Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 09:04:07

Well, new columns will be added to this table as time goes on and I will not be able to keep up with the order of the columns within the table.

you should not be concerned on order of columns inside table at all, as you can always retrieve them in the order you want by specifying required order in select statement. for ex if you want 5th column to come first you can use like

select col5,col1,col2,.. from table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources