Lex de Willigen Lex de Willigen - 25 days ago 9
SQL Question

How to add Row Numbers and GROUP BY in Access?

I have a table like this:

Tabel1

Now I wish to GROUP BY the Field1, which is not that hard. After this, I want to add a row number to each group. And finally.. this has to be done in Access which is slightly different of course. So this is the code I have:

SELECT A.*, (SELECT COUNT(*) FROM Tabel1 WHERE A.ID>=ID) AS RowNum
FROM Tabel1 AS A
ORDER BY A.ID;


Result

So, this works well, but now I can't group it. How can I group it?

Answer

You can wrap your entire query in a subquery:

select
  B.*
from
( SELECT
    A.*,
   (SELECT COUNT(*) FROM Tabel1 WHERE A.ID>=ID) AS RowNum
  FROM Tabel1 AS A
) as B
ORDER BY B.ID;

From here, you can do joins as though the output of your query was a table. I have no idea what you actually want to group on, but here is an example:

select
  B.Field1, count (*) as count, max (B.RowNum) as max_row
from
( SELECT
    A.*,
   (SELECT COUNT(*) FROM Tabel1 WHERE A.ID>=ID) AS RowNum
  FROM Tabel1 AS A
) as B
group by
  b.Field1

-- Edit 11/14/2016 --

I think I see now. In that case, first build a query to handle your grouping. This is just an example:

SELECT Field1, min (Id) as min_id, max (id) as max_id
FROM Table1
group by Field1

Name this query Table1_Summary for the purposes of our example.

Now, in your new query, you will refer to Table1_Summary in the exact same way you did with your example:

SELECT 
  t.*,
  (select count (*) from Table1_Summary t2 where t.Field1 >= t2.Field1) as RowNum
FROM Table1_Summary t

You could theoretically do this in a single query, but for readability/maintainability sake, I'd recommend you keep them split. Here is an example of the output:

enter image description here