Sum the top 2 records
-
I have this table UserAttributes UserAttributeId int identity (1,1) not null AttributeId int not null UserId int not null AttributeValue default (1) not null Attribute Id is a FK to a table of attributes and user Id is a FK to the table of users. I am trying to create a trigger that whenever AttributeValue is changed that the sum of the two highest attributes is loaded into a field in the Users table. I would like it to be one statement (so I don't have to iterate a cursor), but I'm stuck on the syntax. Any ideas?
Broken Bokken You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod http://www.brokenbokken.com
-
I have this table UserAttributes UserAttributeId int identity (1,1) not null AttributeId int not null UserId int not null AttributeValue default (1) not null Attribute Id is a FK to a table of attributes and user Id is a FK to the table of users. I am trying to create a trigger that whenever AttributeValue is changed that the sum of the two highest attributes is loaded into a field in the Users table. I would like it to be one statement (so I don't have to iterate a cursor), but I'm stuck on the syntax. Any ideas?
Broken Bokken You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod http://www.brokenbokken.com
Hi I am not clear with your question, do you want to get two maximum values from a column without using cursor? Please go thro' the following SQL Script, I am not sure whether you are looking for this solution:
USE northwind DECLARE @MaxStock SMALLINT DECLARE @NextMaxStock SMALLINT SELECT @MaxStock = MAX(UnitsInStock) FROM PRODUCTS SELECT @MaxStock SELECT @NextMaxStock = MAX(UnitsInStock) FROM PRODUCTS WHERE UnitsInStock < @MaxStock SELECT @NextMaxStock SELECT @MaxStock + @NextMaxStockHope this is clear.Harini
-
I have this table UserAttributes UserAttributeId int identity (1,1) not null AttributeId int not null UserId int not null AttributeValue default (1) not null Attribute Id is a FK to a table of attributes and user Id is a FK to the table of users. I am trying to create a trigger that whenever AttributeValue is changed that the sum of the two highest attributes is loaded into a field in the Users table. I would like it to be one statement (so I don't have to iterate a cursor), but I'm stuck on the syntax. Any ideas?
Broken Bokken You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod http://www.brokenbokken.com
-
You could try something like:
Select Sum(AttributeValue) From ( Select top 2 AttributeValue From UserAttributes Order by AttributeValue desc )Wout Louwers
This was exactly what I was trying to do. Thanks!
Broken Bokken You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod http://www.brokenbokken.com