running the same SQL from MS Access produced a different result

[Teg]

Yes, I know the SQL support of MS SQL is sometimes *very* different from that of MS Access (go figure, the same company produced those RDBMS <g>) however logic dictates that this should work in MS SQL too (or should it?) Anyway, I have the following table structure:

[somename]
+----+-------+------+-------+--------+
| id | catid | name | order | active |
+----+-------+------+-------+--------+

and here's my SQL statement:

SELECT top.order,
top.name,
top.id,

count(sub.id),

under.order,
under.name,
under.catid,
under.id

FROM
([somename] under left join [somename] sub
on sub.catid=under.id)
left join [somename] top
on under.catid=top.id

WHERE under.active=1

GROUP BY
top.order,
top.name,
top.id,

under.order,
under.name,
under.id,
under.catid

the output in MS Access follows the top.order however when I ran the same SQL statement in MS SQL, it grouped my outputs by [top.name] but both follows the output for the [under.order] Anyone have any fix for this? Thanks

 

[tlbroadbent]

MS SQL Server follows the ANSI standard more closely than Access in ordering of result sets. By definition and according to the ANSI standard a result set (or a table) is not ordered. Prior to version 7, SQL Server ordrered results sets when the Group By clause was used. With SQL 7, Microsoft eliminated ordering of aggregated result sets.

You can add an order by clause to your query to return a sorted result set. It should follow the Group By clause.

ORDER BY
top.order,
top.name,
top.id,

under.order,
under.name,
under.id,
under.catid

All of this is explained in SQL BOL under the topic of &quot;sp_dbcmptlevel (T-SQL).&quot; This topic covers compatibility issues between versions. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.