Sort least to greatest by rows

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 2005 Forums

Transact-SQL (2005)

Sort least to greatest by rows

Author

Topic

gsmccoy
Starting Member

7 Posts

Posted - 2009-07-17 : 18:56:21

I have a table called Rates that has multiple rows of columns with each column having a datatype of money and one row that is varchar(50) datatype.

Rates Table
NPANXX VARCHAR (50),
[1512] Money,
[1756] Money,
[1049] Money
etc..,
etc..

NPANXX 1512 1756 1049
201202 .0042 .0092 .0021
423468 .0021 .0045 .0023
765384 .0011 .0032 .0011

I am trying to figure out how to wright a statement that will display each row from least to greatest. Eventually I will export this to a text file.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 01:44:05

do you mean this?


SELECT [NPANXX], [1512], [1756], [1049]
FROM Table
ORDER BY [NPANXX]*1 DESC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-18 : 01:50:56

quote:
Originally posted by gsmccoy

I have a table called Rates that has multiple rows of columns with each column having a datatype of money and one row that is varchar(50) datatype.

Rates Table
NPANXX VARCHAR (50),
[1512] Money,
[1756] Money,
[1049] Money
etc..,
etc..
NPANXX 1512 1756 1049
201202 .0042 .0092 .0021
423468 .0021 .0045 .0023
765384 .0011 .0032 .0011

I am trying to figure out how to wright a statement that will display each row from least to greatest. Eventually I will export this to a text file.

Thanks

Can you show us how will the result looks like from the sample that you provided ?

KH
[spoiler]Time is always against us[/spoiler]

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2009-07-18 : 19:35:40

There's a fundamental design flaw in the table in that you have more than 1 rate per row. The table should probably be in the form of ...

NPA NXX RatePlanNumber Rate

... that way you don't have to add a new column every time you want to add a new rate plan and you don't have to hard code rateplan numbers as column names in code anywhere.

And, yeah... I do recommend breaking the NPA and NXX apart from each other so that when you have area code splits and overlays, you don't have to pull all the hair from the left side of your head during the permissive dialing period.

--Jeff Moden

"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! "
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources