Help with SQL queries?

[mbonfyre]

I am DESPERATE for help with an sql query, does anyone know of a good forum?

[holly]

Try checking out Beacons thread...

[techgnome]

Well, depending on your problem, this is a good place to start, or you can try http://dbforums.com or I've heard that http://www.sql-server-performance.com is pretty good too (I haven't visited it yet).

What's the prob?

[mbonfyre]

Im stuck on this question (It's homework, so I would like DIRECTION not the answer)

'For any given part, give the details of the supplier with the cheapest purchase price. The user should be prompted to enter the supplier number on the screen.'

I've been trying various combinations similar to this:

<SQL> SELECT MIN(Price) AS LowestPrice, SUP_NAME, SUP_TOWN, SUP_COUNTY
2 FROM MMSuppliers s, MMParts p;
SELECT MIN(Price) AS LowestPrice, SUP_NAME, SUP_TOWN, SUP_COUNTY</SQL>
*
ERROR at line 1:
ORA-00937: not a single-group group function

Any Ideas how to point me in the right direction.
P.s I'm a newbie, so please don't get all technical on me

[techgnome]

Well, the problem is the SELECT statement.
In as simple terms as I can, the problem is this: When you use an aggregate function, such as MIN, MAX, SUM, AVG, (and there are more), you have to do a GROUP BY, and then tell it what fields to group on. Usually, this would be all of your other non-aggregated fields.
Here's an example. Let's say I have a table, for simplicity, three fields: PriceID, PriceValue, and PriceName.
The table looks like this:

1     SomePrice      2.00
1     SomePrice      5.50
1     SomePrice      5.95
1     SomePrice      5.00
3     AnotherPrice  2.50
3     AnotherPrice  3.00
4     DifferentPrice 1.25
4     DifferentPrice 0.25
4     DifferentPrice 0.29
4     DifferentPrice 0.95
5     OldPrice         4.30
5     OldPrice         4.95
5     OldPrice         4.15

If I wanted to find the lowest price, I can do SELECT MIN(PriceValue) FROM tblPrice. That will give me the smallest value of PriceValue. I would get the result: 0.25
But, let's say that I want to find the lowest price for each price. It would look like this: SELECT PriceID, PriceName, MIN(PriceValue) FROM tblPrice GROUP BY PriceID, PriceName.
I would get something that should look like:

1     SomePrice      5.00
3     AnotherPrice  2.50
4     DifferentPrice 0.25
5     OldPrice         4.15

Hope this helps some.