Analytic Function: ROW_NUMBER( )
I have a table "Invoice"
id integer Primary key
customer_id Integer
total Number (*,2)
The query is to display all customer_id, total and running serial number to each customer with alias name as 'SNO'. And the records should be displayed in ascending order based on the customer_id and then by SNO.
Hints:
- Analytic Function: ROW_NUMBER( )
- Analytic Clause: query_partition_clause and order_by_clause.
I wrote the below query:
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY customer_id ASC) AS "SNO"
from invoice;
But the result is failing. What is that I am missing. Also what is meant "the records should be displayed in ascending order based on the customer_id and then by SNO".
The result I am getting is as below:
CUSTOMER_ID TOTAL SNO
1 70000 1
2 250000 1
2 560000 2
3 200000 1
3 45000 2
4 475000 1
5 50000 1
5 10000 2
6 600000 1
6 90000 2
Expected result is :
CUSTOMER_ID TOTAL SNO
1 70000 1
2 250000 1
2 560000 2
3 45000 1
3 200000 2
4 475000 1
5 10000 1
5 50000 2
6 600000 1
6 90000 2
TOTAL Column data is not matching.
sql oracle window-functions
asked Nov 21 '18 at 11:07
DeesDees
115
add a comment |
I have a table "Invoice"
id integer Primary key
customer_id Integer
total Number (*,2)
The query is to display all customer_id, total and running serial number to each customer with alias name as 'SNO'. And the records should be displayed in ascending order based on the customer_id and then by SNO.
Hints:
- Analytic Function: ROW_NUMBER( )
- Analytic Clause: query_partition_clause and order_by_clause.
I wrote the below query:
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY customer_id ASC) AS "SNO"
from invoice;
But the result is failing. What is that I am missing. Also what is meant "the records should be displayed in ascending order based on the customer_id and then by SNO".
The result I am getting is as below:
CUSTOMER_ID TOTAL SNO
1 70000 1
2 250000 1
2 560000 2
3 200000 1
3 45000 2
4 475000 1
5 50000 1
5 10000 2
6 600000 1
6 90000 2
Expected result is :
CUSTOMER_ID TOTAL SNO
1 70000 1
2 250000 1
2 560000 2
3 45000 1
3 200000 2
4 475000 1
5 10000 1
5 50000 2
6 600000 1
6 90000 2
TOTAL Column data is not matching.
sql oracle window-functions
asked Nov 21 '18 at 11:07
DeesDees
115
2
What is the error you get?
– a_horse_with_no_name
Nov 21 '18 at 11:15
2
"the result is failing" - what does that mean? Do you get an error? Wrong results? Partitioning and ordering by the same column is odd, and indeterminate; is there some other column like a timestamp you can order by instead? "and then by SNO" doesn't really help as you're generating that...
– Alex Poole
Nov 21 '18 at 11:15
Wrong results I am getting. No timestamp column.
– Dees
Nov 21 '18 at 14:08
The results you're getting are ordered by SNO (though as dnoeth mentioned, you should still have an explicit order-by clause really). You seem to be expecting them to be ordered by total, and for SNO to track that; but your question and the assignment you seem to have quoted doesn't actually say how the 'running serial number' should be found. Changing yourrow_number()to order byidortotalseems the obvious step, but it isn't clear which (if either) would be correct, as that hasn't been specified in what you've shown us.
– Alex Poole
Nov 21 '18 at 18:21
add a comment |
I have a table "Invoice"
id integer Primary key
customer_id Integer
total Number (*,2)
The query is to display all customer_id, total and running serial number to each customer with alias name as 'SNO'. And the records should be displayed in ascending order based on the customer_id and then by SNO.
Hints:
- Analytic Function: ROW_NUMBER( )
- Analytic Clause: query_partition_clause and order_by_clause.
I wrote the below query:
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY customer_id ASC) AS "SNO"
from invoice;
But the result is failing. What is that I am missing. Also what is meant "the records should be displayed in ascending order based on the customer_id and then by SNO".
The result I am getting is as below:
CUSTOMER_ID TOTAL SNO
1 70000 1
2 250000 1
2 560000 2
3 200000 1
3 45000 2
4 475000 1
5 50000 1
5 10000 2
6 600000 1
6 90000 2
Expected result is :
CUSTOMER_ID TOTAL SNO
1 70000 1
2 250000 1
2 560000 2
3 45000 1
3 200000 2
4 475000 1
5 10000 1
5 50000 2
6 600000 1
6 90000 2
TOTAL Column data is not matching.
sql oracle window-functions
asked Nov 21 '18 at 11:07
DeesDees
115
I have a table "Invoice"
id integer Primary key
customer_id Integer
total Number (*,2)
The query is to display all customer_id, total and running serial number to each customer with alias name as 'SNO'. And the records should be displayed in ascending order based on the customer_id and then by SNO.
Hints:
- Analytic Function: ROW_NUMBER( )
- Analytic Clause: query_partition_clause and order_by_clause.
I wrote the below query:
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY customer_id ASC) AS "SNO"
from invoice;
But the result is failing. What is that I am missing. Also what is meant "the records should be displayed in ascending order based on the customer_id and then by SNO".
The result I am getting is as below:
CUSTOMER_ID TOTAL SNO
1 70000 1
2 250000 1
2 560000 2
3 200000 1
3 45000 2
4 475000 1
5 50000 1
5 10000 2
6 600000 1
6 90000 2
Expected result is :
CUSTOMER_ID TOTAL SNO
1 70000 1
2 250000 1
2 560000 2
3 45000 1
3 200000 2
4 475000 1
5 10000 1
5 50000 2
6 600000 1
6 90000 2
TOTAL Column data is not matching.
sql oracle window-functions
sql oracle window-functions
asked Nov 21 '18 at 11:07
DeesDees
115
asked Nov 21 '18 at 11:07
DeesDees
115
edited Nov 21 '18 at 15:39
Dees
asked Nov 21 '18 at 11:07
DeesDees
115
asked Nov 21 '18 at 11:07
DeesDees
115
asked Nov 21 '18 at 11:07
DeesDees
115
115
2
What is the error you get?
– a_horse_with_no_name
Nov 21 '18 at 11:15
2
"the result is failing" - what does that mean? Do you get an error? Wrong results? Partitioning and ordering by the same column is odd, and indeterminate; is there some other column like a timestamp you can order by instead? "and then by SNO" doesn't really help as you're generating that...
– Alex Poole
Nov 21 '18 at 11:15
Wrong results I am getting. No timestamp column.
– Dees
Nov 21 '18 at 14:08
The results you're getting are ordered by SNO (though as dnoeth mentioned, you should still have an explicit order-by clause really). You seem to be expecting them to be ordered by total, and for SNO to track that; but your question and the assignment you seem to have quoted doesn't actually say how the 'running serial number' should be found. Changing yourrow_number()to order byidortotalseems the obvious step, but it isn't clear which (if either) would be correct, as that hasn't been specified in what you've shown us.
– Alex Poole
Nov 21 '18 at 18:21
add a comment |
2
What is the error you get?
– a_horse_with_no_name
Nov 21 '18 at 11:15
2
"the result is failing" - what does that mean? Do you get an error? Wrong results? Partitioning and ordering by the same column is odd, and indeterminate; is there some other column like a timestamp you can order by instead? "and then by SNO" doesn't really help as you're generating that...
– Alex Poole
Nov 21 '18 at 11:15
Wrong results I am getting. No timestamp column.
– Dees
Nov 21 '18 at 14:08
The results you're getting are ordered by SNO (though as dnoeth mentioned, you should still have an explicit order-by clause really). You seem to be expecting them to be ordered by total, and for SNO to track that; but your question and the assignment you seem to have quoted doesn't actually say how the 'running serial number' should be found. Changing yourrow_number()to order byidortotalseems the obvious step, but it isn't clear which (if either) would be correct, as that hasn't been specified in what you've shown us.
– Alex Poole
Nov 21 '18 at 18:21
2
2
What is the error you get?
– a_horse_with_no_name
Nov 21 '18 at 11:15
What is the error you get?
– a_horse_with_no_name
Nov 21 '18 at 11:15
2
2
"the result is failing" - what does that mean? Do you get an error? Wrong results? Partitioning and ordering by the same column is odd, and indeterminate; is there some other column like a timestamp you can order by instead? "and then by SNO" doesn't really help as you're generating that...
– Alex Poole
Nov 21 '18 at 11:15
"the result is failing" - what does that mean? Do you get an error? Wrong results? Partitioning and ordering by the same column is odd, and indeterminate; is there some other column like a timestamp you can order by instead? "and then by SNO" doesn't really help as you're generating that...
– Alex Poole
Nov 21 '18 at 11:15
Wrong results I am getting. No timestamp column.
– Dees
Nov 21 '18 at 14:08
Wrong results I am getting. No timestamp column.
– Dees
Nov 21 '18 at 14:08
The results you're getting are ordered by SNO (though as dnoeth mentioned, you should still have an explicit order-by clause really). You seem to be expecting them to be ordered by total, and for SNO to track that; but your question and the assignment you seem to have quoted doesn't actually say how the 'running serial number' should be found. Changing your
row_number() to order by id or total seems the obvious step, but it isn't clear which (if either) would be correct, as that hasn't been specified in what you've shown us.– Alex Poole
Nov 21 '18 at 18:21
The results you're getting are ordered by SNO (though as dnoeth mentioned, you should still have an explicit order-by clause really). You seem to be expecting them to be ordered by total, and for SNO to track that; but your question and the assignment you seem to have quoted doesn't actually say how the 'running serial number' should be found. Changing your
row_number() to order by id or total seems the obvious step, but it isn't clear which (if either) would be correct, as that hasn't been specified in what you've shown us.– Alex Poole
Nov 21 '18 at 18:21
add a comment |
2 Answers
2
active
oldest
votes
You're close, you probably need to order the row_number by id (assuming it's ascending based on time)
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY id ASC) AS "SNO"
from invoice
order by customer_id, "SNO" -- should be the default anyway (but there's no guarantee)
answered Nov 21 '18 at 11:20
dnoethdnoeth
45.3k31839
Thank you so much @dnoeth it worked :).
– Dees
Nov 23 '18 at 5:57
add a comment |
I have not found any order by clause in your query, another issue in which order you want to generate SNO ? by using id or total that will impact on your ordering
with cte as
(
select 1 cid, 70000 total from dual
union all
select 2, 250000 from dual
union all
select 2, 560000 from dual
union all
select 3, 200000 from dual
union all
select 3, 45000 from dual
union all
select 4, 475000 from dual
union all
select 5, 50000 from dual
union all
select 5, 10000 from dual
union all
select 6, 600000 from dual
union all
select 6, 90000 from dual
)Select cid,total,ROW_NUMBER( ) OVER (PARTITION BY cid ORDER BY total ) AS "SNO" from cte order by cid,SNO
answered Nov 21 '18 at 11:20
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
Have updated the Question.
– Dees
Nov 21 '18 at 15:41
@Deep answer edited check now
– Zaynul Abadin Tuhin
Nov 21 '18 at 16:05
@ Zaynul Abadin Tuhin , I got the results through the solution shared by you, somehow my DB was showing wrong answer. Thank you for the query.
– Dees
Nov 23 '18 at 5:59
@Deep if it helps then mark it as accepted answer
– Zaynul Abadin Tuhin
Nov 23 '18 at 6:01
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410801%2fanalytic-function-row-number%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You're close, you probably need to order the row_number by id (assuming it's ascending based on time)
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY id ASC) AS "SNO"
from invoice
order by customer_id, "SNO" -- should be the default anyway (but there's no guarantee)
answered Nov 21 '18 at 11:20
dnoethdnoeth
45.3k31839
Thank you so much @dnoeth it worked :).
– Dees
Nov 23 '18 at 5:57
add a comment |
You're close, you probably need to order the row_number by id (assuming it's ascending based on time)
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY id ASC) AS "SNO"
from invoice
order by customer_id, "SNO" -- should be the default anyway (but there's no guarantee)
answered Nov 21 '18 at 11:20
dnoethdnoeth
45.3k31839
Thank you so much @dnoeth it worked :).
– Dees
Nov 23 '18 at 5:57
add a comment |
You're close, you probably need to order the row_number by id (assuming it's ascending based on time)
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY id ASC) AS "SNO"
from invoice
order by customer_id, "SNO" -- should be the default anyway (but there's no guarantee)
answered Nov 21 '18 at 11:20
dnoethdnoeth
45.3k31839
You're close, you probably need to order the row_number by id (assuming it's ascending based on time)
Select customer_id,
total,
ROW_NUMBER( ) OVER (PARTITION BY customer_id ORDER BY id ASC) AS "SNO"
from invoice
order by customer_id, "SNO" -- should be the default anyway (but there's no guarantee)
answered Nov 21 '18 at 11:20
dnoethdnoeth
45.3k31839
answered Nov 21 '18 at 11:20
dnoethdnoeth
45.3k31839
answered Nov 21 '18 at 11:20
dnoethdnoeth
45.3k31839
answered Nov 21 '18 at 11:20
dnoethdnoeth
45.3k31839
45.3k31839
Thank you so much @dnoeth it worked :).
– Dees
Nov 23 '18 at 5:57
add a comment |
Thank you so much @dnoeth it worked :).
– Dees
Nov 23 '18 at 5:57
Thank you so much @dnoeth it worked :).
– Dees
Nov 23 '18 at 5:57
Thank you so much @dnoeth it worked :).
– Dees
Nov 23 '18 at 5:57
add a comment |
I have not found any order by clause in your query, another issue in which order you want to generate SNO ? by using id or total that will impact on your ordering
with cte as
(
select 1 cid, 70000 total from dual
union all
select 2, 250000 from dual
union all
select 2, 560000 from dual
union all
select 3, 200000 from dual
union all
select 3, 45000 from dual
union all
select 4, 475000 from dual
union all
select 5, 50000 from dual
union all
select 5, 10000 from dual
union all
select 6, 600000 from dual
union all
select 6, 90000 from dual
)Select cid,total,ROW_NUMBER( ) OVER (PARTITION BY cid ORDER BY total ) AS "SNO" from cte order by cid,SNO
answered Nov 21 '18 at 11:20
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
Have updated the Question.
– Dees
Nov 21 '18 at 15:41
@Deep answer edited check now
– Zaynul Abadin Tuhin
Nov 21 '18 at 16:05
@ Zaynul Abadin Tuhin , I got the results through the solution shared by you, somehow my DB was showing wrong answer. Thank you for the query.
– Dees
Nov 23 '18 at 5:59
@Deep if it helps then mark it as accepted answer
– Zaynul Abadin Tuhin
Nov 23 '18 at 6:01
add a comment |
I have not found any order by clause in your query, another issue in which order you want to generate SNO ? by using id or total that will impact on your ordering
with cte as
(
select 1 cid, 70000 total from dual
union all
select 2, 250000 from dual
union all
select 2, 560000 from dual
union all
select 3, 200000 from dual
union all
select 3, 45000 from dual
union all
select 4, 475000 from dual
union all
select 5, 50000 from dual
union all
select 5, 10000 from dual
union all
select 6, 600000 from dual
union all
select 6, 90000 from dual
)Select cid,total,ROW_NUMBER( ) OVER (PARTITION BY cid ORDER BY total ) AS "SNO" from cte order by cid,SNO
answered Nov 21 '18 at 11:20
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
Have updated the Question.
– Dees
Nov 21 '18 at 15:41
@Deep answer edited check now
– Zaynul Abadin Tuhin
Nov 21 '18 at 16:05
@ Zaynul Abadin Tuhin , I got the results through the solution shared by you, somehow my DB was showing wrong answer. Thank you for the query.
– Dees
Nov 23 '18 at 5:59
@Deep if it helps then mark it as accepted answer
– Zaynul Abadin Tuhin
Nov 23 '18 at 6:01
add a comment |
I have not found any order by clause in your query, another issue in which order you want to generate SNO ? by using id or total that will impact on your ordering
with cte as
(
select 1 cid, 70000 total from dual
union all
select 2, 250000 from dual
union all
select 2, 560000 from dual
union all
select 3, 200000 from dual
union all
select 3, 45000 from dual
union all
select 4, 475000 from dual
union all
select 5, 50000 from dual
union all
select 5, 10000 from dual
union all
select 6, 600000 from dual
union all
select 6, 90000 from dual
)Select cid,total,ROW_NUMBER( ) OVER (PARTITION BY cid ORDER BY total ) AS "SNO" from cte order by cid,SNO
answered Nov 21 '18 at 11:20
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
I have not found any order by clause in your query, another issue in which order you want to generate SNO ? by using id or total that will impact on your ordering
with cte as
(
select 1 cid, 70000 total from dual
union all
select 2, 250000 from dual
union all
select 2, 560000 from dual
union all
select 3, 200000 from dual
union all
select 3, 45000 from dual
union all
select 4, 475000 from dual
union all
select 5, 50000 from dual
union all
select 5, 10000 from dual
union all
select 6, 600000 from dual
union all
select 6, 90000 from dual
)Select cid,total,ROW_NUMBER( ) OVER (PARTITION BY cid ORDER BY total ) AS "SNO" from cte order by cid,SNO
answered Nov 21 '18 at 11:20
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
edited Nov 21 '18 at 16:05
answered Nov 21 '18 at 11:20
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
answered Nov 21 '18 at 11:20
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
answered Nov 21 '18 at 11:20
Zaynul Abadin TuhinZaynul Abadin Tuhin
12.5k2932
12.5k2932
Have updated the Question.
– Dees
Nov 21 '18 at 15:41
@Deep answer edited check now
– Zaynul Abadin Tuhin
Nov 21 '18 at 16:05
@ Zaynul Abadin Tuhin , I got the results through the solution shared by you, somehow my DB was showing wrong answer. Thank you for the query.
– Dees
Nov 23 '18 at 5:59
@Deep if it helps then mark it as accepted answer
– Zaynul Abadin Tuhin
Nov 23 '18 at 6:01
add a comment |
Have updated the Question.
– Dees
Nov 21 '18 at 15:41
@Deep answer edited check now
– Zaynul Abadin Tuhin
Nov 21 '18 at 16:05
@ Zaynul Abadin Tuhin , I got the results through the solution shared by you, somehow my DB was showing wrong answer. Thank you for the query.
– Dees
Nov 23 '18 at 5:59
@Deep if it helps then mark it as accepted answer
– Zaynul Abadin Tuhin
Nov 23 '18 at 6:01
Have updated the Question.
– Dees
Nov 21 '18 at 15:41
Have updated the Question.
– Dees
Nov 21 '18 at 15:41
@Deep answer edited check now
– Zaynul Abadin Tuhin
Nov 21 '18 at 16:05
@Deep answer edited check now
– Zaynul Abadin Tuhin
Nov 21 '18 at 16:05
@ Zaynul Abadin Tuhin , I got the results through the solution shared by you, somehow my DB was showing wrong answer. Thank you for the query.
– Dees
Nov 23 '18 at 5:59
@ Zaynul Abadin Tuhin , I got the results through the solution shared by you, somehow my DB was showing wrong answer. Thank you for the query.
– Dees
Nov 23 '18 at 5:59
@Deep if it helps then mark it as accepted answer
– Zaynul Abadin Tuhin
Nov 23 '18 at 6:01
@Deep if it helps then mark it as accepted answer
– Zaynul Abadin Tuhin
Nov 23 '18 at 6:01
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410801%2fanalytic-function-row-number%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
What is the error you get?
– a_horse_with_no_name
Nov 21 '18 at 11:15
2
"the result is failing" - what does that mean? Do you get an error? Wrong results? Partitioning and ordering by the same column is odd, and indeterminate; is there some other column like a timestamp you can order by instead? "and then by SNO" doesn't really help as you're generating that...
– Alex Poole
Nov 21 '18 at 11:15
Wrong results I am getting. No timestamp column.
– Dees
Nov 21 '18 at 14:08
The results you're getting are ordered by SNO (though as dnoeth mentioned, you should still have an explicit order-by clause really). You seem to be expecting them to be ordered by total, and for SNO to track that; but your question and the assignment you seem to have quoted doesn't actually say how the 'running serial number' should be found. Changing your
row_number()to order byidortotalseems the obvious step, but it isn't clear which (if either) would be correct, as that hasn't been specified in what you've shown us.– Alex Poole
Nov 21 '18 at 18:21