How to limit max rows in select query by key
0
I have
ID ID2 Amount
-------- -------- ------
01 02 20
01 03 30
02 04 40
02 06 30
03 05 70
03 06 60
03 07 60
04 08 100
04 09 110
I want to query the data above into 2 resultset (return max 5 rows) which are:
result 1:
ID1 ID2 Amount
-------- -------- ------
01 02 20
01 03 30
02 04 40
02 06 30
result 2:
ID1 ID2 Amount
-------- -------- ------
03 05 70
03 06 60
03 07 60
04 08 100
04 09 110
How should I construct my query?
sql
sql-server tsql edited Nov 20 '18 at 8:32
Rahul Neekhra
6001627
asked Nov 20 '18 at 3:39
Wayne CywayWayne Cyway
11
|
show 3 more comments
0
I have
ID ID2 Amount
-------- -------- ------
01 02 20
01 03 30
02 04 40
02 06 30
03 05 70
03 06 60
03 07 60
04 08 100
04 09 110
I want to query the data above into 2 resultset (return max 5 rows) which are:
result 1:
ID1 ID2 Amount
-------- -------- ------
01 02 20
01 03 30
02 04 40
02 06 30
result 2:
ID1 ID2 Amount
-------- -------- ------
03 05 70
03 06 60
03 07 60
04 08 100
04 09 110
How should I construct my query?
sql
sql-server tsql edited Nov 20 '18 at 8:32
Rahul Neekhra
6001627
asked Nov 20 '18 at 3:39
Wayne CywayWayne Cyway
11
2
What’s the logic behind the rows you choose? Maybe you are looking for the NTILE function to split your data in half?
– scsimon
Nov 20 '18 at 3:43
I want to write the data into files where it needs to be grouped by ID1. Each of the file limited to max 5 rows
– Wayne Cyway
Nov 20 '18 at 3:47
But your second result set isn’t grouped by ID1. It has multiple IDs
– scsimon
Nov 20 '18 at 3:49
ID1 (03) having 3 rows where result 1 has already 4 rows, hence ID1 (03) need to be in result 2.
– Wayne Cyway
Nov 20 '18 at 3:57
Now each result set has multiple IDs. Is the idea to group the result set into 5 rows ordered by ID1? How would you handle sets where the ID1 could straddle two sets? What’s the purpose?
– scsimon
Nov 20 '18 at 3:58
|
show 3 more comments
0
0
0
I have
ID ID2 Amount
-------- -------- ------
01 02 20
01 03 30
02 04 40
02 06 30
03 05 70
03 06 60
03 07 60
04 08 100
04 09 110
I want to query the data above into 2 resultset (return max 5 rows) which are:
result 1:
ID1 ID2 Amount
-------- -------- ------
01 02 20
01 03 30
02 04 40
02 06 30
result 2:
ID1 ID2 Amount
-------- -------- ------
03 05 70
03 06 60
03 07 60
04 08 100
04 09 110
How should I construct my query?
sql
sql-server tsql edited Nov 20 '18 at 8:32
Rahul Neekhra
6001627
asked Nov 20 '18 at 3:39
Wayne CywayWayne Cyway
11
I have
ID ID2 Amount
-------- -------- ------
01 02 20
01 03 30
02 04 40
02 06 30
03 05 70
03 06 60
03 07 60
04 08 100
04 09 110
I want to query the data above into 2 resultset (return max 5 rows) which are:
result 1:
ID1 ID2 Amount
-------- -------- ------
01 02 20
01 03 30
02 04 40
02 06 30
result 2:
ID1 ID2 Amount
-------- -------- ------
03 05 70
03 06 60
03 07 60
04 08 100
04 09 110
How should I construct my query?
sql
sql-server tsql sql
sql-server tsql edited Nov 20 '18 at 8:32
Rahul Neekhra
6001627
asked Nov 20 '18 at 3:39
Wayne CywayWayne Cyway
11
edited Nov 20 '18 at 8:32
Rahul Neekhra
6001627
asked Nov 20 '18 at 3:39
Wayne CywayWayne Cyway
11
edited Nov 20 '18 at 8:32
Rahul Neekhra
6001627
edited Nov 20 '18 at 8:32
Rahul Neekhra
6001627
edited Nov 20 '18 at 8:32
Rahul Neekhra
6001627
6001627
asked Nov 20 '18 at 3:39
Wayne CywayWayne Cyway
11
asked Nov 20 '18 at 3:39
Wayne CywayWayne Cyway
11
asked Nov 20 '18 at 3:39
Wayne CywayWayne Cyway
11
11
2
What’s the logic behind the rows you choose? Maybe you are looking for the NTILE function to split your data in half?
– scsimon
Nov 20 '18 at 3:43
I want to write the data into files where it needs to be grouped by ID1. Each of the file limited to max 5 rows
– Wayne Cyway
Nov 20 '18 at 3:47
But your second result set isn’t grouped by ID1. It has multiple IDs
– scsimon
Nov 20 '18 at 3:49
ID1 (03) having 3 rows where result 1 has already 4 rows, hence ID1 (03) need to be in result 2.
– Wayne Cyway
Nov 20 '18 at 3:57
Now each result set has multiple IDs. Is the idea to group the result set into 5 rows ordered by ID1? How would you handle sets where the ID1 could straddle two sets? What’s the purpose?
– scsimon
Nov 20 '18 at 3:58
|
show 3 more comments
2
What’s the logic behind the rows you choose? Maybe you are looking for the NTILE function to split your data in half?
– scsimon
Nov 20 '18 at 3:43
I want to write the data into files where it needs to be grouped by ID1. Each of the file limited to max 5 rows
– Wayne Cyway
Nov 20 '18 at 3:47
But your second result set isn’t grouped by ID1. It has multiple IDs
– scsimon
Nov 20 '18 at 3:49
ID1 (03) having 3 rows where result 1 has already 4 rows, hence ID1 (03) need to be in result 2.
– Wayne Cyway
Nov 20 '18 at 3:57
Now each result set has multiple IDs. Is the idea to group the result set into 5 rows ordered by ID1? How would you handle sets where the ID1 could straddle two sets? What’s the purpose?
– scsimon
Nov 20 '18 at 3:58
2
2
What’s the logic behind the rows you choose? Maybe you are looking for the NTILE function to split your data in half?
– scsimon
Nov 20 '18 at 3:43
What’s the logic behind the rows you choose? Maybe you are looking for the NTILE function to split your data in half?
– scsimon
Nov 20 '18 at 3:43
I want to write the data into files where it needs to be grouped by ID1. Each of the file limited to max 5 rows
– Wayne Cyway
Nov 20 '18 at 3:47
I want to write the data into files where it needs to be grouped by ID1. Each of the file limited to max 5 rows
– Wayne Cyway
Nov 20 '18 at 3:47
But your second result set isn’t grouped by ID1. It has multiple IDs
– scsimon
Nov 20 '18 at 3:49
But your second result set isn’t grouped by ID1. It has multiple IDs
– scsimon
Nov 20 '18 at 3:49
ID1 (03) having 3 rows where result 1 has already 4 rows, hence ID1 (03) need to be in result 2.
– Wayne Cyway
Nov 20 '18 at 3:57
ID1 (03) having 3 rows where result 1 has already 4 rows, hence ID1 (03) need to be in result 2.
– Wayne Cyway
Nov 20 '18 at 3:57
Now each result set has multiple IDs. Is the idea to group the result set into 5 rows ordered by ID1? How would you handle sets where the ID1 could straddle two sets? What’s the purpose?
– scsimon
Nov 20 '18 at 3:58
Now each result set has multiple IDs. Is the idea to group the result set into 5 rows ordered by ID1? How would you handle sets where the ID1 could straddle two sets? What’s the purpose?
– scsimon
Nov 20 '18 at 3:58
|
show 3 more comments
1 Answer
1
active
oldest
votes
0
use row_number() to generate a sequential no. (row_number() over (order by ID1) - 1) / 5 + 1 will gives you set of 5 per each result
; WITH CTE AS
(
SELECT result_no = (row_number() over (order by ID1) - 1) / 5 + 1,
ID1, ID2, Amount
FROM yourtable
)
SELECT *
FROM CTE
WHERE result_no = @result_no
answered Nov 20 '18 at 4:58
SquirrelSquirrel
11.8k22127
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
});
}
});
draft saved
draft discarded
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%2f53385880%2fhow-to-limit-max-rows-in-select-query-by-key%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
use row_number() to generate a sequential no. (row_number() over (order by ID1) - 1) / 5 + 1 will gives you set of 5 per each result
; WITH CTE AS
(
SELECT result_no = (row_number() over (order by ID1) - 1) / 5 + 1,
ID1, ID2, Amount
FROM yourtable
)
SELECT *
FROM CTE
WHERE result_no = @result_no
answered Nov 20 '18 at 4:58
SquirrelSquirrel
11.8k22127
add a comment |
0
use row_number() to generate a sequential no. (row_number() over (order by ID1) - 1) / 5 + 1 will gives you set of 5 per each result
; WITH CTE AS
(
SELECT result_no = (row_number() over (order by ID1) - 1) / 5 + 1,
ID1, ID2, Amount
FROM yourtable
)
SELECT *
FROM CTE
WHERE result_no = @result_no
answered Nov 20 '18 at 4:58
SquirrelSquirrel
11.8k22127
add a comment |
0
0
0
use row_number() to generate a sequential no. (row_number() over (order by ID1) - 1) / 5 + 1 will gives you set of 5 per each result
; WITH CTE AS
(
SELECT result_no = (row_number() over (order by ID1) - 1) / 5 + 1,
ID1, ID2, Amount
FROM yourtable
)
SELECT *
FROM CTE
WHERE result_no = @result_no
answered Nov 20 '18 at 4:58
SquirrelSquirrel
11.8k22127
use row_number() to generate a sequential no. (row_number() over (order by ID1) - 1) / 5 + 1 will gives you set of 5 per each result
; WITH CTE AS
(
SELECT result_no = (row_number() over (order by ID1) - 1) / 5 + 1,
ID1, ID2, Amount
FROM yourtable
)
SELECT *
FROM CTE
WHERE result_no = @result_no
answered Nov 20 '18 at 4:58
SquirrelSquirrel
11.8k22127
answered Nov 20 '18 at 4:58
SquirrelSquirrel
11.8k22127
answered Nov 20 '18 at 4:58
SquirrelSquirrel
11.8k22127
answered Nov 20 '18 at 4:58
SquirrelSquirrel
11.8k22127
11.8k22127
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53385880%2fhow-to-limit-max-rows-in-select-query-by-key%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’s the logic behind the rows you choose? Maybe you are looking for the NTILE function to split your data in half?
– scsimon
Nov 20 '18 at 3:43
I want to write the data into files where it needs to be grouped by ID1. Each of the file limited to max 5 rows
– Wayne Cyway
Nov 20 '18 at 3:47
But your second result set isn’t grouped by ID1. It has multiple IDs
– scsimon
Nov 20 '18 at 3:49
ID1 (03) having 3 rows where result 1 has already 4 rows, hence ID1 (03) need to be in result 2.
– Wayne Cyway
Nov 20 '18 at 3:57
Now each result set has multiple IDs. Is the idea to group the result set into 5 rows ordered by ID1? How would you handle sets where the ID1 could straddle two sets? What’s the purpose?
– scsimon
Nov 20 '18 at 3:58