Organizing top costed products in SQL

Question

I just starting putting together a product database for my company of all the products we sell..

CREATE TABLE Product
(
ProductId BIGINT,
Name VARCHAR(50),
Category VARCHAR(50),
RetailPrice DECIMAL(9,2)
);

I'm trying to make a list of each product category and our most expensive product with this code

SELECT MAX(Product.RetailPrice) AS Expensive
SELECT COUNT(DISTINCT Product.Category) AS Categories FROM Expensive;

The terminal is only telling me that I have a syntax error.

Can you show some sample data and the desired result? It's not clear from your question. Do you want the most expensive product in each category, or the most expensive product of all? — Barmar, Jun 03 '16 at 21:14
If you want the most expensive product in each category, see http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 — Barmar, Jun 03 '16 at 21:15

score 0 · Answer 1 · answered Jun 03 '16 at 20:57

0

Just one select

 SELECT MAX(Product.RetailPrice) AS Expensive, 
  COUNT(DISTINCT Product.Category) AS Categories FROM Expensive;

answered Jun 03 '16 at 20:57

ScaisEdge

131,976
10
91
107

score 0 · Answer 2 · answered Jun 03 '16 at 21:03

Your queries don't have much to do with the question. If this is your question:

I'm trying to make a list of each product category and our most expensive product with this code

Then you can answer it in several ways. Here is one way that uses variables:

select p.*
from (select p.*,
             (@rn := if(@c = p.category, @rn + 1,
                        if(@c := p.category, 1, 1)
                       )
             ) as rn
      from product p cross join
           (select @c := '', @rn := 0) params
      order by p.category, retailprice desc
     ) p
where seqnum = 1;

Organizing top costed products in SQL

2 Answers2