SQL query question

[Gimpster]

I have a SQL database and I'm working on the SQL statement for the Recordset. I have a table that I want to order first by the category and second by the description. However I am not sure how to do this. It does not work to have 2 ORDER BY clauses, and it also does not work to have a GROUP BY and an ORDER BY clause. Does anyone know how I can do this, or is it just not possible?

------------------
Ryan
[email protected]
ICQ# 47799046

[Clunietp]

Select * from MyTable order by category, description

Bookmark this page, Gimpster, you'll find it very useful: http://w3.one.net/~jhoffman/sqltut.htm

[JHausmann]

And, if you're feeling particularly cryptic, some DBMS's allow you to use column number in place of column name.

For example:

Select * from MyTable order by 1, 19

Useful, mainly, when you're reaching the SQL string limit for the particular DBMS _or_ you feel like driving the poor sod that has to maintain your stuff, over the edge.

[Gimpster]

Thanks for the help. But, JHausmann, what did you mean when you were talking about reaching the string limit for the DBMS?

------------------
Ryan
[email protected]
ICQ# 47799046

[Gimpster]

Clunietp, I know you can do that in Sybase SQL Server, and that almost definitely means you can do it in Microsoft SQL Server (because they are almost identical in the way they react). But more likely, you can probably do it with any SQL database. As for databases like Access and other oddball ones, I don't know. It probably varies, depending on whether the programmers thought to put it in the program.

------------------
Ryan
[email protected]
ICQ# 47799046

[Clunietp]

Thanks Gimpster

[Clunietp]

Interesting, what DBMS' can you do that 1,19 thing with?

Thanks John

[JHausmann]

Tom, I've done it in SQL server, SQL/DS (VM's version of DB/2) and DB/2.

Gimpster, it depends on what you're using to send the SQL (in our shop, we typically use MS Access as a front end to SQL Server). I don't know what the size is but Access definitely has a limit, I've run into it...

[Gimpster]

Well, do you know what to look under so I can find out what the limit is, if there is a limit?

------------------
Ryan
[email protected]
ICQ# 47799046

[JHausmann]

I'll see if I can find it...

According to Access 97 Help (Query specifications)

Number of characters in a SQL statement - ~64,000

_also of interest_

Maximum number of ANDs in a where or having clause - 40

Maximum number of fields in a recordset - 255

[This message has been edited by JHausmann (edited 12-30-1999).]