Order the rows based on variable value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Order the rows based on variable value

Author

Topic

bpuccha
Starting Member

34 Posts

Posted - 2012-05-28 : 13:42:39

Hi,

I have a table, contains the below rows

0001 001 1 ON -- row1
0001 001 2 BC ---row2
0001 001 3 QC ---row3
0001 001 4 QC ---row4

In a procedure first i have to process the above rows based on a variable called province.

if province = QC then i have to process 3rd and 4th rows first then i have to process 1st and 2nd rows.

How to write these things in a procedure to order the rows based on province?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:55:52

something like


SELECT *
FROM YourTable
ORDER BY CASE WHEN Province=@YourParameter THEN 0 ELSE 1 END,RowID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bpuccha
Starting Member

34 Posts

Posted - 2012-05-28 : 14:26:38

Thanks For th reply..Here is the total sinario

Table A
p_no t_no prov_A
0001 001 ON

Table B
p_no t_no r_no provi_a
0001 001 1 QC - 1st row
0001 001 2 ON - 2nd row
0001 001 3 BC - 3rd row
0001 001 4 QC - 4th row
0001 001 5 ON - 5th row

there are two diff types of laws

Civil law - Is for "QC" province
Comm law - For all other provinces

Based on Table A prov_A value we have to decide which rows we have to process first from Table B.
In this cases prov_A is ON, so first we have to process all the comm law rows from table B i.e
First we have to process 2,3,5 then 1 and 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 14:57:59

[code]
select b.*
from TableB b
left join TableA a
ON a.p_no = b.P_no
AND a.t_no = b.t_no
AND a.provi_a = b.provi_a
order by b.p_no,b.t_no,case when a.p_no is null then 1 else 0 end asc
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources