January 17, 2006 at 11:53 am
I'm suffering from a mental bloc on how to rank a certain resultset with more than 1 field. Thanks in advance
CREATE TABLE TORANK
( Customer int NOT NULL,SortStart int NOT NULL, SortEnd int NOT NULL)
INSERT INTO TORANK SELECT 100,100,500
INSERT INTO TORANK SELECT 100,501,600
INSERT INTO TORANK SELECT 200,100,400
INSERT INTO TORANK SELECT 200,401,500
INSERT INTO TORANK SELECT 200,501,600
How to I get the resulset with the rank column?
Wanted:
RANK CUSTOMER START END
1 100 100 500
2 100 501 600
3 200 100 400
4 200 401 500
5 200 501 600
Sorting happens in the following order customer, start, end
*edit problem solved*
SELECT A.CUSTOMER,A.SortStart,A.SortEnd ,count(*)
FROM TORANK A
INNER JOIN TORANK B
on (A.CUSTOMER>B.CUSTOMER)
OR (A.CUSTOMER=B.CUSTOMER AND A.SortStart>=B.SortStart)
OR (A.CUSTOMER=B.CUSTOMER AND A.SortStart=B.SortStart AND A.SortEnd >=B.SortEnd )
group by A.CUSTOMER,A.SortStart,A.SortEnd
January 17, 2006 at 3:24 pm
You may order by your fields, output into temp table with Identity property:
create table ##Ranked ( Rank int IDENTITY, Customer int NOT NULL,SortStart int NOT NULL, SortEnd int NOT NULL)
GO
Insert Into ##Ranked
select customer, SortStart, SortEnd from torank
order by customer, SortStart, SortEnd
select * from ##Ranked
drop table ##Ranked
Regards,Yelena Varsha
January 18, 2006 at 12:33 am
my select was still bugyy
Thanks for the solution!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply