SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Order the rows based on variable value		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bpuccha
Starting Member

29 Posts
Posted - 05/28/2012 :  13:42:39  Show Profile  Reply with Quote
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

India
48044 Posts
Posted - 05/28/2012 :  13:55:52  Show Profile  Reply with Quote
something like

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bpuccha
Starting Member

29 Posts
Posted - 05/28/2012 :  14:26:38  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48044 Posts
Posted - 05/28/2012 :  14:57:59  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000