SQL Sort Table

Hi, all. What's wrong with this?

sort table macdec2003_tbl on gpi asc, billcode asc

I've never heard of a SORT TABLE command for MS SQL. The only thing I could google on it was the following, and it looks like it is not a SQL Standard.

http://www.simple-sw.com/sql-sort.htm

My suggestion is to create a view or just use an ORDER BY statement in you SQL.

I had googled and found the same thing - that's how i got in trouble in the first place.

How do I sort to a view? What do you mean 'do it in SQL'?

The following attempt is invalid because no order by is allowed in views:

create view sorted_vw as
select * from macdec2003_tbl
order by gpi

What do you mean you can't use an ORDER BY in your CREATE VIEW select statement?

Everything I've read, including Books Online, says you can. You can't use an order by it says if you are doing a SELECT TOP n FROM tablename.

I also created a view in Enterprise Manager and it allowed me to use an ORDER BY statement.

What exactly is the problem you are having?

Here's the error message I get when executing the above code:

Server: Msg 1033, Level 15, State 1, Procedure sorted_vw, Line 3
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

Sounds like the opposite of what you said - if you do order by you DO need TOP?

Ok, my bad. In the CREATE VIEW statement you can't include the ORDER BY unless you use a TOP clause in the SELECT statement.

Are you able to go in through Enterprise Manager and create a new view from there rather than through the CREATE VIEW statement?

here's what i did, which seems a little kloogy to me

i exported the table, using the query builder during the export and sorting the columns there.

but here's another question:

i need to group the data by 'gpi', then by 'billcode' within gpi. i need to find the avg price of rx's for each billcode (1 avg price for the group of billcodes within the gpi). i think i got it but how then do i do an avg for the whole gpi group?

very kludgey indeed, when a simple SELECT with an ORDER BY does the same thing

THANK YOU, THANK YOU! Just discovered this site today and am feeling very lucky right about now. Good 'old Toronto. My Ma grew up there.

Technically this is still invalid although it may produce the correct result. Rollup is not a SQL-92 standard and do not know what the function of it is in MS-SQL.

SQL-92 is obsolete.

Rollup is defined in SQL-99.

truer words were never spake

if sql server implements something that isn't technically standard, does that stop people from using it?

have you ever tried to do date formatting in sql server? is the CONVERT() function technically valid?

the defence rests, your honour

Really... I did not realize how many vendors fully support SQL-99... do any?


Absolutely not!! I actually removed a comment from my post before I submitted it saying something similar... I should have kept it. The point being... Just because something is producing the correct result do not be surprised if in a different version it does not produce the same result when you are using non-standard code. I experienced this exact same problem when I was porting a db to a platform which did not return group by results in sorted order... that is my point.

All in all my thinking is this. If there is a standard way of doing something use it... If performance or maintanence show that a platform specific method is better than use that... but be sure to document it, measure it, and test it.

All too often I have walked into projects were the original developers took shortcuts not anticipating any change... Guess what almost everything changes eventually and good design up front reduces costs, and headaches, in the long run. Just my two cents.


This is comparing apples to oranges. The convert function is an inline function which happens to be platform specific. But the Group By and Order By are functional clauses of the SQL dialect which dictact rules that rdbms "should" follow to be SQL compliant. No where does it state that Group By must return results in sorted order, however it does state that Order by must return results in sorted order. As far as I know there is no clause in the SQL vocabulary that is used for parsing dates... so what else are you supposed to do?

No, there isn't. Some are pretty close to supporting Core SQL 99. SQL 99 will be superseded by SQL 2003 very soon. Core SQL 2003 is the same as Core SQL 99.

Neither did and DBMS fully support SQL-92. Some (but not all) supported a subset known as Entry Level SQL. (There were two more levels of compliance Intermediate and full.)

To parse dates there is a function called extract in SQL. (Defined in SQl 92 Intermediate level.)