SELECT * WHERE val = MIN ( val )?

Question

I'm fairly new to Oracle SQL, but already it's logic is beginning to confuse me. I'm trying to select all columns from a table where a particular column PRICE has the minimum value.

This works:

SELECT MIN(PRICE) FROM my_tab;

This returns me the minimum value. But why can't I select all the columns in that row? The following won't work:

SELECT * FROM my_tab WHERE PRICE = MIN( PRICE );

What am I missing here? Cheers folks!

*EDIT*

Here is the full code I'm having trouble with:

SELECT * FROM ( SELECT c.NAME, o.* FROM customers c JOIN customer_orders o ON c.CUST_NBR = o.CUST_NBR ) AS t WHERE t.PRICE = ( SELECT MIN( t.PRICE) FROM t );

You can't use an aggregate in the where clause of a select statement. That is a syntax error. — Eric Leschinski, Apr 04 '13 at 14:00
It's not unique to Oracle - it's common to most implementations of SQL. — Damien_The_Unbeliever, Apr 04 '13 at 14:01

Santhosh · Answer 1 · 2013-04-04T16:02:43.127

2

SELECT * FROM TABLE WHERE PRICE = (SELECT MIN(PRICE) FROM TABLE)

--Edited 

WITH 
TABLE AS 
(QUERY) 
SELECT * FROM TABLE
WHERE PRICE = (SELECT MIN(PRICE) FROM TABLE)

edited Apr 04 '13 at 16:02

answered Apr 04 '13 at 13:59

Santhosh

1,771
1
15
25

This doesn't work. The table I'm selecting from is already from a select statement. Will that cause a problem? I posted my code in a comment already but for some reason it was removed – Boon Apr 04 '13 at 15:15

score 0 · Answer 2 · answered Apr 04 '13 at 14:00

0

You can also use a subquery to get the result:

select t1.*
from my_tab t1
inner join
(
  SELECT MIN(PRICE) MinPrice
  FROM my_tab
) t2
  on t1.price = t2.minprice

answered Apr 04 '13 at 14:00

Taryn

242,637
56
362
405

score 0 · Answer 3 · edited May 23 '17 at 12:29

0

See previous SO question, and especially answer by "Vash" which is best for your purposes. Note that you probably want to avoid a subselect since Oracle may be smart enough to use an index on the price if available to look at only one record.

Most databases, but apparently not Oracle, have either TOP 1 or LIMIT clauses for questions like these.

edited May 23 '17 at 12:29

Community

1
1

answered Apr 04 '13 at 15:30

Andrew Lazarus

18,205
3
35
53

SELECT * WHERE val = MIN ( val )?

3 Answers3