CODEWITHSUNDEEP
sqloracle-databasesubquery
Johnny90
Johnny90

Reputation: 503

SQL query: Select max value of subselect

I've got a sql statement which already works, but i think there must be a better solution than mine.

I'm trying to get the articles with the highest price which have never been sold.

With this select I'm getting all the Articles (number + price) that haven't been sold yet:

select anr, price
from article a 
where not exists(
    select 1 from OrderItems o
    where o.artnr = a.anr 
  )

The article number + price result looks like

| Anr | Price |
| 1   | 300.0 |
| 4   | 340.0 |
| 5   | 340.0 |
| 3   | 200.0 |

My temporary solution to get the articles with the highest price is:

select anr, price 
from article 
where anr in(
  select anr
  from article a 
  where not exists(
    select 1 from OrderItems o
    where o.artnr = a.anr 
  )
)
and price = (
  select max(price)
  from article a 
  where not exists(
    select 1 from OrderItems o
    where o.artnr = a.anr 
  )
)

The correct solution is:

| Anr | Price |
| 4   | 340.0 |
| 5   | 340.0 |

Is there a way to avoid having the same subselect twice?

For testing here is the shortened Create Table script with my insert values:

CREATE TABLE Article
(
  Anr Int Primary Key,
  Price Numeric(9,2) Not Null
);

CREATE TABLE Orders
(
  OrderNr Int Primary Key
)

CREATE TABLE OrderItems
(
  OrderNr Int References Orders On Delete Cascade, 
  ItemNr  Int,
  Artnr   Int References Article Not Null,
  Amount  Int Not Null Check(Amount >= 0),
  Primary Key(OrderNr, ItemNr)
)

-- articles without an order
Insert into Article (Anr, Price) values(1,300.0);
Insert into Article (Anr, Price) values(4,340.0);
Insert into Article (Anr, Price) values(5,340.0);
Insert into Article (Anr, Price) values(3,200.0);

-- articles for order with orderNr '1'
Insert into Article (Anr, Price) values(2,340.0);
Insert into Article (Anr, Price) values(6,620.0);

-- insert test order that contains the two articles
Insert into Orders (OrderNr) values (1);
Insert into OrderItems(OrderNr, ItemNr, Artnr, Amount) values(1,1,2,4);
Insert into OrderItems(OrderNr, ItemNr, Artnr, Amount) values(1,2,6,2);

I also read the topic Select max value in subquery SQL but I think in my case there must be a shorter way of doing the select.

Upvotes: 2

Views: 4227

Answers (2)

Er Ketan Vavadiya
Er Ketan Vavadiya

Reputation: 283

SELECT a.*
FROM article a LEFT JOIN OrderItems o ON a.anr = o.artnr
WHERE o.artnr IS NULL 
AND a.price = (SELECT TOP 1 a.price
           FROM article a LEFT JOIN OrderItems o ON a.anr = o.artnr
           WHERE o.artnr IS NULL
           Order By a.price Desc
           )

Try this ....

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520918

Here is a solution which avoids one of the correlated subqueries you had, instead replacing it with a LEFT JOIN:

SELECT a.*
FROM article a LEFT JOIN OrderItems o ON a.anr = o.artnr
WHERE o.artnr IS NULL AND
    a.price = (SELECT MAX(a.price)
               FROM article a LEFT JOIN OrderItems o ON a.anr = o.artnr
               WHERE o.artnr IS NULL)

This solution should be ANSI-92 compliant, meaning it should play friendly with MySQL, Oracle, SQL Server, and any other type of fast food you might encounter.

Upvotes: 3

Related Questions