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

Sorting least to greatest by rows

Author

Topic

gsmccoy
Starting Member

7 Posts

Posted - 2009-08-26 : 08:10:24

I am running SQl 2008 and am trying to figure out a way to sort a row of numbers from least to greatest. I am attempting to write a telephone Lest Cost Routing program.

Here is an example of what I am trying to accomplish. My table is created on the fly and does not have any identity. The NPANXX column is a 6 digit number that represent the area code and three digit prefix of all possible telephone dialed digits. The rest of the columns are my trunk groups that I have that terminate to my traffic vendors. The table has over 600,000 rows of information and the vendors columns are around 30 different columns.

I am trying to figure out a way to write a stored procedure that will create a new table per trunk group; thus 30 new tables. It will then look at each NPAXXX row and sort the row least to greatest. If the trunk group is one of the least three costs then it will add that NPANXX and the cost to the new table. If not is will simply bypass it

I was thinking of the best way was to create a master table that have all the figures sorted least to greatest and then perform the new table creation.

Any help pushing me in the right direction would be much appreciated.

Example of Current Table Structure

MasterRateTable
NPANXX VARCHAR(50)
1505 money
1870 money
1872 money
1845 money
Etc
Etc

NPANXX 1505 1870 1872 1845 etc etc
202112 .013 .019 .034 .011 .003 .023
202113 .014 .011 .032 .012 .004 .033

Resulting Tables
1505
202112 .013
202113 Null (The cost .014 was not in the least three rates)

1870
202112 Null (The cost .019 was not in the least three rates)
202113 .011

1872
202112 Null (The cost .034 was not in the least three rates)
202113 Null (The cost .032 was not in the least three rates)

1845
202112 .011
202113 .012

Thanks in advance for any assistance.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-26 : 08:59:02

You might be doing this the hard way. Have you thought about creating a view instead? It's been a while since I worked in telecom but a table with 600K rows isn't that bad if indexed properly. How old is the data now and how big do you expect the table to grow?

How often do new trunk groups get created?

An infinite universe is the ultimate cartesian product.

gsmccoy
Starting Member

7 Posts

Posted - 2009-08-26 : 12:51:37

I have not considered a view. I will read up on views. The data is very current and gets updated daily a few times a day even. I do not expect the data to really grow much but it will change constantly. New Trunk Groups are more rare. Once a month is expected.

Thanks

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-26 : 13:16:25

Yeah I figured your data was being updated a lot, hence my suggestion about views. Since trunks don't get added very often once you get one view created as a template the others will follow very easily.

You'll probably want to look into unpivot too.

Something like the following will be the core of your statement and you'll want to check for top 3 values before you filter out other trunks.

SELECT NPANXX, Trunk , amount
FROM
(select NPANXX, 1505, 1870, 1872, 1845 from MasterRateTable) p
UNPIVOT
(Amount FOR trunk IN ( 1505, 1870, 1872, 1845)) as unpvt

An infinite universe is the ultimate cartesian product.

gsmccoy
Starting Member

7 Posts

Posted - 2009-08-28 : 09:14:42

Thanks,

I am working on a SP that seems to be working but I am still a ways from finishing it. I will post my solution.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources