So, I have a query
SELECT type
FROM order, product
WHERE order.id = product.id
GROUP BY type
ORDER BY sum(units) DESC
but I only want the first row. MS SQL 2005 doesn't seem to support
"LIMIT" or "FIRST" which is unfortunate. I can shove that whole query
into another one that checks the MAX, but then I can only get the MAX
number of units within a group, when I want to know the type that has
the max units in any group.
select top 1 type from (
SELECT type,sum(units) s
FROM order, product
WHERE order.id = product.id
GROUP BY type ) t1 order by s desc
On Sep 18, 12:03 pm, Mark
> So, I have a query
>
> SELECT type
> FROM order, product
> WHERE order.id = product.id
> GROUP BY type
> ORDER BY sum(units) DESC
>
> but I only want the first row. MS SQL 2005 doesn't seem to support
> "LIMIT" or "FIRST" which is unfortunate. I can shove that whole query
> into another one that checks the MAX, but then I can only get the MAX
> number of units within a group, when I want to know the type that has
> the max units in any group.
"Mark"
news:1190088217.757875.10240@57g2000hsv.googlegroups.com...
> So, I have a query
>
> SELECT type
> FROM order, product
> WHERE order.id = product.id
> GROUP BY type
> ORDER BY sum(units) DESC
>
> but I only want the first row. MS SQL 2005 doesn't seem to support
> "LIMIT" or "FIRST" which is unfortunate. I can shove that whole query
> into another one that checks the MAX, but then I can only get the MAX
> number of units within a group, when I want to know the type that has
> the max units in any group.
>
LIMIT and FIRST are not standard SQL features. Nor is TOP but it achieves
something similar in SQL Server:
SELECT TOP 1 WITH TIES type
FROM ord, product
WHERE ord.id = product.id
GROUP BY type
ORDER BY SUM(units) DESC;
Alternatively you can do the following using standard ANSI SQL, which should
work on many different platforms:
SELECT type
FROM ord, product
WHERE ord.id = product.id
GROUP BY type
HAVING SUM(units) >= ALL
(SELECT DISTINCT SUM(units)
FROM ord, product
WHERE ord.id = product.id
GROUP BY type);
(untested)
--
David Portas
David Portas wrote:
> Alternatively you can do the following using standard ANSI SQL, which should
> work on many different platforms:
>
> SELECT type
> FROM ord, product
> WHERE ord.id = product.id
> GROUP BY type
> HAVING SUM(units) >= ALL
> (SELECT DISTINCT SUM(units)
> FROM ord, product
> WHERE ord.id = product.id
> GROUP BY type);
>
> (untested)
This could return multiple values if there's a tie for most
common type.
On 18 Sep, 08:30, Ed Murphy
>
> This could return multiple values if there's a tie for most
> common type.
Correct. So could the version using TOP WITH TIES. Mark said: "I want
to know the type that has the max units in any group". If there is
more than one such type then the specification is incomplete because
Mark doesn't say which one should come "first". Rather than pick a
random row or make the assumption that there is only one row I decided
it was safer to return everything - that way Mark can decide for
himself whether he needs to refine his spec.
--
David Portas
On Sep 18, 5:27 am, David Portas
> On 18 Sep, 08:30, Ed Murphy
>
>
>
> > This could return multiple values if there's a tie for most
> > common type.
>
> Correct. So could the version using TOP WITH TIES. Mark said: "I want
> to know the type that has the max units in any group". If there is
> more than one such type then the specification is incomplete because
> Mark doesn't say which one should come "first". Rather than pick a
> random row or make the assumption that there is only one row I decided
> it was safer to return everything - that way Mark can decide for
> himself whether he needs to refine his spec.
>
> --
> David Portas
Thanks guys. You'd think they'd have a "standard" (and simple) method
for doing this eh?
Mark (mnbayazit@gmail.com) writes:
> Thanks guys. You'd think they'd have a "standard" (and simple) method
> for doing this eh?
They have:
WITH unitsums (type, unitsum) AS (
SELECT type, SUM(units)
FROM order, product
WHERE order.id = product.id
GROUP BY type
), ranks (type, rank) AS (
SELECT type, rank AS (ORDER BY unitsum)
FROM unitsums
)
SELECT type FROM ranks WHERE rank = 1
This is how you would write this query in ANSI SQL. This particular
syntax is accepted in SQL 2005, and I believe it should run on Oracle
as well. I will have to confess that I don't really expect MySQL to
support this.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
>> You'd think they'd have a "standard" (and simple) method for doing this eh? <<
It is a little hard to guess what your tables and columns look like
from your narrative. There is even a magical, universal "id" attached
to nothing in particular! I guess you meant "product_id" and do not
have an industry standard identifier to use, like UPC. I guess type
is the product type (category?) code, and that it is in the Products
table, not the Orders. But it could be the order type, customer blood
type or anything.
One Standard SQL answer would be:
WITH ProductCategorySales(product_type, sold_tot)
AS (SELECT P.product_type, SUM(O.sold_units)
FROM Orders AS O, Products AS P
WHERE O.product_id = P.product_id
GROUP BY product_type)
SELECT product_type
FROM ProductCategorySales
HAVING sold_tot = (SELECT MAX(sold_tot) FROM ProductCategorySales);
or get fancy and use some stuff not in SQL Server yet:
(SELECT P.product_type,
SUM(O.sold_units)
OVER (PARTITION BY P.product_type)
FROM Orders AS O, Products AS P
WHERE O.product_id = P.product_id)
The reason that Standard SQL does not have LIMIT or something like it,
is that SQL is a set-oriented database language, not a sequential file
language. Such things would have to be part of a cursor's ORDER BY
clause to fit into the language model.
On Sep 23, 4:30 pm, --CELKO--
> >> You'd think they'd have a "standard" (and simple) method for doing this eh? <<
>
> It is a little hard to guess what your tables and columns look like
> from your narrative. There is even a magical, universal "id" attached
> to nothing in particular! I guess you meant "product_id" and do not
> have an industry standard identifier to use, like UPC. I guess type
> is the product type (category?) code, and that it is in the Products
> table, not the Orders. But it could be the order type, customer blood
> type or anything.
>
> One Standard SQL answer would be:
>
> WITH ProductCategorySales(product_type, sold_tot)
> AS (SELECT P.product_type, SUM(O.sold_units)
> FROM Orders AS O, Products AS P
> WHERE O.product_id = P.product_id
> GROUP BY product_type)
>
> SELECT product_type
> FROM ProductCategorySales
> HAVING sold_tot = (SELECT MAX(sold_tot) FROM ProductCategorySales);
>
> or get fancy and use some stuff not in SQL Server yet:
>
> (SELECT P.product_type,
> SUM(O.sold_units)
> OVER (PARTITION BY P.product_type)
> FROM Orders AS O, Products AS P
> WHERE O.product_id = P.product_id)
>
> The reason that Standard SQL does not have LIMIT or something like it,
> is that SQL is a set-oriented database language, not a sequential file
> language. Such things would have to be part of a cursor's ORDER BY
> clause to fit into the language model.
I didn't think the tables and columns were important. It's a fake
data set.
I hadn't heard of this "WITH" clause. I'll look into it some more.
Thanks again.
BTW, I ended up using something like WHERE unitsum=MAX(...) which
seems horrible and hacky, but will suffice.