If you’ve been following this blog, you’ll know that I write a fair bit of SQL. I’m going to post some small snippets of SQL which I had to work out how to accomplish a small task as part of a larger script.
In thi spost, I am going to show how use ROW_NUMBER to generate a unique row number. There are three examples of code.
This first example, the simplest of the three, shows how to generate a unique number for each row in the recordset:
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
PONUMBER
,ORD
,ITEMNMBR
,ITEMDESC
,ROW_NUMBER() OVER(ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
POP30110
GO
The second example, shows a row number can be assigned to the lines of each transaction (this is accomplished :
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT
PONUMBER
,ORD
,ITEMNMBR
,ITEMDESC
,ROW_NUMBER() OVER(PARTITION BY PONUMBER ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
POP30110
GO
This final example, takes the first example and shows how we can select a range of row numbers (this is useful if you are selecting data to display on a page):
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
WITH POTRX AS
(SELECT
PONUMBER
,ORD
,ITEMNMBR
,ITEMDESC
,ROW_NUMBER() OVER(ORDER BY PONUMBER ORDER BY PONUMBER, ORD ASC) AS CUSTOM_ROW_ID
FROM
POP30110)
SELECT
*
FROM
POTRX
WHERE
CUSTOM_ROW_ID BETWEEN 40 AND 59
GO