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?

If using Oracle, your 'for example' query will involve ORA-00979 for sure. It cannot 'return all rows', it cannot return anything by design. So please show your true select and some of it's results. — Sanders the Softwarer, Dec 17 '14 at 07:48
using oracle the query works too, but it returns all the lines of the table, if i run it to get just the lowest price and max price, i get that fine, but when i want to know what the items are, thats where its not working, i could order the table like mention Dirk said but trying to get the min price of item and its information just that one line of data, i can get min price alone fine, but if i want the rest of the line it doesnt work — Admiral Diggs, Dec 17 '14 at 08:16

DirkNM · Answer 1 · 2014-12-17T09:01:01.470

1

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.

edited Dec 17 '14 at 09:01

answered Dec 17 '14 at 07:54

DirkNM

2,614
15
21

ROW_NUMBER() won't return the correct result when there is more than one item with the lowest or highest price. – APC Dec 17 '14 at 08:14
You're right. It's not really clear, if the OP want just one item (even if there are more than one with the same price) or if he wants all items with the lowest (highest) price. If so, replacing `ROW_NUMER()` with `DENSE_RANK()` should do it. – DirkNM Dec 17 '14 at 08:59

Spock · Answer 2 · 2014-12-17T08:25:59.483

0

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

edited Dec 17 '14 at 08:25

answered Dec 17 '14 at 07:48

Spock

4,700
2
16
21

still returns all rows not just the min(price row) – Admiral Diggs Dec 17 '14 at 08:00
Sorry. I miss read the question... I've updated my answer – Spock Dec 17 '14 at 08:20

APC · Answer 3 · 2014-12-17T08:12:53.167

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.

@beny23 The OP want just the item with the lowest (or highest) price — DirkNM, Dec 17 '14 at 07:59

psaraj12 · Answer 4 · 2014-12-17T11:55:05.823

0

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

edited Dec 17 '14 at 11:55

answered Dec 17 '14 at 10:42

psaraj12

4,772
2
21
30

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

4 Answers4