0

I'm using Oracle SQL and i need some help with max() function.

I have the following table:

ID  | Type | Price | Quantity
 1  |  A   |  10   |    2
 2  |  B   |  5    |    5
 3  |  C   |  10   |    3
 4  |  A   |  8    |    7
 5  |  A   |  6    |    9
 6  |  A   |  7    |    5
 7  |  B   |  15   |    3
 8  |  A   |  20   |    4
 9  |  A   |  3    |    7
 10 |  B   |  11   |    8

I need to aggregate the table by Type column. For each group of Type (A, B, C), i need to select the price and the quantity of max(id).

I this case:

 ID  | Type | Price | Quantity
  9  |  A   |  3    |    7
  10 |  B   |  11   |    8
  3  |  C   |  10   |    3

Any Suggestion?

Omri
  • 1,436
  • 7
  • 31
  • 61

2 Answers2

1

max won't help you with this. You can use the row_number partitioning function.

select id, type, price, quantity
from
(
select yourtable.*,
    row_number() over (partition by type order by id desc) rn
from yourtable
) v
where rn = 1
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • The subquery has a problem: FROM keyword not found where expected – Omri Feb 08 '15 at 10:24
  • @Omri: you need to "qualify" the `*` in Oracle: `select yourtable.*, row_number() ...` –  Feb 08 '15 at 10:48
0

Something like this:

Select t.* From 
(Select Max(ID) As ID From Table
 Group By Type) tmp
Join Table t On t.ID = tmp.ID
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75