ROW_NUMBER() OVER (PARTITION BY A,B,C ordered by x)

[muratakkemik]

Hi All,

I have Sql query but i need to convert it to QV script. This (ROW_NUMBER() OVER (PARTITION BY A,B,C ordered by x)) function is not working in QV.

Here is my sql script:

; with cte as

(

select ah.artikelid, ae.eankod, s.EanColor, f.EanSize, ae.EanDate,

ROW_NUMBER() OVER (PARTITION BY ah.artikelid, s.EanColor, f.EanSize, ,ae.eankod,

ORDER BY ae.EanDate DESC) AS RN

from artikelhuvud ah inner join artikelean ae on ah.artikellopnr = ae.artikellopnr

inner join  farg f on ae.fargid = f.fargid inner join storlek s on s.storleksid = ae.storleksid

--where ah.artikelid = 223221

order by ah.artikelid,  s.EanColor, f.EanSize, ae.eankod

)

select * from cte

where RN=1

This is my QV script which needs to change;

EanCode:
load ARTIKELLOPNR, ARTIKELID,  EANColor,  EANSize, EANKOD, EANdate
resident EanCodeT
order by ARTIKELLOPNR, ARTIKELID,  EANColor,  EANSize, EANdate DESC;

Drop table EanCodeT;

Data like this:

ARTIKELLOPNR, ARTIKELID,  EANColor,  EANSize, EANKOD, EANdate,     RN   (This was coming with sql but need to be done in qv)

111                         2222               Black          L               12345     10-31-2018          1

111                         2222               Black          L                23232     10-20-2018          2

111                         2222               White          L                45454     10-25-2018          1

111                         2222               White          L                55585     10-20-2018          2

111                         2222               White          M               97474     09-21-2018          1

111                         2222               White          M               58585     09-21-2018          2

nearly Every item, color, size have 2 Ean codes I want to sort them date base and use their last one

Thank you

M

[andrey_krylov]

Hi Murat, try AutoNumber(RowNo()

EanCode:
load ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANKOD, EANdate

,AutoNumber(RowNo(), ARTIKELLOPNR & ARTIKELID & EANColor & EANSize) as RN
resident EanCodeT
order by ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANdate DESC;

[muratakkemik]

Thank you for your reply, this solution (AutoNumber) is working so slow. I have more than 2m records and it takes nearly 3 hours. is there any another way to do that?

thx

[vishsaggi]

Are you running Andrey's script from SQL or using QVD? Like i mean run you sql script and store it in your QVD.

Then load your qvd and then use Autonumber Andrey suggested. Check that way and let us know.

[muratakkemik]

Im using QVD file

as you see it took already 30min for half, and still working

[vishsaggi]

Can you share the script you are running for EanCodeT table? Are you using Resident or straight from Qvd?

[muratakkemik]

First of all taking data from QVD into cash then using that script in resident and still it is working

[andrey_krylov · Accepted solution]

Another option

EanCode:
load ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANKOD, EANdate

,If(Previous(ARTIKELLOPNR) = ARTIKELLOPNR and Previous(ARTIKELID) = ARTIKELID and Previous(EANColor) = EANColor and Previous(EANSize) = EANSize, Peek(RN) + 1, 1) as RN
resident EanCodeT
order by ARTIKELLOPNR, ARTIKELID, EANColor, EANSize, EANdate DESC;

but I'm not sure it will be faster although if you try and share the result  it might be interesting.

[muratakkemik]

Perfect!

it works in 10 sec

Thank you

[andrey_krylov]

Unexpectedly! But I'm glad it helped))