Retrieve the maximum number for each group of records oracle DB

Question

I failed to write SQL code to retrieve maximum number in order_number column. notice that visit_number is group contain more than one record and i want the maximum value of order_number column.

visit_number , Order_number , NAME
111          ,     1        , 001
111          ,     2        , 001
111          ,     3        , 001
222          ,     1        , 252
222          ,     2        , 252 
003          ,     1        , 121
003          ,     2        , 121

I want the result to be like below matrix:

111          ,     3        , 001
222          ,     2        , 252 
003          ,     2        , 121

this is my query

SELECT VISIT_NUMBER , MAX(ORDER_NUMBER) , NAME
from table
group by ( visit_number , name )

Can you show what you have already tried? – McVenco Dec 14 '15 at 14:42 — McVenco, Dec 14 '15 at 14:42
Does a visit_number always have the same name value? – jarlh Dec 14 '15 at 14:43 — jarlh, Dec 14 '15 at 14:43
Doesn't that query return what you expect? – jarlh Dec 14 '15 at 14:48 — jarlh, Dec 14 '15 at 14:48

score 0 · Answer 1 · answered Dec 14 '15 at 14:47

0

Use GROUP BY and the MAX function.

SELECT visit_number, MAX(Order_number), NAME
FROM yourtable
GROUP BY visit_number, NAME

answered Dec 14 '15 at 14:47

Matt

14,906
27
99
149

if the name column changeable does that effect? – Nawaf Dec 14 '15 at 14:51
@Nawaf, update your sample data with different names, and also adjust the expected result! – jarlh Dec 14 '15 at 14:52
this query not works it is return the same value either group by function is written or not. – Nawaf Dec 15 '15 at 06:37

score 0 · Answer 2 · answered Dec 14 '15 at 14:53

Join the table with a sub-query that returns each visit_number together with its max order_number:

SELECT distinct t1.*
FROM tablename t1
  JOIN (select visit_number, MAX(Order_number) as max_Order_number
        from tablename group by visit_number) as t2
  ON t1.visit_number = t2.visit_number and t1.Order_number = t2.max_Order_number

(Will return all rows if several different Names for a maximum order number.)

score 0 · Answer 3 · answered Dec 14 '15 at 15:39

select VISIT_NUMBER, ORDER_NUMBER, NAME
from (
select VISIT_NUMBER, ORDER_NUMBER, NAME, row_number() over(partition by VISIT_NUMBER order by ORDER_NUMBER desc) as ORDER_DESC
from TABLE
)
where ORDER_DESC = 1

This will return the max for each VISIT_NUMBER, even if the NAME changes

Retrieve the maximum number for each group of records oracle DB

3 Answers3