...
For our sample, let's use the Customers file from the SampleData database that is distributed with Pentaho BI and this query:
SELECT
"CUSTOMERS"."CUSTOMERNAME"
,
"CUSTOMERS"."ADDRESSLINE1"
,
"CUSTOMERS"."CITY"
,
"CUSTOMERS"."STATE"
,
"CUSTOMERS"."POSTALCODE"
FROM
"CUSTOMERS"
WHERE
"CUSTOMERS"."COUNTRY" = 'USA'
ORDER BY
"CUSTOMERS"."POSTALCODE"
,
"CUSTOMERS"."CUSTOMERNAME"
...
Some databases support returning a row number from a result set. For example MySQL does it this way:
select @rownum SELECT @ROWNUM := @rownum @ROWNUM +1 as rownumAS ROWNUM
, `customers``CUSTOMERS`.`CUSTOMERNAME`
, `customers``CUSTOMERS`.`ADDRESSLINE1`
, `customers``CUSTOMERS`.`CITY`
, `customers``CUSTOMERS`.`STATE`
, `customers``CUSTOMERS`.`POSTALCODE`
from
(select @rownum FROM (SELECT @ROWNUM :=0) rR
,
customers
where
`customers`CUSTOMERS
WHERE `CUSTOMERS`.`COUNTRY` = 'USA'
order by
`customers` ORDER BY `CUSTOMERS`.`POSTALCODE`
, `customers``CUSTOMERS`.`CUSTOMERNAME`
;
Unfortunately, this isn't a universal capability, but we . We can use a report function to remedy overcome that, so let's add a function, Running Count, and name it R0.
...