I am trying to select records based on a precedence. I'm using SQL2005.
These records are address records for our students and employees. Each
person will have, at least, one address, but many people will have more
than one address.
The addresses are coded so that we know the type of address: H = Home,
LOC = Local, RMA = Room Assignment.
I want to pull only one record based on a precedence. For example, if I
find an RMA record, then I want to pull that and no other records. If
there is no RMA record, I need to find a LOC record, if one exists.
Repeat as needed until I find that last address type.
The precedence is:
1 - RMA
2 - LOC
3 - H
Here is the first step to find the address type in the order I need:
SELECT DISTINCT TOP 1
pseason.id
,pseason.addr_type
,pseason.person_addresses
,CASE
WHEN pseason.addr_type = 'H' THEN 1
WHEN pseason.addr_type = 'AM' THEN 2
WHEN pseason.addr_type = 'ISIR' THEN 3
ELSE 999
END AS sortOrder
FROM
PSeason
WHERE
pseason.id = '0000133'
ORDER BY
sortOrder
Is there a better way to select these records?
Thanks
M!ke
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7.
Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282493
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
