CODEWITHSUNDEEP
sqldatabaseoracle-databasejoinselect
Ponzaro
Ponzaro

Reputation: 149

Oracle - Selecting only the maximum value from a column

the structure of my DB is roughly this (simplified) :

Three tables: Order, step, material

The table "order" contains the columns ID and material

The table "step" contains the column ID, order and stepnumber

The table "material" contains the column ID and description

            Order                         
    --------------------------------    
     **ID** **Number**     **Material**    
       1         X1            11
       2         X2            12
       3         X3            13


              Step
   ---------------------------------------
   **ID**    **Order**  **Stepnumber***
     1          X1           X110
     2          X1           X120
     3          X1           X170
     4          X1           X180
     5          X2           X270
     6          X2           X280

                 Material
   ---------------------------------------
  **ID**      **Description***
    11             Mat1
    12             Mat2
    13             Mat3

The thing to notice is that the column stepnumberof the table is formed by the order number (ex. X1) and a number that identifies a step (10, 20, 70, 80, etc.), and as you can see there are multiple values of the stepnumber column that refers to the same order.

I'm using this query:

select order.number,step.stepnumber, material.id,material.description, 
from db.order inner join db.stepnumber
on order.number = step.order
inner join db.material
on material.id = order.material
where step.stepnumber not like '%10'
and step.stepnumber not like '%20'
group by order.number, step.stepnumber, material.id, material.description

As you can see from the query, I want to extract order and step data, and I'm excluding some step based on the stepnumber value.

Right now, the query output is:

order.number step.stepnumber material.id material.description 
-------------------------------------------------------------
  X1             X170             11           Mat1
  X1             X180             11           Mat1
  X2             X270             12           Mat2
  X2             X280             12           Mat2

I want to achieve that for every order only one row is extracted, taking the one in which the stepnumber value is higher. So the results should be:

order.number step.stepnumber material.id material.description 
-------------------------------------------------------------
  X1             X180             11           Mat1
  X2             X280             12           Mat2

I've tried putting the MAXfunction to step.stepnumber in the select with no effect.

Upvotes: 1

Views: 61

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use ROW_NUMBER window function

CREATE TABLE "Order"(
   ID int,
   "Number" varchar(50),
   Material int
);


INSERT INTO  "Order" VALUES (1,'X1',11);
INSERT INTO  "Order" VALUES (2,'X2',12);
INSERT INTO  "Order" VALUES (3,'X3',13);

CREATE TABLE Step(
   ID int,
   "Order" varchar(50),
   Stepnumber varchar(50)
);


insert into Step values (1,'X1','X110');
insert into Step values (2,'X1','X120');
insert into Step values (3,'X1','X170');
insert into Step values (4,'X1','X180');
insert into Step values (5,'X2','X270');
insert into Step values (6,'X2','X280');

CREATE TABLE Material(
   ID int,
   Description varchar(50)
);               


INSERT INTO Material VALUES (11,'Mat1');
INSERT INTO Material VALUES (12,'Mat2');
INSERT INTO Material VALUES (13,'Mat3');

Query 1:

SELECT "Number",STEPNUMBER,ID,DESCRIPTION 
FROM (
  select o."Number",
           s.stepnumber, 
           m.id,
           m.description,
           ROW_NUMBER() OVER(PARTITION BY o."Number" ORDER BY s.stepnumber DESC) rn
    from db."Order" o
    inner join db.Step s on o."Number" = s."Order"
    inner join db.material m on m.id = o.material
    where 
        s.stepnumber not like '%10'
    and 
        s.stepnumber not like '%20'
) t1
WHERE rn = 1

Results:

| Number | STEPNUMBER | ID | DESCRIPTION |
|--------|------------|----|-------------|
|     X1 |       X180 | 11 |        Mat1 |
|     X2 |       X280 | 12 |        Mat2 |

NOTE

We might try to avoid to use Order, Number be columns name or table name ... because they are keywords in Oracle.

Upvotes: 1

Related Questions