March 7, 2013 at 12:25 pm
CREATE TABLE dbo.#score
(
StudentID int IDENTITY (1,1) NOT NULL
, Math int
, science int
,History int
,Sports int
,Least_score int
);
-- Insert values into the table.
INSERT INTO dbo.#score (Math, science,History,Sports)
VALUES (5, 6,8,10), (3, 7,0,9)
select * from #score
I want to make a least_score as a computed column or by use of some trigger .So that the least_score column should get updated automatically
with the least value out of Math,Science,History and sports.In my case least_score for student_id=1 should be 5 and for student_id=2 it should be 0.
How we can do this?.
update #score
set Least_score=5
where StudentID=1
update #score
set Least_score=0
where StudentID=2
select * from #score
March 7, 2013 at 12:27 pm
Computed column, absolutely. No need for a trigger here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2013 at 12:56 pm
But ,How we can do with a computed column?.
March 7, 2013 at 1:53 pm
A little complex, but...
CREATE TABLE #score (
StudentID INT IDENTITY(1, 1)
NOT NULL ,
Math INT ,
Science INT ,
History INT ,
Sports INT ,
Least_score AS (CASE WHEN ( CASE WHEN ( CASE WHEN Math < science THEN Math
ELSE Science
END ) < History THEN ( CASE WHEN Math < science THEN Math
ELSE Science
END )
ELSE History
END ) < Sports THEN ( CASE WHEN ( CASE WHEN Math < Science THEN Math
ELSE Science
END ) < History THEN ( CASE WHEN Math < Science THEN Math
ELSE Science
END )
ELSE History
END )
ELSE Sports
END)
);
If you expect to be querying the table far more than modifying data, you can make the computed column persisted, that way it's calculated on insert/update only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2013 at 2:10 pm
This should work. Although it seems a little complicated for this.
drop table #score
go
CREATE TABLE dbo.#score
(
StudentID int IDENTITY (1,1) NOT NULL
, Math int
, science int
,History int
,Sports int
,Least_score as case when Math < Science and Math < History and Math < Sports then Math
when Science < Math and Science < History and Science < Sports then Science
when History < Math and History < Science and History < Sports then History
when Sports < Math and Sports < Science and Sports < History then Sports
end PERSISTED
);
-- Insert values into the table.
INSERT INTO dbo.#score (Math, science,History,Sports)
VALUES (5, 6,8,10), (3, 7,0,9)
select * from #score
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2013 at 2:11 pm
It seems that Gail beat me to it. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2013 at 2:25 pm
Thanks a lot !!
March 7, 2013 at 2:38 pm
Sean Lange (3/7/2013)
It seems that Gail beat me to it. 😀
I think yours is a bit simpler than mine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply