Joining a table on itself

October 3, 2008

select  RTRIM(LTRIM(rl.ric)) 'Identifier',
        case
            when (rl.code like '%46%' ) THEN 'True'
            when (rl.code like '%47%' ) THEN 'True'
            ELSE 'False' END D1,
        case
            when (rl.hard_restriction = 1 AND rl.hard_to_borrow=0) THEN 'Restricted'
            else '' end 'EventType'
from restricted_list rl_1
    inner join (select id, max(entry_date) as MaxEntryDate
                from restricted_list rl_2
                group by ric) rl_2
        on  rl_1.ric = rl_2.ric
        and rl_1.entry_date = rl_2.MaxEntryDate
order by rl_1.ric
Advertisements

Another useful bit of SQL

August 27, 2008

 The problem was to take this data

RIC Visibility
BMWG.DE 1
BMWG.DE 0
CONG.DE 0

 and return a distinct list of RICs, with the lowest possible Visibility value. 
The SQL needed was

select distinct Ric,Visibility from Table
group by  Ric
having Visibility = min(Visibility)

Another quick tip: I used to get this functionality for free when I used an SQL plugin for Emacs years ago.  I wanted to use it again and had a quick think about how it must have been done; it’s trivial really, but I don’t have a huge background with SQL transactions.  Anyway, this controls your update statement so that it only affects one row (or as many rows as you specify) and rolls back if the rows affected is greater.  This fixes the worst nightmare of “250,000 rows affected” when you were expecting “1 row affected”, and it’s in a production system, and you don’t have a backup.

begin tran x

update Table set Value='NewValue' where Name='Key'
if @@rowcount > 1
begin
print 'error'
rollback tran x
end 
else
begin
commit tran x
end