Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    2

    Unanswered: "ROW_NUMBER() OVER (PARTITION BY)" problem

    Heya all, First post on the forum.

    I have a dataset of about 1200 records looking something like this table (Data);

    ID; SSC; CSD SL Name; Tnx
    1; Allandale (Qld); BEAUDESERT QGAP; 266
    2; Allandale (Qld); BOONAH POLICE STATION; 63
    3; Allandale (Qld); IPSWICH CSC; 20
    4; Allandale (Qld); BURLEIGH WATERS CSC; 11
    5; Aratula (Qld); BEAUDESERT QGAP; 902
    6; Aratula (Qld); IPSWICH CSC; 559
    7; Aratula (Qld); BOONAH POLICE STATION; 435
    8; Aratula (Qld); BEAUDESERT QGAP; 146
    9; Aratula (Qld); BEAUDESERT QGAP; 135
    10; Aratula (Qld); WARWICK CSC; 104

    I have code as follows;
    Code:
    SELECT [Data].[SSC], [Data].[CSD SL Name], [Data].[Tnx], rn
    FROM 
      ( SELECT [Data].[SSC], [Data].[CSD SL Name], [Data].[Tnx],
      ROW_NUMBER() OVER (PARTITION BY  [Data].[SSC]
                                  ORDER BY [Data].[Tnx] DESC
                                 ) as rn
      FROM [Data]  
      ) tmp 
    WHERE rn <= 3
    ORDER BY [Data].[SSC], rn;
    which i believe is NOT compatible with MS Access querying.

    so two problems:
    1) Is there a way of coding this for Access to output
    1; Allandale (Qld); BEAUDESERT QGAP; 266
    2; Allandale (Qld); BOONAH POLICE STATION; 63
    3; Allandale (Qld); IPSWICH CSC; 20
    5; Aratula (Qld); BEAUDESERT QGAP; 902
    6; Aratula (Qld); IPSWICH CSC; 559
    7; Aratula (Qld); BOONAH POLICE STATION; 435


    2) I'm a bit of a noob so often fail at grouping.
    The "Data" table is output from an earlier query.
    Ideally the Data table would be better aggregated so that records 5-8 were actually like this;
    5; Aratula (Qld); BEAUDESERT QGAP; 1183
    6; Aratula (Qld); IPSWICH CSC; 559
    7; Aratula (Qld); BOONAH POLICE STATION; 435
    8; Aratula (Qld); WARWICK CSC; 104
    Is there a way of aggregating the data as a part of this new query?

    thanks,
    J

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know, ROW_NUMBER() OVER (PARTITION BY... can be used in T-SQL (SQL Server), but not in Access.
    Have a nice day!

  3. #3
    Join Date
    Jun 2014
    Posts
    2

    thanks Sinndho

    thanks for the clarification that indeed MS Access doesn't support this syntax, so keen to identify an MS Access-friendly method.

    i've grouped the former query (had an order by which was ungrouping the output) so no worries on that part of my request for info.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    Select id, ssc, [csd sl name], sum(tnx) from data
    group by id, ssc,[csd sl name]
    Having rn < 3
    should be something like
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •