Remove duplicates [message #689042] |
Thu, 31 August 2023 03:23 |
|
arun888
Messages: 100 Registered: June 2015 Location: INDIA
|
Senior Member |
|
|
Can you let me know how i can remove duplicates in the records.
w ith t
as (select list_code,
shop_no,
week,
week - row_number() over (order by list_code, shop_no, week) grp
from list.imp_shops
where list_code =
(
select list_code from list.list where list_name = 'mmm'
)
),
tst
as (select list_code,
shop_no,
min(week) week_start,
max(week) week_end
from t
group by list_code,
shop_no,
grp
)
select shop_NO as "CF shop",
b.sri_list_shop_NO as "shop",
WEEK_END as "Report Week" ,
((week_end - week_start) + 1) as "Copies",
(select WEEK_REASON from list.imp_shops c where c.shop_no = a.shop_no and week = 2295) "Latest "
from tst a JOIN euroscan b on a.shop_no = b.list_shop_no
where week_start <= 2295
and week_end = 2295 and b.list_file_abbrev =
(
select list_code from list.list where list_name = 'mmm'
)
order by shop_no,
list_code;
|
|
|
Re: Remove duplicates [message #689043 is a reply to message #689042] |
Thu, 31 August 2023 05:13 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Hm. If DISTINCT in final SELECT, here:
SELECT DISTINCT
shop_NO as "CF shop", ...
doesn't help, I wouldn't know.
Query you posted is too complex for me and I can't imagine what - out of all that - causes "duplicates". If you posted sample data that illustrates that problem, it would be easier (for me, at least) to assist.
|
|
|