SQL code to get next variable in table with different value
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I need to find a way in SQL Server 2014 Management Studios to find the next unique value in a column that shares the value of a different column.
So for example below I would want my results to be
Column 1 - A
Column 2 - 1
Column 3 - 4
As that is the first time that A has unique values in column 2 and 3
Column1 | Column2 | Column3
---------+---------+---------
| A | X | 1 |
| A | X | 2 |
| B | Y | 3 |
| A | Z | 4 |
Query:
SELECT
Column1,
LEAD(Column3) OVER (PARTITION BY Column2 ORDER BY Column3) AS FindValue
FROM
Table
sql window-functions lead
edited Jan 4 at 18:33
a_horse_with_no_name
308k46471572
asked Jan 4 at 4:52
Chad CollingsChad Collings
61
add a comment |
I need to find a way in SQL Server 2014 Management Studios to find the next unique value in a column that shares the value of a different column.
So for example below I would want my results to be
Column 1 - A
Column 2 - 1
Column 3 - 4
As that is the first time that A has unique values in column 2 and 3
Column1 | Column2 | Column3
---------+---------+---------
| A | X | 1 |
| A | X | 2 |
| B | Y | 3 |
| A | Z | 4 |
Query:
SELECT
Column1,
LEAD(Column3) OVER (PARTITION BY Column2 ORDER BY Column3) AS FindValue
FROM
Table
sql window-functions lead
edited Jan 4 at 18:33
a_horse_with_no_name
308k46471572
asked Jan 4 at 4:52
Chad CollingsChad Collings
61
Can you please explain the logic of your output ?
– Avi
Jan 4 at 5:00
As best as I can.. words are not coming to me at the moment.. I would like to display each unique value from column1 (so for row 1 - A) then the first the first record from Column 3 based on the first unique record of Column 2 ( so 1, as its the first value to align to X), then finally the first record from column 3 - that shares the value from column 1, but is the next value from column2. (as in Z comes after X)
– Chad Collings
Jan 4 at 5:07
Please, show the exact output you need (in the form of table). For now it's not clear what you need.
– JohnyL
Jan 4 at 9:31
add a comment |
I need to find a way in SQL Server 2014 Management Studios to find the next unique value in a column that shares the value of a different column.
So for example below I would want my results to be
Column 1 - A
Column 2 - 1
Column 3 - 4
As that is the first time that A has unique values in column 2 and 3
Column1 | Column2 | Column3
---------+---------+---------
| A | X | 1 |
| A | X | 2 |
| B | Y | 3 |
| A | Z | 4 |
Query:
SELECT
Column1,
LEAD(Column3) OVER (PARTITION BY Column2 ORDER BY Column3) AS FindValue
FROM
Table
sql window-functions lead
edited Jan 4 at 18:33
a_horse_with_no_name
308k46471572
asked Jan 4 at 4:52
Chad CollingsChad Collings
61
I need to find a way in SQL Server 2014 Management Studios to find the next unique value in a column that shares the value of a different column.
So for example below I would want my results to be
Column 1 - A
Column 2 - 1
Column 3 - 4
As that is the first time that A has unique values in column 2 and 3
Column1 | Column2 | Column3
---------+---------+---------
| A | X | 1 |
| A | X | 2 |
| B | Y | 3 |
| A | Z | 4 |
Query:
SELECT
Column1,
LEAD(Column3) OVER (PARTITION BY Column2 ORDER BY Column3) AS FindValue
FROM
Table
sql window-functions lead
sql window-functions lead
edited Jan 4 at 18:33
a_horse_with_no_name
308k46471572
asked Jan 4 at 4:52
Chad CollingsChad Collings
61
edited Jan 4 at 18:33
a_horse_with_no_name
308k46471572
asked Jan 4 at 4:52
Chad CollingsChad Collings
61
edited Jan 4 at 18:33
a_horse_with_no_name
308k46471572
edited Jan 4 at 18:33
a_horse_with_no_name
308k46471572
edited Jan 4 at 18:33
a_horse_with_no_name
308k46471572
308k46471572
asked Jan 4 at 4:52
Chad CollingsChad Collings
61
asked Jan 4 at 4:52
Chad CollingsChad Collings
61
asked Jan 4 at 4:52
Chad CollingsChad Collings
61
61
Can you please explain the logic of your output ?
– Avi
Jan 4 at 5:00
As best as I can.. words are not coming to me at the moment.. I would like to display each unique value from column1 (so for row 1 - A) then the first the first record from Column 3 based on the first unique record of Column 2 ( so 1, as its the first value to align to X), then finally the first record from column 3 - that shares the value from column 1, but is the next value from column2. (as in Z comes after X)
– Chad Collings
Jan 4 at 5:07
Please, show the exact output you need (in the form of table). For now it's not clear what you need.
– JohnyL
Jan 4 at 9:31
add a comment |
Can you please explain the logic of your output ?
– Avi
Jan 4 at 5:00
As best as I can.. words are not coming to me at the moment.. I would like to display each unique value from column1 (so for row 1 - A) then the first the first record from Column 3 based on the first unique record of Column 2 ( so 1, as its the first value to align to X), then finally the first record from column 3 - that shares the value from column 1, but is the next value from column2. (as in Z comes after X)
– Chad Collings
Jan 4 at 5:07
Please, show the exact output you need (in the form of table). For now it's not clear what you need.
– JohnyL
Jan 4 at 9:31
Can you please explain the logic of your output ?
– Avi
Jan 4 at 5:00
Can you please explain the logic of your output ?
– Avi
Jan 4 at 5:00
As best as I can.. words are not coming to me at the moment.. I would like to display each unique value from column1 (so for row 1 - A) then the first the first record from Column 3 based on the first unique record of Column 2 ( so 1, as its the first value to align to X), then finally the first record from column 3 - that shares the value from column 1, but is the next value from column2. (as in Z comes after X)
– Chad Collings
Jan 4 at 5:07
As best as I can.. words are not coming to me at the moment.. I would like to display each unique value from column1 (so for row 1 - A) then the first the first record from Column 3 based on the first unique record of Column 2 ( so 1, as its the first value to align to X), then finally the first record from column 3 - that shares the value from column 1, but is the next value from column2. (as in Z comes after X)
– Chad Collings
Jan 4 at 5:07
Please, show the exact output you need (in the form of table). For now it's not clear what you need.
– JohnyL
Jan 4 at 9:31
Please, show the exact output you need (in the form of table). For now it's not clear what you need.
– JohnyL
Jan 4 at 9:31
add a comment |
1 Answer
1
active
oldest
votes
If I understand it correctly I would try something like this:
-- first we find minimum values for column1, column2 variations
WITH min_values AS (
SELECT
column1,
column2,
min(column3) AS min_value
FROM
table
GROUP BY 1,2
)
-- then we find bottom 2 values for column1
,bottom_2 AS (
SELECT
column1,
min_value,
row_number() OVER (PARTITION BY column1 ORDER BY min_value ASC) AS rn
FROM
min_values
)
-- THEN we JOIN results INTO single record
SELECT
b1.column1, b2.min_value, b1.min_value
FROM
bottom_2 b1
JOIN
bottom_2 b2 ON b1.column1 = b2.column1 AND b2.rn < b1.rn
WHERE b1.rn <= 2
I just checked comments above and would like to add some notes.
If you want to find next value ordered by column2 then you have to change order by from min_value to column2 in row_number() line. Otherwise, if you are looking for next inserted value then you need a timestamp or some kind of id.
answered Jan 4 at 17:37
wojtgwojtg
9112
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%2f54033290%2fsql-code-to-get-next-variable-in-table-with-different-value%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
If I understand it correctly I would try something like this:
-- first we find minimum values for column1, column2 variations
WITH min_values AS (
SELECT
column1,
column2,
min(column3) AS min_value
FROM
table
GROUP BY 1,2
)
-- then we find bottom 2 values for column1
,bottom_2 AS (
SELECT
column1,
min_value,
row_number() OVER (PARTITION BY column1 ORDER BY min_value ASC) AS rn
FROM
min_values
)
-- THEN we JOIN results INTO single record
SELECT
b1.column1, b2.min_value, b1.min_value
FROM
bottom_2 b1
JOIN
bottom_2 b2 ON b1.column1 = b2.column1 AND b2.rn < b1.rn
WHERE b1.rn <= 2
I just checked comments above and would like to add some notes.
If you want to find next value ordered by column2 then you have to change order by from min_value to column2 in row_number() line. Otherwise, if you are looking for next inserted value then you need a timestamp or some kind of id.
answered Jan 4 at 17:37
wojtgwojtg
9112
add a comment |
If I understand it correctly I would try something like this:
-- first we find minimum values for column1, column2 variations
WITH min_values AS (
SELECT
column1,
column2,
min(column3) AS min_value
FROM
table
GROUP BY 1,2
)
-- then we find bottom 2 values for column1
,bottom_2 AS (
SELECT
column1,
min_value,
row_number() OVER (PARTITION BY column1 ORDER BY min_value ASC) AS rn
FROM
min_values
)
-- THEN we JOIN results INTO single record
SELECT
b1.column1, b2.min_value, b1.min_value
FROM
bottom_2 b1
JOIN
bottom_2 b2 ON b1.column1 = b2.column1 AND b2.rn < b1.rn
WHERE b1.rn <= 2
I just checked comments above and would like to add some notes.
If you want to find next value ordered by column2 then you have to change order by from min_value to column2 in row_number() line. Otherwise, if you are looking for next inserted value then you need a timestamp or some kind of id.
answered Jan 4 at 17:37
wojtgwojtg
9112
add a comment |
If I understand it correctly I would try something like this:
-- first we find minimum values for column1, column2 variations
WITH min_values AS (
SELECT
column1,
column2,
min(column3) AS min_value
FROM
table
GROUP BY 1,2
)
-- then we find bottom 2 values for column1
,bottom_2 AS (
SELECT
column1,
min_value,
row_number() OVER (PARTITION BY column1 ORDER BY min_value ASC) AS rn
FROM
min_values
)
-- THEN we JOIN results INTO single record
SELECT
b1.column1, b2.min_value, b1.min_value
FROM
bottom_2 b1
JOIN
bottom_2 b2 ON b1.column1 = b2.column1 AND b2.rn < b1.rn
WHERE b1.rn <= 2
I just checked comments above and would like to add some notes.
If you want to find next value ordered by column2 then you have to change order by from min_value to column2 in row_number() line. Otherwise, if you are looking for next inserted value then you need a timestamp or some kind of id.
answered Jan 4 at 17:37
wojtgwojtg
9112
If I understand it correctly I would try something like this:
-- first we find minimum values for column1, column2 variations
WITH min_values AS (
SELECT
column1,
column2,
min(column3) AS min_value
FROM
table
GROUP BY 1,2
)
-- then we find bottom 2 values for column1
,bottom_2 AS (
SELECT
column1,
min_value,
row_number() OVER (PARTITION BY column1 ORDER BY min_value ASC) AS rn
FROM
min_values
)
-- THEN we JOIN results INTO single record
SELECT
b1.column1, b2.min_value, b1.min_value
FROM
bottom_2 b1
JOIN
bottom_2 b2 ON b1.column1 = b2.column1 AND b2.rn < b1.rn
WHERE b1.rn <= 2
I just checked comments above and would like to add some notes.
If you want to find next value ordered by column2 then you have to change order by from min_value to column2 in row_number() line. Otherwise, if you are looking for next inserted value then you need a timestamp or some kind of id.
answered Jan 4 at 17:37
wojtgwojtg
9112
edited Jan 4 at 18:37
answered Jan 4 at 17:37
wojtgwojtg
9112
answered Jan 4 at 17:37
wojtgwojtg
9112
answered Jan 4 at 17:37
wojtgwojtg
9112
9112
add a comment |
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%2f54033290%2fsql-code-to-get-next-variable-in-table-with-different-value%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
Can you please explain the logic of your output ?
– Avi
Jan 4 at 5:00
As best as I can.. words are not coming to me at the moment.. I would like to display each unique value from column1 (so for row 1 - A) then the first the first record from Column 3 based on the first unique record of Column 2 ( so 1, as its the first value to align to X), then finally the first record from column 3 - that shares the value from column 1, but is the next value from column2. (as in Z comes after X)
– Chad Collings
Jan 4 at 5:07
Please, show the exact output you need (in the form of table). For now it's not clear what you need.
– JohnyL
Jan 4 at 9:31