simple query SQL-CodePudding

I'm stuck in this query. I need to print the laptop and the notebook with the maximum price (art_pv) from a table with products data(articles).

I can get the products data from the laptops and notebooks like this:

SELECT art_name, art_pv
FROM articles
WHERE art_name LIKE'%NOTEBOOK%' OR art_name LIKE '%LAPTOP%';

but i can't get only the most expensive laptop and notebook. Can you help me?

CodePudding user response：

max should help you:

SELECT art_name, art_pv
FROM articles
WHERE art_name LIKE'%NOTEBOOK%' OR art_name LIKE '%LAPTOP%'
and art_price = (select max(art_price) FROM articles
   WHERE art_name LIKE'%NOTEBOOK%' OR art_name LIKE '%LAPTOP%')

or if you need from both objects the the one who have the max price you can use union:

SELECT art_name, art_pv
FROM articles
WHERE art_name LIKE'%NOTEBOOK%' 
and art_price = (select max(art_price) FROM articles
   WHERE art_name LIKE'%NOTEBOOK%')

UNION
SELECT art_name, art_pv
FROM articles
WHERE  art_name LIKE '%LAPTOP%'
and art_price = (select max(art_price) FROM articles
   WHERE  art_name LIKE '%LAPTOP%')

CodePudding user response：

Aggregate by the maximum of each group:

SELECT art_name, Max(art_pv) MaxPrice
FROM articles
WHERE art_name LIKE'%NOTEBOOK%' OR art_name LIKE '%LAPTOP%'
group by art_name;

CodePudding user response：

There are a few ways this could be handled, but I believe a simple UNION could be a solution:

SQL Server

SELECT TOP 1 art_name, art_pv
FROM articles
WHERE art_name LIKE '%NOTEBOOK%'
ORDER BY art_pv DESC

UNION

SELECT TOP 1 art_name, art_pv
FROM articles
WHERE art_name art_name LIKE '%LAPTOP%'
ORDER BY art_pv DESC