Access query counter per group-CodePudding

Is it possible to add a sort per group (Sort/Group) field in an Access Query to have sort by value as per below table:

Note: ID is unique index (Auto Number)

CodePudding user response：

Consider:

SELECT Data.ID, Data.Group1, Data.Value, 
DCount("*","Data","Group1='" & [Group1] & "' AND Value<" & [Value]) 1 AS GrpSeq
FROM Data
ORDER BY Data.Value;

SELECT Data.ID, Data.Group1, Data.Value, (
       SELECT Count(*) AS Cnt FROM Data AS Dupe 
       WHERE Dupe.Value<Data.Value AND Dupe.Group1=Data.Group1) 1 AS GrpSeq
FROM Data
ORDER BY Data.Value;

CodePudding user response：

On a real RDBMS, one would typically use the window function ROW_NUMBER for this.

select *
, row_number() over (partition by Group1 order by Value, ID) as Rownum
from yourtable

But an alternative is to use a correlated subquery.

select *, 
(select count(*) from yourtable t2 
 where t2.Group1 = t.Group1
   and (t2.Value < t.Value
        or (t2.Value = t.Value and t2.ID <= t.ID)) ) as Rownum
from yourtable t
order by Group1, Rownum