Oracle SQL Query - Help Required

Question

For the below Table structure, I am not able to build the required output, not sure if I need to apply transpose, of which I don't have a strong knowledge. SQL script for table creation and data insertion is given towards the end.

Output should be in below format as shown above and ID_MAX_VAL of output result should be the max of ID for each manufacturer-country combination in main source table. I need to fetch the max id for each manufacturer-country combination and display/use them to send out a report.

Output:

MANUFACTURER    COUNTRY     ID_MAX_VAL
--------------------------------------
NISSAN          USA         10
NISSAN          UK          30
HONDA           USA         80
HONDA           UK          70

Note: This is a test data and table structure to simulate the actual business requirement.

A view similar to the table I have mentioned is the only thing that we have access to and that works as our sole source. Have to work with that only.

SQL Script:

CREATE TABLE TB_TEST_01 
(
  ID NUMBER(6) NOT NULL 
, PARAM_NM VARCHAR2(200) NOT NULL 
, PARAM_VAL VARCHAR2(200) 
);
/

INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (10, 'MANUFACTURER', 'NISSAN');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (10, 'COUNTRY', 'USA');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (30, 'MANUFACTURER', 'NISSAN');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (30, 'COUNTRY', 'UK');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (20, 'MANUFACTURER', 'NISSAN');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (20, 'COUNTRY', 'UK');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (50, 'MANUFACTURER', 'HONDA');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (50, 'COUNTRY', 'USA');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (60, 'MANUFACTURER', 'HONDA');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (60, 'COUNTRY', 'USA');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (80, 'MANUFACTURER', 'HONDA');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (80, 'COUNTRY', 'USA');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (70, 'MANUFACTURER', 'HONDA');
INSERT INTO TB_TEST_01 (ID, PARAM_NM, PARAM_VAL) VALUES (70, 'COUNTRY', 'UK')
;
/
COMMIT;

Don't use `;` **and** `/` for regular DDL or DML statements: http://stackoverflow.com/questions/1079949/sql-the-semicolon-or-the-slash/10207695#10207695 — , Jun 11 '14 at 05:51

score 2 · Answer 1 · answered Jun 11 '14 at 04:55

2

You can use PIVOT function also:

select M_PARAM_VAL MANUFACTURER, C_PARAM_VAL COUNTRY, max(ID) ID_MAX_VAL
  from TB_TEST_01
 pivot (min(PARAM_VAL) as PARAM_VAL for (PARAM_NM) in ('MANUFACTURER' as M, 'COUNTRY' as C))
group by M_PARAM_VAL, C_PARAM_VAL;

Output:

| MANUFACTURER | COUNTRY | ID_MAX_VAL |
|--------------|---------|------------|
|        HONDA |      UK |         70 |
|       NISSAN |     USA |         10 |
|       NISSAN |      UK |         30 |
|        HONDA |     USA |         80 |

answered Jun 11 '14 at 04:55

San

4,508
1
13
19

Thanks San! Worked like a charm.. PIVOT is nice.. Still need to understand it further.. I'll go through the Oracle docs.. :) – NiCKz Jun 11 '14 at 14:11
Using the above PIVOT query, how do I derive the max ID for let's say Honda USA? I have never used a PIVOT before, so quite confused. My real table has lots of data in it... so need to make sure I choose the best query for performance. – NiCKz Jun 12 '14 at 02:21
`PIVOT` function works with aggregate functions, `min(PARAM_VAL)` is used just to satisfy this condition. `PIVOT` converted data from row to column or you can say transposed and then the task becomes easier to find the max of `ID`. Find out more by removing `max(ID) ID_MAX_VAL` from select list and `GROUP BY` clause. – San Jun 12 '14 at 05:27

score 1 · Accepted Answer · answered Jun 11 '14 at 04:30

1

In case of not a normalized design, we can self join the table, like one below.

SELECT T1.PARAM_VAL AS MANUFACTURER,
       T2.PARAM_VAL AS COUNTRY,
       MAX(T1.ID) AS MAX_ID_VAL
FROM TB_TEST_01 T1,TB_TEST_01 T2
WHERE
       T1.ID = T2.ID
   AND T1.PARAM_NM='MANUFACTURER'
   AND T2.PARAM_NM='COUNTRY'
GROUP BY
   T1.PARAM_VAL,T2.PARAM_VAL

answered Jun 11 '14 at 04:30

Maheswaran Ravisankar

17,652
6
47
69

For this test table and data, it works fine. But, when I want to find out the maximum ID for a particular manufacturer-country combination, it takes a lot of time, considering my real table has lots of data in it. Any way to tune it further? Let's say I just want to derive the max ID for Honda USA. – NiCKz Jun 12 '14 at 02:23
partition the table by param_nm if possible or worst case index it(in order by paranam name , param value), tht itself should give you a lot of improvement. when oracle is able to filter out the manufacturers (the condition actually), the other operations should go smooth. And I would like to see the explain plan actually. – Maheswaran Ravisankar Jun 12 '14 at 08:21

score 0 · Answer 3 · answered Jun 11 '14 at 04:19

Try this

CREATE TABLE TB_TEST_01 
(
  ID NUMBER(6) NOT NULL 
, MANUFACTURER VARCHAR2(200) NOT NULL 
, COUNTRY VARCHAR2(200) 
);
/

INSERT INTO TB_TEST_01 (ID, MANUFACTURER, COUNTRY) VALUES (10, 'NISSAN', 'USA');
INSERT INTO TB_TEST_01 (ID, MANUFACTURER, COUNTRY) VALUES (20, 'NISSAN', 'UK');
INSERT INTO TB_TEST_01 (ID, MANUFACTURER, COUNTRY) VALUES (30, 'NISSAN', 'UK');
INSERT INTO TB_TEST_01 (ID, MANUFACTURER, COUNTRY) VALUES (50, 'HONDA', 'USA');
INSERT INTO TB_TEST_01 (ID, MANUFACTURER, COUNTRY) VALUES (60, 'HONDA', 'USA');
INSERT INTO TB_TEST_01 (ID, MANUFACTURER, COUNTRY) VALUES (70, 'HONDA', 'UK');
INSERT INTO TB_TEST_01 (ID, MANUFACTURER, COUNTRY) VALUES (80, 'HONDA', 'USA');

COMMIT;
/

and for out put

SELECT manufacturer, country,MAX(ID) id_max FROM TB_TEST_01
GROUP BY manufacturer,country;

Thanks Kitezzz, but I don't have an option to change the table structure. That is the view I must use, and the solution is required out of that. — NiCKz, Jun 11 '14 at 04:27

score 0 · Answer 4 · answered Jun 11 '14 at 14:09

@San @OracleUser Thanks all for the help and with different types of solutions.. PIVOT, SELF JOIN.. :) (I'm new to PIVOT, still need to understand how it works)

I used it like this to get the details of a particular manufacturer:

SELECT T1.PARAM_VAL AS MANUFACTURER,
       T2.PARAM_VAL AS COUNTRY,
       MAX(T1.ID) AS MAX_ID_VAL
FROM TB_TEST_01 T1,TB_TEST_01 T2
WHERE
       T1.ID = T2.ID
   AND T1.PARAM_NM='MANUFACTURER'
   AND T2.PARAM_NM='COUNTRY'
   AND T1.PARAM_VAL='HONDA'
GROUP BY
   T1.PARAM_VAL,T2.PARAM_VAL;

Oracle SQL Query - Help Required

4 Answers4