Help with a couple SQL queries

[Tarrant64]

I have a few queries I have to change according to an email I just got. I am gathering up some data from tables and when I put them all together, let's say I have a customer who purchased 200 wines and has purchased the most(yes...the winestore database), how do I just show him when I do the sort?

The original method I had used was to find the max's and sort in descending order, limiting it by 1.

EXAMPLE:

SELECT firstname, surname, MAX(qty*price) FROM customer, orders, items WHERE customer.cust_id=orders.order_id AND orders.cust_id=items.cust_id and orders.order_id=items.order_id GROUP BY firstname, surname ORDER BY Max(qty*price) DESC LIMIT 1;

That query might still be a little messed up, but I get the result I need I think. But is there another way to do this without using the order by desc limit 1 method? I think if I just get help on this one I'll be able to apply it to the others. I spent a couple hours every day working on the queries, and just a couple hours ago it hit me to use this method, and I just now go an email from the professor because other students asked the same thing, and he says it's no go.

Thanks!

 

[Jeraden]

you can do this:

SELECT firstname, surname, qty*price
FROM customer, orders, items
WHERE customer.cust_id=orders.order_id
AND orders.cust_id=items.cust_id
and orders.order_id=items.order_id
and qty*price =
(select max(qty*price)
FROM customer c2, orders o2, items i2
WHERE c2.cust_id=o2.order_id
AND o2.cust_id=i2.cust_id
and o2.order_id=i2.order_id)

or something like that, that way it will only return 1 row (or multiple rows if multiple people have the same qty*price total).

 

[Mark R]

The catch with using 'LIMIT' is that it limits you to that number of rows - not necessarily, that number of results. If you get ties, then LIMIT won't necessarily give you all the rows in the tie - it'll just tie break randomly. LIMIT is also a non-standard SQL command - so while it may work on the database you're using, it won't work the majority (it certainly doesn't work on the 'big-boys': Oracle, MS SQL and DB2).

If you are need to accept ties, then you should use Jearden's method.

 

[Rage187]

or

select top 1 firstname, surname, MAX(qty*price) FROM customer, orders, items WHERE customer.cust_id=orders.order_id AND orders.cust_id=items.cust_id and orders.order_id=items.order_id GROUP BY firstname, surname ORDER BY Max(qty*price) DESC

 

[Tarrant64]

Ok, thanks a lot guys! I will be giving this a shot here pretty soon I'll let you know what I come out with.

Rage, how was that "top 1" work?

 

[Rage187]

top works like,

"show me the top 100 records that meet the following query"

I use most when trying to get example results from a query out of a table that may have 100'000's of entries.

Ex.

Select top 100 cltfirstname, cltlastname, cltaaddress1
from clients
inner join clientaddresses on cltcode=cltaclientcode
where cltlastname like 'Smith%'


if I ran the above without the Top 100 I would end up with 10,000+ results, I really don't need them but just want a subset of them so I use Top to give me the first 100 results that meet the where criteria.

Top, Where 1=2 are my favorite query tricks to see what tables and queries will look like when hitting big tables such as Clients.

 

[Tarrant64]

Originally posted by: Jeraden
you can do this:

SELECT firstname, surname, qty*price
FROM customer, orders, items
WHERE customer.cust_id=orders.order_id
AND orders.cust_id=items.cust_id
and orders.order_id=items.order_id
and qty*price =
(select max(qty*price)
FROM customer c2, orders o2, items i2
WHERE c2.cust_id=o2.order_id
AND o2.cust_id=i2.cust_id
and o2.order_id=i2.order_id)

or something like that, that way it will only return 1 row (or multiple rows if multiple people have the same qty*price total).

Thanks a lot. This looks like it's giving me the info I want. I will apply it to the other ones as well!

 

[Tarrant64]

Is TOP 1 similar to LIMIT 1 though? As in it won't give you the top results if more than one people have the max value?

 

[Tarrant64]

Ok I was able to fix the other ones. What about the Max of a row that was counted.

Max(count(blah)) How do I make this work?

I will get an invalid use of group function if i try it that way...

 

[Tarrant64]

quick up.

 

[Tarrant64]

up.

 

[Mark R]

SELECT Max(CountOfFoo) AS MaxFoo
FROM
(
SELECT Count(Foo) AS CountOfFoo
FROM
Bar
GROUP BY
Blah
) Foobar

Originally posted by: Tarrant64
Is TOP 1 similar to LIMIT 1 though? As in it won't give you the top results if more than one people have the max value?

TOP is microsoft proprietary.
LIMIT is MySQL proprietary.

They both do essentially the same thing.

They will both limit the results to no more than the number of rows specified.

E.g. SELECT TOP 10 * FROM Wibble ORDER BY Price DESC
will never give more than 10 rows - even if there are 20 items sharing the highest price.

TOP does have a 'WITH TIES' option which does correctly deal with ties.