CODEWITHSUNDEEP
sql
Nona
Nona

Reputation: 5462

How do I use row_number and case statement for this SQL exercise?

I'm doing an sql exercise and am stuck. If I have database schema as in Computer Firm http://sql-ex.ru/help/select13.php#db_1 and the problem is described as:

Renumber the unique pairs {maker, type} from Product table, having ordered them as follows:
- A name of the manufacturer (maker) in ascending;
- Type of a product (type) in the order: PC, Laptop, Printer.
If a manufacturer produces more than one type of product, deduce its name only in the first row;
other rows for THIS manufacturer should contain an empty string ('').

My query:

select 
    row_number() over (ORDER BY 
        maker ASC, 
        CASE type when 'PC' THEN 1 WHEN 'Laptop' THEN 2 WHEN 'Printer' THEN 3 END
    ) num, 
    maker, type 
from product 
order by maker, CASE type when 'PC' THEN 1 WHEN 'Laptop' THEN 2 WHEN 'Printer' THEN 3 END

My (incorrect) answer:

+-----+-------+---------+
| num | maker |  type   |
+-----+-------+---------+
|   1 | A     | PC      |
|  10 | C     | Laptop  |
|  11 | D     | Printer |
|  12 | D     | Printer |
|  13 | E     | PC      |
|  14 | E     | PC      |
|  15 | E     | PC      |
|  16 | E     | Printer |
|   2 | A     | PC      |
|   3 | A     | Laptop  |
|   4 | A     | Laptop  |
|   5 | A     | Printer |
|   6 | A     | Printer |
|   7 | A     | Printer |
|   8 | B     | PC      |
|   9 | B     | Laptop  |
+-----+-------+---------+

The correct answer looks like:

+-----+-------+---------+
| num | maker |  type   |
+-----+-------+---------+
|   1 | A     | PC      |
|   2 |       | Laptop  |
|   3 |       | Printer |
|   4 | B     | PC      |
|   5 |       | Laptop  |
|   6 | C     | Laptop  |
|   7 | D     | Printer |
|   8 | E     | PC      |
|   9 |       | Printer |
+-----+-------+---------+

EDIT

This is what the site says it uses in terms of SQL support: Now we use Microsoft SQL Server 2012 on the rating stages, and MySQL 5.5.11, PostgreSQL 9.0, and Oracle Database 11g on the learn stage in addition.. This question comes from the learning stage.

Upvotes: 1

Views: 10790

Answers (2)

Rahul Jain
Rahul Jain

Reputation: 1399

I was able to build a query for this:

Select 
row_number() OVER(
ORDER BY maker,
CASE    WHEN type = 'PC' THEN '1'
        WHEN type = 'Laptop' THEN '2'
        WHEN type = 'Printer' THEN '3'
        ELSE type  END ASC) num,
CASE WHEN 
row_number() OVER(
PARTITION BY maker
ORDER BY CASE   WHEN type = 'PC' THEN '1'
                WHEN type = 'Laptop' THEN '2'
                WHEN type = 'Printer' THEN '3'
                ELSE type  END ASC) =1
                THEN maker
                ELSE '' END as maker,
type 
FROM
(select distinct maker,type from product) as p

Upvotes: 2

Darshan Mehta
Darshan Mehta

Reputation: 30809

Below query would give the expected output for MySql:

select rank, manufacturer, type 
from (
  select @r:=@r+1 AS rank,
  if( @lastmaker = maker, '', maker ) as manufacturer, 
  type, @lastmaker := maker
  from product, (SELECT @r:=0, @lastmaker:='') r
  order by maker, type
) mytable;

Here is SQL Fiddle for example.

Upvotes: 1

Related Questions