Click here to Skip to main content
14,389,928 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,

Any info on how to sort and rank 4 columns at the same time.

I have 4 columns
Select Salary, TransAllowance, HouseAllw, Cash from Payroll
1st column should be asc - the first will get number 1 pts
2nd column should be asc - 1 first will get number 1 pts
2 will get 2 pnts
last will get the last number of recound cnt
3rd column should be desc - opposite of 1 and 2 columns
4th column should be asc - same as 1st column


Result Record 1 1,000 (1pt) 500 (1pts) 2,000 (1 pts)
Record 2 1,500 (2pt) 800 (2pts) 1,000 (2 pts)

Thanks
Dabuskol
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I found the answer. You have to use the RANK()
Example below:

select originator,holdtime,hubtime,
       RankHubTime = ROW_NUMBER() OVER(ORDER BY hubtime ASC),
       RankHoldTime = ROW_NUMBER() OVER(ORDER BY holdtime ASC),
 from #tmp1


Result:
Col1    Col2    Col3           col4    col5<br />
Test1	304925	59674601	1	2<br />
Test2	353072	62857242	2	4<br />
Test3	490172	24462340	4	3<br />
Test4	975875	15261822	3	4


Thanks
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100