April 30, 2009 at 10:22 pm
Hi,
I need help to flag a field (1/0 or true/False) in sql table based on the highest rank/ version in different column.
Here is the example:
Create table TestRank
(Field1 varchar(30),
Field2 varchar (30) )
Insert into TestRank (Field1, Field2)
Values ('Item1.1', '4.5')
Insert into TestRank (Field1, Field2)
Values ('Item1.2', '5.5')
Insert into TestRank (Field1, Field2)
Values ('Item2.1', '4.5')
Insert into TestRank (Field1, Field2)
Values ('Item2.2', '4')
I need to add a 3rd field (data type as Bit) and the expected result should be as below:
Field1 Field2 Field3
Item1.1 4.5 0
Item1.2 5.5 1
Item2.1 4.5 1
Item2.2 4 0
Note: Item1.1 and Item1.2 (in Field1)are of same category with different versions i.e 4.5 and 5.5 as given in Field2. Field3 is the Ranking. Item2.1 has the value ‘1’ in Field3 since it has higher value than item2.2
I am using SQL 2005.
Thanks
April 30, 2009 at 11:46 pm
One way of doing it is to create a user defined function that checks for each record if it has the highest value in filed2 according to field1. Then you can create a computed column and base it on the output of the user defined function. Here is a small example:
create function RankRow (@field1 varchar(30), @field2 varchar(30))
returns tinyint
as
begin
declare @Res tinyint
if exists (select *
from TestRank
where Field2 > @field2 and
left(Field1,charindex('.',Field1))=left(@field1,charindex('.',@field1)))
set @Res = 0
else
set @Res = 1
return(@Res)
end
go
alter table TestRank add IsMax as dbo.RankRow(Field1, Field2)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 1, 2009 at 5:17 am
Depends on the requirement. Adi's solutions is a good way to have a online ranking. If you just need this statement once you can also use the RANK function:
DECLARE @TestRank TABLE
(
Field1 varchar(30),
Field2 varchar (30),
Field3 BIT
)
INSERT INTO @TestRank (Field1, Field2)
SELECT 'Item1.1', '4.5'
UNION ALL SELECT 'Item1.2', '5.5'
UNION ALL SELECT 'Item2.1', '4.5'
UNION ALL SELECT 'Item2.2', '4'
; WITH
cte (VersionRank, Field3) AS
(
SELECT
RANK() OVER (PARTITION BY LEFT(Field1, CHARINDEX('.', Field1, 1)) ORDER BY Field2 DESC),
Field3
FROM @TestRank
)
UPDATE cte SET Field3 = CASE WHEN VersionRank = 1 THEN 1 ELSE 0 END
SELECT * FROM @TestRank
Greets
Flo
May 1, 2009 at 11:32 am
Thanks you both. Adi's suggestion worked for me.
May 1, 2009 at 10:43 pm
select field1 ,field2 ,(case when (rank() over (partition by LEFT(Field1, CHARINDEX('.', Field1, 1)) order by Field2 desc))=1 then 1
else 0 end)
from #TestRank
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply