Calculated ORDER BY? - 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 2000 Forums

Transact-SQL (2000)

Calculated ORDER BY?

Author

Topic

Phi
Starting Member

2 Posts

Posted - 2004-05-26 : 04:22:13

Hi,

Today I got a request to see if it's possible to join 2 columns together and base my ORDER BY upon that.
This is what I have, working fine:

CREATE VIEW user.view
AS
SELECT TOP 3 Id, Naam, Host, Woonplaats, IdAbon, WidthH, HeightH, YesCount
FROM user.table
WHERE (IdAbon = 3) AND (Foto = 1)
ORDER BY YesCount

Point is now, to sort the list on a combined value of YesCount & Datum
Datum is a date value, off course, and YesCount is an integer.

When I do this:

CREATE VIEW user.view
AS
SELECT TOP 3 Id, Naam, Host, Woonplaats, IdAbon, WidthH, HeightH, YesCount, Datum
FROM user.table
WHERE (IdAbon = 3) AND (Foto = 1)
ORDER BY (YesCount+Datum)

syntax check turns out succesfull, but I can't save the view.
Error code: "Error 4511: Could not perform CREATE VIEW because no column name was specified for column 10."
Which basically means, he wants the expression (YesCount+Datum) as a new column. How can I fix this?

Or, any other suggestions on how to fix it?

Thanx in Adv.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-26 : 04:29:15

I can suggest adding another column to the view:

CREATE VIEW user.view
AS
SELECT TOP 3 Id, Naam, Host, Woonplaats, IdAbon, WidthH, HeightH, YesCount, Datum, (YesCount+Datum) as ordby
FROM user.table
WHERE (IdAbon = 3) AND (Foto = 1)
ORDER BY (YesCount+Datum)

will this be ok

Duane.

Phi
Starting Member

2 Posts

Posted - 2004-05-26 : 06:33:38

WAUW!
it worked (no errors), that's for sure.
Now testing; see if it gives the results I want it to..

Looks like it is going to work!

MANY & GREAT THANX!
Phi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources