looping with SQL

Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFOYou don't need to loop. It would have been nice if you supplied exact DDL
and real INSERT statements. Here's an untested solution:
select
o.*
from
MyTable
where
o.id in
(
select top 1
i.id
from
MyTable i
where
i.Company = o.Company
order by
case
when i.Title like '%CEO%' then 1
when i.Title like '%CFO%' then 2
else 3
end
, o.id
)
It's not clear what you mean by the "first" title, so I used id as the
tiebreaker.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Logger" <Logger@.discussions.microsoft.com> wrote in message
news:635B49E6-0714-46FA-997D-B44B4F1F0085@.microsoft.com...
Hopefully this is a better explanation of what I tried to explain earlier.
Below is an example of a dataset I build as a #wkfile. It consists of a
count/identity, Company, AccountNo, and Title. I’m trying to loop thru wi
th
SQL to pull out one occurrence of Company Name and AccountNo, into a 2nd
dataset. When there are more than one occurrence, the priority is first -
CEO’ OR like ‘%CEO%’, second - ‘CFO’ or like ‘%CFO%’, and if n
either of them
then take the first title.
id Company AccountNo Title
1 national Publishers A2053145611 CEO
2 (i)STRUCTURE, Inc A5051045506 CFO
3 (i)STRUCTURE, Inc 99091268226 Chief Executive Officer
4 (i)STRUCTURE, Inc A3013151569 Finance Director/CFO
5 (i)STRUCTURE, Inc 99091970756 President
6 (i)STRUCTURE, Inc 99091204766 President/CEO
7 (i)STRUCTURE, Inc 99091268239 Vice President
8 (i)STRUCTURE, Inc A5051045492 VP Finance & Service Management
9 (i)STRUCTURE, Inc A5051045541 VP Infrastructure Operations
10 (i)STRUCTURE, Inc A5051045455 VP Marketing
11 (i)STRUCTURE, Inc A5051045467 VP Sales
12 Andel Amba A3013151463 Finance Director/CFO
13 @.Global Inc A1022852020 President
14 @.plan, Inc. 99113038170 Chairman/CEO
15 @.plan, Inc. 99113038390 President/COO
16 @.plan, Inc. A0080460716 Vice President/CFO/CAO
17 @.radical.media, Inc. A4102749756 Chairman & CEO
18 @.radical.media, Inc. A4102749802 COO
19 @.radical.media, Inc. A4102749784 EVP
20 @.Road, Inc. A4071235030 CFO