Modifying query (showing the latest row for each item)

Question

I'm trying to obtain the latest row for each item (the latest row of each item is identified with the request_id within the row)

These are my tables:

create table table_price_product 
(
    LIST_HEADER_ID int, 
    LIST_LINE_ID int,   
    ITEM_CODE int,  
    PRICE VARCHAR(32),  
    START_DATE_ACTIVE varchar(32),  
    END_DATE_ACTIVE varchar(32),    
    INSERT_UPDATE_FLAG varchar(5),  
    REQUEST_ID int, 
    LAST_UPDATE_DATE varchar(32)
);

create table table_price_list 
(
    LIST_HEADER_ID int, 
    NAME varchar(32),   
    CURRENCY_CODE varchar (32),
    REQUEST_ID int
);

This is my SQLFIDDLE

This is my query BUT i am not getting what i want:

SELECT man.LIST_HEADER_ID number,
       man.ITEM_CODE item_code,
       man.PRICE,
       man.START_DATE_ACTIVE,
       man.END_DATE_ACTIVE,
       man.REQUEST_ID
FROM table_price_product man,
     table_price_list pal
WHERE man.LIST_HEADER_ID = pal.LIST_HEADER_ID
  AND (man.START_DATE_ACTIVE IS NULL
OR man.END_DATE_ACTIVE IS NULL)
AND man.REQUEST_ID = pal.REQUEST_ID
ORDER BY man.LAST_UPDATE_DATE ASC;

This is what my query shows:

> CODE  |SKU     |PRICE  |START_DATE_ACTIVE|END_DATE_ACTIVE|REQUEST_ID
> 655234|99342435|9999   |       null      |null           |42937536
> 655234|99342435|8888   |       null      |null           |42937507
> 655234|99342435|7445   |       null      |null           |42937506
> 655234|99342435|5545   |       null      |null           |42937505
> 655234|99342435|2254   |       null      |null           |42937504
> 655234|89992424|18799  |       null      |null           |72254787
> 655234|89992424|18444  |       null      |null           |72254780
> 655234|89992424|18005  |       null      |null           |72254781
> 655234|89992424|18045  |       null      |null           |72254782
> 655234|91123323|1049   |       null      |null           |88755875
> 655234|91123323|1000   |       null      |null           |88755865
> 655234|91123323|9878   |       null      |null           |88755862
> 655234|91123323|10498  |       null      |null           |88755861
> 655234|91123323|1044   |       null      |null           |88755860

But i want to obtain this output (the highest request_id for each item):

CODE  |SKU     |PRICE  |START_DATE_ACTIVE|END_DATE_ACTIVE|REQUEST_ID
655234|99342435|9999   |       null      |null           |42937536
465408|89992424|18799  |       null      |null           |72254787
464062|91123323|1049   |       null      |null           |88755875

I've tried to solve this by using over within partition but it did not work for me

Can help me?

EDIT: i'm using Oracle database

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged — marc_s, May 22 '20 at 04:39
Also - please use the **most appropriate** datatypes for your columns - do **NOT** store date or numerical values like `price` as `Varchar`!! — marc_s, May 22 '20 at 04:39
Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — philipxy, Jun 24 '20 at 04:34

zealous · Accepted Answer · 2020-05-22T17:01:19.127

2

Try the following using window function row_number, Also you should always use explicit joins.

SELECT 
  man.LIST_HEADER_ID number,
  man.ITEM_CODE item_code,
  man.PRICE,
  man.START_DATE_ACTIVE,
  man.END_DATE_ACTIVE,
  man.REQUEST_ID
from
(
  SELECT 
    man.LIST_HEADER_ID number,
    man.ITEM_CODE item_code,
    man.PRICE,
    man.START_DATE_ACTIVE,
    man.END_DATE_ACTIVE,
    man.REQUEST_ID,
    row_number() over (partition by SKU order by request_id desc) as rnk
  FROM table_price_product man
  join table_price_list pal
  ON man.LIST_HEADER_ID = pal.LIST_HEADER_ID
  AND man.REQUEST_ID = pal.REQUEST_ID
  where (man.START_DATE_ACTIVE IS NULL
  OR man.END_DATE_ACTIVE IS NULL)
) val
where rnk = 1

edited May 22 '20 at 17:01

answered May 22 '20 at 04:39

zealous

7,336
4
16
36

Thank you for replying, dude!.I'm checking it out. I'm a little confused about "val". What it is actually doing? – JustToKnow May 22 '20 at 04:53
@Student_new `val` is just an alias to subquery. – zealous May 22 '20 at 04:55
Hey dude, i'm getting the same error over and over again: " FROM keyword not found where expected". What's going on? – JustToKnow May 22 '20 at 14:27
Hey dude, yeah.. the problem is now i'm geting this error: "Ambiguous column name error " – JustToKnow May 23 '20 at 02:46
@Student_new you have to solve it yourself, look around and you should get it why it is happening. – zealous May 23 '20 at 02:55
Done!, my bad :) – JustToKnow May 23 '20 at 03:39

Modifying query (showing the latest row for each item)

Can help me?

1 Answers1