1

Topic: Numbering ROW_NUMBER without the last line

Good afternoon!
There is a sampling with the last line. It is necessary to enumerate records without the last line, since the last line - total row (8 line on a screenshot should not be numbered). I can invent Nothing.

SELECT ROW_NUMBER () OVER (ORDER BY (CASE WHEN ID IS NULL THEN MAX (ID) +1 ELSE ID END) ASC) AS ROW, ID, NClaim, DateClaim, NZN, DZN, SUM (LClaim) AS LClaim, SUM (MClaim) AS MClaim, SUM (LMClaim) AS LMClaim, SUM (LProformPredpCalc) AS LProformPredpCalc, SUM (MProformPredpCalc) AS MProformPredpCalc, SUM (LMProformPredpCalc) AS LMProformPredpCalc FROM qdfAccount WHERE Oplacheno=0 AND Reject=0 GROUP BY GROUPING SETS ((ID, NClaim, DateClaim, NZN, DZN), ())

2

Re: Numbering ROW_NUMBER without the last line

ypmail;
Enter one more field-date stamp (through all the same ROW_NUMBER), but in reverse sorting. For a line with RN = 1 do not display value from the initiating field-date stamp...

3

Re: Numbering ROW_NUMBER without the last line

Not absolutely I understand, it is possible an example?

4

Re: Numbering ROW_NUMBER without the last line

ypmail;

--
-- Dial-up of the test data:
with
b$m_test (id) as
(
select * from (values (1), (5), (5), (8), (8), (8), (10), (15), (15), (17)) v (n)
)
--
-- The main request:
select case row_number () over (order by case when id is null then 0 else 1 end, id desc)
when 1
then null
else row_number () over (order by case when id is null then 1 else 0 end, id)
end as x_rn
, ID
, count (1) as cnt
from b$m_test
group by rollup (id)
order by case when id is null then 1 else 0 end, id

on-line check on sqlfiddle.com
Though, in yours a case, probably, more simple variant suffices also:

--
-- Dial-up of the test data:
with
b$m_test (id) as
(
select * from (values (1), (5), (5), (8), (8), (8), (10), (15), (15), (17)) v (n)
)
--
-- The main request:
select case
when id is null
then null
else row_number () over (order by case when id is null then 1 else 0 end, id)
end as x_rn
, ID
, count (1) as cnt
from b$m_test
group by rollup (id)
order by case when id is null then 1 else 0 end, id

on-line check on sqlfiddle.com

5

Re: Numbering ROW_NUMBER without the last line

Kind  - Eh, thanks for the help.
Not absolutely understood manipulations with 0 and 1 in an example, but in my case earned

SELECT CASE WHEN ID IS NULL THEN NULL ELSE ROW_NUMBER () OVER (ORDER BY (CASE WHEN ID IS NULL THEN MAX (ID) +1 ELSE ID END) ASC) END AS ROW;
ID, NClaim, DateClaim, NZN, DZN, SUM (LClaim) AS LClaim, SUM (MClaim) AS MClaim, SUM (LMClaim) AS LMClaim, SUM (LProformPredpCalc) AS LProformPredpCalc;
SUM (MProformPredpCalc) AS MProformPredpCalc, SUM (LMProformPredpCalc) AS LMProformPredpCalc
FROM qdfAccount WHERE Oplacheno=0 AND Reject=0
GROUP BY GROUPING SETS ((ID, NClaim, DateClaim, NZN, DZN), ())

6

Re: Numbering ROW_NUMBER without the last line

And if so?

SELECT CASE WHEN ID IS NULL THEN NULL ELSE ROW_NUMBER () OVER (ORDER BY ID ASC) END AS ROW;
ID, NClaim, DateClaim, NZN, DZN, SUM (LClaim) AS LClaim, SUM (MClaim) AS MClaim, SUM (LMClaim) AS LMClaim, SUM (LProformPredpCalc) AS LProformPredpCalc;
SUM (MProformPredpCalc) AS MProformPredpCalc, SUM (LMProformPredpCalc) AS LMProformPredpCalc
FROM qdfAccount WHERE Oplacheno=0 AND Reject=0
GROUP BY GROUPING SETS ((ID, NClaim, DateClaim, NZN, DZN), ())

7

Re: Numbering ROW_NUMBER without the last line

And in what a difference?

8

Re: Numbering ROW_NUMBER without the last line

Found in what a difference. So does not work, I in MAX (ID) +1 put a line with zero ID in the sampling end, it at me total row. And by your variant it becomes in the beginning.

9

Re: Numbering ROW_NUMBER without the last line

It turned out to deduce in the report html 3 different tables with numbering and the totals, all thanks!