ROW_NUMBER() OVER(PARTITION...

[khsaunderson]

I am trying to write a bit of SQL in Access where I get a sequence dependent upon a field, eg

ROCD CACD SEQ
0001 0001 001
0001 0002 002
0002 0001 001
0003 0001 001
etc

I've tried writing a row over partition, but I keep on getting an error saying "Syntax error (missing operator) in query expression 'xxx'."

The code I'm using is:

SELECT DISTINCT ROCD, 
CACD,
ROW_NUMBER() OVER(PARTITION BY CACD ORDER BY CACD) AS SEQ
FROM PACK

I assume that Access can't handle row_number queries, but is there some other function I could use instead? I don't want the numbers to increment all the way through - only where there are more that 1 record per ROCD.

Thanks

 

[r937]

SELECT pack.rocd
     , pack.cacd
     , COUNT(pack2.rocd)+1 AS seq
  FROM pack
LEFT OUTER
  JOIN pack AS pack2
    ON pack2.rocd = pack.rocd
   AND pack2.cacd < pack.cacd
ORDER
    BY pack.rocd
     , pack.cacd

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[khsaunderson]

Thanks for this. I had to make a small change, by adding a grouping to the end of the function:

SELECT PACK.PAROCD, PACK.PACACD, COUNT(PACK2.PAROCD)+1 AS SEQ
FROM PACK
LEFT OUTER JOIN PACK AS PACK2 ON PACK2.PAROCD = PACK.PAROCD
								AND PACK2.PACACD < PACK.PACACD
GROUP BY PACK.PAROCD, PACK.PACACD
ORDER BY PACK.PAROCD, PACK.PACACD

The sequence works fine for some examples in my table, but not others. In the example below, it gives me seq 1 for the first CACD, then 4 for the next one, because there are actually 3 lines with the same CACD.

RACD CACD SEQ
A001 0724354268526 001
A001 5099951079727 004
A001 5099951079727 004
A001 5099951079727 004

I would want it to give a sequence of 001 and 002, ie

RACD CACD SEQ
A001 0724354268526 001
A001 5099951079727 002
A001 5099951079727 002
A001 5099951079727 002

I've tried tweaking the code a bit with groupings and distincts, but can't seem to get it.

 

[r937]

omg omg omg i wrote ORDER BY instead of GROUP BY, sorry!!

actually i totally messed it up

please try this instead --

SELECT pack.rocd
     , pack.cacd
     , ( SELECT COUNT(*)
           FROM pack AS pack2    
          WHERE pack2.rocd = pack.rocd 
            AND pack2.cacd < pack.cacd ) + 1 AS seq
  FROM pack
ORDER
    BY pack.rocd
     , pack.cacd

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[khsaunderson]

Hi

Yes, that worked like a charm. Thanks so much

Kate

 

[lespaul]

you should just give up rudy! OMG what a mistake! LOL

Les

 

[r937]

there is a difference between giving up and knowing when to step away

favre hasn't stepped away (although it can't be far off), but he will ~never~ give up



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon