lowest price > 0

Hello,
How can I select the lowest price excluding 0?
in this example '4'
part price
001 0
001 4
001 5
thx, shSELECT MIN(price) FROM [YourTable] WHERE
PRICE > 0
-Jason
"Steve H" <steve9@.gmail.com> wrote in message
news:OsEfMuJMFHA.3844@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I select the lowest price excluding 0?
> in this example '4'
> part price
> 001 0
> 001 4
> 001 5
> thx, sh
>|||nevermind, coffee hadn't quite settled into the brain this morning...
"Steve H" <steve9@.gmail.com> wrote in message
news:OsEfMuJMFHA.3844@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I select the lowest price excluding 0?
> in this example '4'
> part price
> 001 0
> 001 4
> 001 5
> thx, sh
>|||select min(t.price)
from
(select price
from mytable
where price > 0) t
"Steve H" <steve9@.gmail.com> wrote in message
news:OsEfMuJMFHA.3844@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I select the lowest price excluding 0?
> in this example '4'
> part price
> 001 0
> 001 4
> 001 5
> thx, sh
>

Lowest Marks

I have the following 3 tables.
Student Table : S_ID, FNAME, LNAME
UNIT : U_ID, UNITNAME
Marks Table : S_ID, U_ID, YEAR, MARKS
A student can enrol in multiple units of study each year. At enrolment by default their marks is zero.

Given a certain year we need to output the lowest marks obtained by a student in each unit. Students with zero marks should be excluded.
The SQL query shall output the following :
FNAME, LNAME, UNITNAME, MARKS
Example find the lowest marks obtained for all units in 2002. (The unitname can only appear once in the result).

Can the above query be performed in a single select statement? If yes how ? If no what is the alternate?Hi
Kindly use the following query to get the required results.

select t1.sid, t1.fname, t1.u_id, l2.u_name, t1.marks from (select l1.sid, l1.fname, l3.u_id, l3.marks from L1, L3 where L1.sid=l3.sid and l3.marks!=0) t1, l2 where t1.u_id=l2.u_id;

Thanx and Regards
Aruneesh|||Everything that you wanted is coming packaged in just one query.
Hope it works for you fine.|||I suppose the question was not clear therefore the reply by aruneeshsalhotr did not answer the qestion. I will try & simplify with an example.

Student Table : S_ID, FNAME, LNAME
Student Data :
001, Jack, Russel
002, Mark, Benny
003, John, Wayne

Unit Table : U_ID, UNITNAME
Unit Data :
MA, Maths
EN, English
SC, Science

Marks Table : S_ID, U_ID, YEAR, MARKS
Marks Data :
001, MA, 2002, 80
001, EN, 2002, 60
001, SC, 2002, 0
002, MA, 2002, 50
002, EN, 2002, 70
002, SC, 2002, 60
003, MA, 2002, 0
003, EN, 2002, 0
003, SC, 2002, 55
003, MA, 2003, 50
003, EN, 2003, 70
001, SC, 2003, 50

The Output required for the query -> find the lowest marks obtained for all units in 2002 is as follows:
FNAME, LNAME, UNITNAME, MARKS
Jack, Russel, English, 60
Mark, Benny, Maths, 50
John, Wayne, Science, 55
In the above result all those with 0 marks have been eliminated.

Is it possible to have a all in one query for the above output?

Lowest Cost

OK, this should be an easy one but my brain isn't quite working right now.

I have a table, we'll call Table1 like so:

ProductID Supplier Cost
12345 A 14.50
12345 B 13.49
12345 C 12.00
43222 A 15.00
43222 B 15.21
43222 C 13.99
12312 B 14.00
15421 A 21.99
15421 C 20.00

And I want to Get the name of the Supplier with the Lowest cost,
I know I can go like:

SELECT ProductID, MIN(Cost) FROM Table1 GROUP BY ProductID

and get the lowest cost, but what would be the most effiecent way to get all three fields returned by the query? I need the ProductID, Supplier and Cost.

Thanks,

select d.productid, t.supplier, d.cost
from table1 t
join
(
select productid, min(cost) cost
from table1
group by productid
) d on d.productid = t.productid and d.cost = t.cost

|||Thanks, that was what I was looking for. I'm still getting duplicate records if there are two suppliers with the same lowest cost for a product. But thats something I can work out.

Thanks.|||Just throw a DISTINCT on the outer query|||The extra distinct wont eliminate multiple suppliers with matching lowest prices

try adding a Top 1 to the outer query|||>>Just throw a DISTINCT on the outer query

>>>The extra distinct wont eliminate multiple suppliers with matching lowest prices
try adding a Top 1 to the outer query

You are correct mbanavige, I misread the problem to be the same supplier with multiple min(costs).

Though, TOP 1 will not do it either

DeoDev, I am having a hard time understanding why supplier has any relevance then in this resultset??|||Its for a ordering system, where it will automatically order products that are out of stock from the supplier who has the lowest price, so I need to know who has the lowest price so I can place the order with them, and I'd prefer to return the information in one table.

But with having two suppliers with the same lowest cost, It doesn't matter which one to order from out of the two. I think I have to do a little bit more thinking on the design of this function.|||I see.

Though I would hate to be the supplier whose name doesn't get returned in this result
:)