View On the Fly or Temp Table - 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 2005 Forums

Transact-SQL (2005)

View On the Fly or Temp Table

Author

Topic

Absir
Starting Member

4 Posts

Posted - 2008-05-06 : 09:43:14

problem:

Have a table with Data and Dependencies(Foreign Keys) and Stored Procedures, views etc.
Need the Data in that table to put in different order.
For exampl, put older years in the begining or end so when sorted by year, you will get right data.
Question is: If i use view it will be based on the table with Wrong entry order and if i use Temporary Table
each time stored procedure is run, it will be created and overhead.

Example of the table is below with Wrong order.
I should have entered the old years first.
Note this is example and not the actual table!

ID Yr Title/Model Serial#

---------------------------------
1 2005 Toyota Camery IXp12365555
2 2006 Honda Accord XJi9770009
3 2007 Honda Accord XJi9000009
4 2004 Honda Accord XJi9880009
5 2005 Honda Accord XJi9009009
6 2007 Honda Accord XJi9078009

If this example is not right, my bottom line is this:
I have entered the Data in a table and i'm getting wrong resutls when i order by Desc or Asc and now either have to delete all rows and re-enter it or have someother clever way!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 10:04:05

SELECT * FROM Table1
ORDER BY Yr, ID

E 12°55'05.25"
N 56°04'39.16"

Absir
Starting Member

4 Posts

Posted - 2008-05-06 : 10:53:05

Peso, Trust me i tried that and many other clevar ways and didn't work. The Table Example i give here might not hte best Description of what i need but the following statement can give you:

I have entered the Data in a table Wrong and i'm getting wrong resutls when i order by Desc or Asc and now either have to delete all rows and re-enter it or have someother clever way!
Other than view which is based on the Wrong Table or Temp Table which has performance issue, are there any other ways??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 11:27:02

If you can't show us the expected result based on the sample records posted above, how do you think we can help?

E 12°55'05.25"
N 56°04'39.16"

Absir
Starting Member

4 Posts

Posted - 2008-05-06 : 12:16:29

Thanks for the question:
Here is the Exact situation:
Table:
ID Description
1 Honda Accord
2 Honda EX
3 Honda LX
4 Honda CX

I need the output to be something like

Honda CX
Honda EX
Honda LX
Honda Accord

If i do
Select * From Table
Order by ID

you can see what i'm getting back
and if i do

Order by Description accord will get mixed i
I can't delete the table rows becasue of dependencies.
Any idea other than temp table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 12:36:19

select * from table1
order by len(description), description

E 12°55'05.25"
N 56°04'39.16"

Absir
Starting Member

4 Posts

Posted - 2008-05-08 : 12:11:50

Thanks a lot. You have pointe me into another area that i wasn't looking at.
This worked for me -atleast solved part of the issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources