Best solution? (too few parameters. Expected 1.)

[Sonnich]

I am making an app which has to work on a number of DB systems. This
requires simple SQL which will work almost everywhere.
Something like this does not work in MS access

select b.name,a.code,count(a.code) as x
from a, b
where....
group by code
order by x

So I have to do this

--- same--..
group by code,name
order by count(a.code)

Or is there a better solution?
I wonder hot the 2nd count is translated, e.g. when I have large tables
and different systems, it will run it 2 times, and become slower.

Sonnich

[Anthony England]

"Sonnich" <so************@elektrobit.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

I am making an app which has to work on a number of DB systems. This
requires simple SQL which will work almost everywhere.
Something like this does not work in MS access

select b.name,a.code,count(a.code) as x
from a, b
where....
group by code
order by x

So I have to do this

--- same--..
group by code,name
order by count(a.code)

Or is there a better solution?
I wonder hot the 2nd count is translated, e.g. when I have large tables
and different systems, it will run it 2 times, and become slower.

Sonnich

The first has "group by code" while the second has "group by code,name".
Could it be that you did not mean to write this and in fact you question is
can I sort on an alias column?
The short answer is no, but you could do the following to query the sample
Northwind database to get a list of customers with those that had placed the
most orders at the top of the list. Here we use the column number in the
order by clause.

Original:

SELECT Customers.CustomerID, COUNT(*) AS Qty
FROM Customers INNER JOIN Orders ON
Customers.CustomerID=Orders.CustomerID
GROUP BY Customers.CustomerID
ORDER BY COUNT(*) DESC

Changed to:

SELECT Customers.CustomerID, COUNT(*) AS Qty
FROM Customers INNER JOIN Orders ON
Customers.CustomerID=Orders.CustomerID
GROUP BY Customers.CustomerID
ORDER BY 2 DESC
As to how much any application might slow down, I think you have to just try
it out for yourself with sample data. Even if it did double the time (which
I'm not sure it would), you also need to look at the absolute time - e.g.
another 0.3 seconds may be of no consequence. In cases where the speed of a
query is an absolute must, and you have no other choice you can break the
normalisation rules and store a calculated column as an indexed field in the
table. Of course, that brings its own issues with it, but nothing will beat
it for speed.

[Sonnich]

Hmmm, I better explain a bit here:

GROUP BY Customers.CustomerID, Customers.something_else
The point: I found, that if I retrive more than one field, I need to
group by all of them, but when using MySQL I don't. This is simple, and
will work everywhere.
If there would be any question here, then it would be something like:
what is standard sql? (e.g. SQL-92)?

ORDER BY COUNT(*) DESC
This is more interesting - I found, that in MySQL I can have a generic
(?) field here, such as "count(something) as x", but it does not work
with MS Access, there I have to use the "count..." in both places,
which eventually might result in the DB reading that field/running that
action twice.
Again, I wonder what is standard with SQL....

Anyway, the solutitions described here works with MS Access, I haven't
had an opputunity to test it on MySQL again.

HTH
Sonnich