SQL Grouping through temptable and getting new column value if one of the column value is 1Add a column with a default value to an existing table in SQL ServerHow to check if a column exists in a SQL Server table?Multiple Indexes vs Multi-Column IndexesSQL update query using joinsRetrieving the last record in each group - MySQLSQL Server: How to Join to first rowFinding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?Get top 1 row of each groupSQL select only rows with max value on a column
Translation of Scottish 16th century church stained glass
Is a model fitted to data or is data fitted to a model?
Difference between -| and |- in TikZ
How can Trident be so inexpensive? Will it orbit Triton or just do a (slow) flyby?
Can we have a perfect cadence in a minor key?
What does this horizontal bar at the first measure mean?
How to decide convergence of Integrals
Could solar power be utilized and substitute coal in the 19th Century
Flux received by a negative charge
About a little hole in Z'ha'dum
Is there a conventional notation or name for the slip angle?
Why is Arduino resetting while driving motors?
Why did the HMS Bounty go back to a time when whales are already rare?
Visiting the UK as unmarried couple
Should I install hardwood flooring or cabinets first?
Two-sided logarithm inequality
Reply 'no position' while the job posting is still there
How do I extrude a face to a single vertex
What (else) happened July 1st 1858 in London?
Can a significant change in incentives void an employment contract?
Gibbs free energy in standard state vs. equilibrium
Why has "pence" been used in this sentence, not "pences"?
What is the difference between "Do you interest" and "...interested in" something?
A Permanent Norse Presence in America
SQL Grouping through temptable and getting new column value if one of the column value is 1
Add a column with a default value to an existing table in SQL ServerHow to check if a column exists in a SQL Server table?Multiple Indexes vs Multi-Column IndexesSQL update query using joinsRetrieving the last record in each group - MySQLSQL Server: How to Join to first rowFinding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?Get top 1 row of each groupSQL select only rows with max value on a column
I have 3 columns in table in my DB and want to achieve following in SQL
Create table #TempTableChild
(
Child_Id int,
UnReadCount int,
Adult_id int
)
I have following values in #TempTableChild:
Child_Id | UnreadCount | AdultId | NewColumnVal
---------+-------------+---------+----------------
28 | 1 | 4 | 0
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 0
6 | 0 | 8 | 0
And, want to achieve below data.
Child_Id | UnreadCount | AdultId | NewColumnVal
---------+-------------+---------+----------------
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
I want top value of NewColumnVal = 1 when for each distinct AdultId as shown above.
When for AdultId = 4 UnreadCount = 1 NewColumnVal =1 for first Child_Id only then all other subsequent values of NewColumnVal = 0 then again for new adult_id top value of NewColumnVal = 0 if UnreadCount = 0
sql
sql-server edited Mar 8 at 7:20
marc_s
582k13011231269
asked Mar 8 at 6:36
GUNJAN ASWANIGUNJAN ASWANI
385
add a comment |
I have 3 columns in table in my DB and want to achieve following in SQL
Create table #TempTableChild
(
Child_Id int,
UnReadCount int,
Adult_id int
)
I have following values in #TempTableChild:
Child_Id | UnreadCount | AdultId | NewColumnVal
---------+-------------+---------+----------------
28 | 1 | 4 | 0
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 0
6 | 0 | 8 | 0
And, want to achieve below data.
Child_Id | UnreadCount | AdultId | NewColumnVal
---------+-------------+---------+----------------
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
I want top value of NewColumnVal = 1 when for each distinct AdultId as shown above.
When for AdultId = 4 UnreadCount = 1 NewColumnVal =1 for first Child_Id only then all other subsequent values of NewColumnVal = 0 then again for new adult_id top value of NewColumnVal = 0 if UnreadCount = 0
sql
sql-server edited Mar 8 at 7:20
marc_s
582k13011231269
asked Mar 8 at 6:36
GUNJAN ASWANIGUNJAN ASWANI
385
Case statement will do the trick!
– Prashant Pimpale
Mar 8 at 6:43
add a comment |
I have 3 columns in table in my DB and want to achieve following in SQL
Create table #TempTableChild
(
Child_Id int,
UnReadCount int,
Adult_id int
)
I have following values in #TempTableChild:
Child_Id | UnreadCount | AdultId | NewColumnVal
---------+-------------+---------+----------------
28 | 1 | 4 | 0
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 0
6 | 0 | 8 | 0
And, want to achieve below data.
Child_Id | UnreadCount | AdultId | NewColumnVal
---------+-------------+---------+----------------
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
I want top value of NewColumnVal = 1 when for each distinct AdultId as shown above.
When for AdultId = 4 UnreadCount = 1 NewColumnVal =1 for first Child_Id only then all other subsequent values of NewColumnVal = 0 then again for new adult_id top value of NewColumnVal = 0 if UnreadCount = 0
sql
sql-server edited Mar 8 at 7:20
marc_s
582k13011231269
asked Mar 8 at 6:36
GUNJAN ASWANIGUNJAN ASWANI
385
I have 3 columns in table in my DB and want to achieve following in SQL
Create table #TempTableChild
(
Child_Id int,
UnReadCount int,
Adult_id int
)
I have following values in #TempTableChild:
Child_Id | UnreadCount | AdultId | NewColumnVal
---------+-------------+---------+----------------
28 | 1 | 4 | 0
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 0
6 | 0 | 8 | 0
And, want to achieve below data.
Child_Id | UnreadCount | AdultId | NewColumnVal
---------+-------------+---------+----------------
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
I want top value of NewColumnVal = 1 when for each distinct AdultId as shown above.
When for AdultId = 4 UnreadCount = 1 NewColumnVal =1 for first Child_Id only then all other subsequent values of NewColumnVal = 0 then again for new adult_id top value of NewColumnVal = 0 if UnreadCount = 0
sql
sql-server sql
sql-server edited Mar 8 at 7:20
marc_s
582k13011231269
asked Mar 8 at 6:36
GUNJAN ASWANIGUNJAN ASWANI
385
edited Mar 8 at 7:20
marc_s
582k13011231269
asked Mar 8 at 6:36
GUNJAN ASWANIGUNJAN ASWANI
385
edited Mar 8 at 7:20
marc_s
582k13011231269
edited Mar 8 at 7:20
marc_s
582k13011231269
edited Mar 8 at 7:20
marc_s
582k13011231269
582k13011231269
asked Mar 8 at 6:36
GUNJAN ASWANIGUNJAN ASWANI
385
asked Mar 8 at 6:36
GUNJAN ASWANIGUNJAN ASWANI
385
asked Mar 8 at 6:36
GUNJAN ASWANIGUNJAN ASWANI
385
385
Case statement will do the trick!
– Prashant Pimpale
Mar 8 at 6:43
add a comment |
Case statement will do the trick!
– Prashant Pimpale
Mar 8 at 6:43
Case statement will do the trick!
– Prashant Pimpale
Mar 8 at 6:43
Case statement will do the trick!
– Prashant Pimpale
Mar 8 at 6:43
add a comment |
1 Answer
1
active
oldest
votes
You can use window function together with Case Statement. see Below:
CREATE TABLE t (
Child_Id INT,
UnreadCount INT,
AdultId INT,
NewColumnVal INT
);
INSERT INTO t
(Child_Id, UnreadCount, AdultId, NewColumnVal)
VALUES
('28', '1', '4', '0'),
('29', '1', '4', '0'),
('28', '0', '5', '0'),
('29', '0', '5', '0'),
('5', '1', '6', '0'),
('6', '0', '8', '0');
GO
6 rows affected
select
Child_Id,
UnreadCount,
AdultId,
CASE WHEN UnreadCount > 0 AND rn = 1 THEN 1 ELSE 0 END NewColumnVal
from (
select *,
row_number() over (partition by AdultId order by Child_Id ) rn
from t
) tou
GO
Child_Id | UnreadCount | AdultId | NewColumnVal
-------: | ----------: | ------: | -----------:
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
db<>fiddle here
answered Mar 8 at 7:05
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.6k22042
Thanks Simonare for quick and easy solution. Can you provide explanation on your query as I am just beginner of SQL.
– GUNJAN ASWANI
Mar 8 at 7:23
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%2f55057930%2fsql-grouping-through-temptable-and-getting-new-column-value-if-one-of-the-column%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
You can use window function together with Case Statement. see Below:
CREATE TABLE t (
Child_Id INT,
UnreadCount INT,
AdultId INT,
NewColumnVal INT
);
INSERT INTO t
(Child_Id, UnreadCount, AdultId, NewColumnVal)
VALUES
('28', '1', '4', '0'),
('29', '1', '4', '0'),
('28', '0', '5', '0'),
('29', '0', '5', '0'),
('5', '1', '6', '0'),
('6', '0', '8', '0');
GO
6 rows affected
select
Child_Id,
UnreadCount,
AdultId,
CASE WHEN UnreadCount > 0 AND rn = 1 THEN 1 ELSE 0 END NewColumnVal
from (
select *,
row_number() over (partition by AdultId order by Child_Id ) rn
from t
) tou
GO
Child_Id | UnreadCount | AdultId | NewColumnVal
-------: | ----------: | ------: | -----------:
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
db<>fiddle here
answered Mar 8 at 7:05
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.6k22042
Thanks Simonare for quick and easy solution. Can you provide explanation on your query as I am just beginner of SQL.
– GUNJAN ASWANI
Mar 8 at 7:23
add a comment |
You can use window function together with Case Statement. see Below:
CREATE TABLE t (
Child_Id INT,
UnreadCount INT,
AdultId INT,
NewColumnVal INT
);
INSERT INTO t
(Child_Id, UnreadCount, AdultId, NewColumnVal)
VALUES
('28', '1', '4', '0'),
('29', '1', '4', '0'),
('28', '0', '5', '0'),
('29', '0', '5', '0'),
('5', '1', '6', '0'),
('6', '0', '8', '0');
GO
6 rows affected
select
Child_Id,
UnreadCount,
AdultId,
CASE WHEN UnreadCount > 0 AND rn = 1 THEN 1 ELSE 0 END NewColumnVal
from (
select *,
row_number() over (partition by AdultId order by Child_Id ) rn
from t
) tou
GO
Child_Id | UnreadCount | AdultId | NewColumnVal
-------: | ----------: | ------: | -----------:
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
db<>fiddle here
answered Mar 8 at 7:05
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.6k22042
Thanks Simonare for quick and easy solution. Can you provide explanation on your query as I am just beginner of SQL.
– GUNJAN ASWANI
Mar 8 at 7:23
add a comment |
You can use window function together with Case Statement. see Below:
CREATE TABLE t (
Child_Id INT,
UnreadCount INT,
AdultId INT,
NewColumnVal INT
);
INSERT INTO t
(Child_Id, UnreadCount, AdultId, NewColumnVal)
VALUES
('28', '1', '4', '0'),
('29', '1', '4', '0'),
('28', '0', '5', '0'),
('29', '0', '5', '0'),
('5', '1', '6', '0'),
('6', '0', '8', '0');
GO
6 rows affected
select
Child_Id,
UnreadCount,
AdultId,
CASE WHEN UnreadCount > 0 AND rn = 1 THEN 1 ELSE 0 END NewColumnVal
from (
select *,
row_number() over (partition by AdultId order by Child_Id ) rn
from t
) tou
GO
Child_Id | UnreadCount | AdultId | NewColumnVal
-------: | ----------: | ------: | -----------:
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
db<>fiddle here
answered Mar 8 at 7:05
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.6k22042
You can use window function together with Case Statement. see Below:
CREATE TABLE t (
Child_Id INT,
UnreadCount INT,
AdultId INT,
NewColumnVal INT
);
INSERT INTO t
(Child_Id, UnreadCount, AdultId, NewColumnVal)
VALUES
('28', '1', '4', '0'),
('29', '1', '4', '0'),
('28', '0', '5', '0'),
('29', '0', '5', '0'),
('5', '1', '6', '0'),
('6', '0', '8', '0');
GO
6 rows affected
select
Child_Id,
UnreadCount,
AdultId,
CASE WHEN UnreadCount > 0 AND rn = 1 THEN 1 ELSE 0 END NewColumnVal
from (
select *,
row_number() over (partition by AdultId order by Child_Id ) rn
from t
) tou
GO
Child_Id | UnreadCount | AdultId | NewColumnVal
-------: | ----------: | ------: | -----------:
28 | 1 | 4 | 1
29 | 1 | 4 | 0
28 | 0 | 5 | 0
29 | 0 | 5 | 0
5 | 1 | 6 | 1
6 | 0 | 8 | 0
db<>fiddle here
answered Mar 8 at 7:05
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.6k22042
answered Mar 8 at 7:05
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.6k22042
answered Mar 8 at 7:05
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.6k22042
answered Mar 8 at 7:05
Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu
15.6k22042
15.6k22042
Thanks Simonare for quick and easy solution. Can you provide explanation on your query as I am just beginner of SQL.
– GUNJAN ASWANI
Mar 8 at 7:23
add a comment |
Thanks Simonare for quick and easy solution. Can you provide explanation on your query as I am just beginner of SQL.
– GUNJAN ASWANI
Mar 8 at 7:23
Thanks Simonare for quick and easy solution. Can you provide explanation on your query as I am just beginner of SQL.
– GUNJAN ASWANI
Mar 8 at 7:23
Thanks Simonare for quick and easy solution. Can you provide explanation on your query as I am just beginner of SQL.
– GUNJAN ASWANI
Mar 8 at 7:23
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%2f55057930%2fsql-grouping-through-temptable-and-getting-new-column-value-if-one-of-the-column%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
Case statement will do the trick!
– Prashant Pimpale
Mar 8 at 6:43