Ranking

Question

Post reply

Ranking

pmathema1

SSC Enthusiast

Points: 151
More actions
April 30, 2009 at 10:22 pm

#95133

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/

Florian Reischl SSC-Dedicated Points: 37299 More actions · Answer 2

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