The 'min' and 'max' does not work with other information

Question

I am trying to work out the min and max price on a table of items:

item [itemcode] [itemname] [iteminfo] [price]

I want to get not only the min and max price but also what the item is: so query result needs to be cheapest item, itemname item info, then want to change the syntax for the max version of it. Most expensive item, itemname and iteminfo etc select min(price) from item

This works fine. Returns the min same with max. But, when I try to get item information, it returns all rows of the table not just the single row where its cheapest or most expensive. For example:

select min(price) as lowest, itemcode, itemname, iteminfo
from item
group by itemname

I'm not sure, what I'm doing wrong? Can anyone help me?

Answer 1

If you need to display the row with a mininum price for a particular itemname kindly use the below

with item1 as
(select price, itemcode, itemname, iteminfo
from item
), item2 as(select min(price) as lowest,itemname from item1
group by itemname)

select lowest,itemcode,item1.itemname,iteminfo
from item1,item2
where item1.itemname=item2.itemname 
and item1.price=item2.lowest

Answer 2

try This ...

select itemcode, itemname, iteminfo, price
from item
where item.price in (
    select min(price) from item
    union
    select max(price) from item)

if you want the results separate, it will be... lowest

select itemcode, itemname, iteminfo, price
from item
where item.price = (
    select min(price) from item)

highest

select itemcode, itemname, iteminfo, price
from item
where item.price = (
    select max(price) from item)

Note that this will return all the items with the lowest/highest price... If more than one item cost $1, both will be returned

Answer 3

Maybe this will help (it will always return just one item, even if there are other items with the same price):

SELECT itemcode,
       itemname,
       iteminfo,
       price
  FROM(SELECT itemcode,
              itemname,
              iteminfo,
              price,
              ROW_NUMBER() OVER (ORDER BY price) AS rn
         FROM item
      )
 WHERE rn = 1;

If you want all items with the lowest price, you can replace ROW_NUMBER() OVER (ORDER BY price) AS rn with DENSE_RANK() OVER (ORDER BY price) AS rn.

If you want the item with the maximum price, just change the ORDER BY price into ORDER BY price DESC.

Answer 4

Simplest solution for minimum:

select price as lowest, itemcode, itemname, iteminfo
from item 
where price = (select min(price) price from item)

The equivalent will do also for maximum.

There are various ways of calculating min and max, and if you want to do get both in one query you can use analytics. Here I've used the RANK() function because it correctly handles the scenario where more than one item has the lowest or highest price.

select case when maxp = 1 then 'HIGHEST' else 'LOWEST' end as type
       , price
       , itemcode
       , itemname
       , iteminfo
from (
       select price
              , itemcode
              , itemname
              , iteminfo
              , rank() over (order by price desc) maxp 
              , rank() over (order by price asc) minp 
       from item
      )
where maxp = 1
or    minp = 1

The analytic solution has the advantage of only reading the table once. This efficiency is valuable for large tables, especially when the column we're using is not indexed. So in real life I would always go the analytical version rather than the simpler version I lead with.

Analytic functions are extremely powerful. Find out more.