Thread: SQL Sort Table

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Palo Alto, Manhattan, Michigan
    Posts
    9
    Rep Power
    0

    SQL Sort Table


    Hi, all. What's wrong with this?

    sort table macdec2003_tbl on gpi asc, billcode asc
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    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.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Palo Alto, Manhattan, Michigan
    Posts
    9
    Rep Power
    0
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    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?
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Palo Alto, Manhattan, Michigan
    Posts
    9
    Rep Power
    0
    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?
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Location
    Cincinnati, OH USA
    Posts
    111
    Rep Power
    11
    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?
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Palo Alto, Manhattan, Michigan
    Posts
    9
    Rep Power
    0

    avg on a group & on a group within group


    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?
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    very kludgey indeed, when a simple SELECT with an ORDER BY does the same thing
    Code:
    select gpi
         , billcode
         , avg(rxprice) as abgrxprice
      from macdec2003_tbl
    group
        by gpi     
         , billcode
    with rollup
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Location
    Palo Alto, Manhattan, Michigan
    Posts
    9
    Rep Power
    0
    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.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    select gpi
    , billcode
    , avg(rxprice) as abgrxprice
    from macdec2003_tbl
    group
    by gpi
    , billcode
    with rollup
    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.
    Code:
    select top 100% 
           gpi
         , billcode
         , avg(rxprice) as abgrxprice
      from macdec2003_tbl
    group by 
        gpi, billcode
    order by gpi
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,376
    Rep Power
    391
    SQL-92 is obsolete.

    Rollup is defined in SQL-99.
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,124
    Rep Power
    4274
    Originally posted by abombss
    Technically this is still invalid although it may produce the correct result.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    49
    Rep Power
    11
    SQL-92 is obsolete
    Really... I did not realize how many vendors fully support SQL-99... do any?

    if sql server implements something that isn't technically standard, does that stop people from using it?
    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.

    have you ever tried to do date formatting in sql server? is the CONVERT() function technically valid?
    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?
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,376
    Rep Power
    391
    Really... I did not realize how many vendors fully support SQL-99... do any?
    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.)

IMN logo majestic logo threadwatch logo seochat tools logo