Excluding tuples based on maximum condition

Question

I have been trying to answer to solve this SQL Query problem, but got no success. The problem is the following:

PROBLEM:

Given 4 tables, PRODUCTS, REPAIRS, OWNERS and MALFUNCTION, for each product Brand and Model display the type of malfunction which have been repaired more times.

The tables have the following fields:

PRODUCTS: *Series_num, Brand, Model, Year, Code_Owner
OWNERS: *Code_Owner, Name, Surname, Street, Civic, City, (u)Phone
MALFUNCTIONS: *Malf_code, Desc
REPAIRS: *Series_num, *Malf_code, *Repair_Date, Price

* <- Primary key
(u) <- Unique attribute

The expected result, given this example of data:

| MODEL  |  BRAND   |  MALF_CODE  | NUMBER OF REPAIRS|
|----------------------------------------------------|
|   1    |  BRAND1  |      1      |        20        |
|   1    |  BRAND1  |      2      |        10        |
|   2    |  BRAND1  |      1      |         1        |
|   2    |  BRAND1  |      2      |         1        |
|   1    |  BRAND2  |      1      |        10        |
|   1    |  BRAND2  |      2      |        11        |

Should be:

| MODEL  |  BRAND   |  MALF_CODE  | NUMBER OF REPAIRS|
|----------------------------------------------------|
|   1    |  BRAND1  |      1      |        20        |
|   2    |  BRAND1  |      1      |         1        |
|   1    |  BRAND2  |      2      |        11        |

Note that BRAND1, MODEL:2 has the same number of repairs for two different types of malfunction, so one of the rows can be ignored or both of them can be shown (it does not matter)

WHAT I'VE TRIED:

To get the first table, I used a simple JOIN query:

SELECT A.MODEL, A.BRAND, R.MALF_CODE, COUNT(*) AS N_REP
FROM REPAIRS R LEFT JOIN PRODUCTS A ON A.SERIES_NUM = R.SERIES_NUM
GROUP BY A.MODEL, A.BRAND, R.MALF_CODE;

Then I tried to get the second table thanks to MAX() function:

SELECT A.MODEL, A.BRAND, R.MALF_CODE, COUNT(*) AS N_REP
FROM REPAIRS R LEFT JOIN PRODUCTS A ON A.SERIES_NUM = R.SERIES_NUM
GROUP BY A.MODEL, A.BRAND, R.MALF_CODE
HAVING COUNT(*) IN(
    SELECT MAX(R.MALF_CODE)
    FROM REPAIRS R LEFT JOIN PRODUCTS A ON A.SERIES_NUM = R.SERIES_NUM
    GROUP BY A.MODEL, A.BRAND, R.MALF_CODE
    ORDER BY A.BRAND, R.MALF_CODE);

But this throws me the following error:

[42000][907] ORA-00907: Missing closing Parenthesis

It seems I can't find the error. I hope I've been clear enough. Thanks in advance.

EDIT: I forgot to mention that I'm aware of RANK functions and such, but never heard of Partitions. So a solution without them is highly appreciated but not mandatory.

score 2 · Answer 1 · answered May 24 '19 at 10:37

2

If I understand correctly, you want the row with the most repairs for each model/brand combination. If so, window functions are one method:

SELECT MODEL, BRAND, MALF_CODE, N_REP
FROM (SELECT P.MODEL, P.BRAND, R.MALF_CODE, COUNT(*) AS N_REP,
             ROW_NUMBER() OVER (PARTITION BY P.MODEL, P.BRAND ORDER BY COUNT(*) DESC, R.MALF_CODE) as SEQNUM
      FROM REPAIRS R LEFT JOIN
           PRODUCTS P
           ON P.SERIES_NUM = R.SERIES_NUM
      GROUP BY P.MODEL, P.BRAND, R.MALF_CODE
     ) MB
WHERE seqnum = 1;

answered May 24 '19 at 10:37

Gordon Linoff

1,242,037
58
646
786

Your example works perfectly. However I've not studied partition, so if you have another example in mind can you give me a tip on how to write the query? I do not want to bother you. I will read some documentation about partition however. – King Powa May 24 '19 at 10:43

Excluding tuples based on maximum condition

1 Answers1