Row filtering 1

[samcam]

Hi Experts...

I have a table with the following structure

OfficeName Address
x1 x1a1
x1 x1a2
x2 x2a1
x3 x3a1
x4 x4a1
x5 x5a1
x5 x5a2
x6 x6a1

Now you can see that some offices have more than on address. I want to extract only the a1 addresses for all the offices. How do I filter those.

Would really appreciate your input and help.

 

[Golom]

Try
[blue][tt]
Select OfficeName, Address
From tbl
Where Address LIKE %a1%
[/tt][/blue]

 

[samcam]

Ok...may by I didn't publish my question right.

Here is how the table is

OfficeName address
x1 abcd
x2 efgh
x3 1234
x4 5678
x5 ijkl
x5 mnop

now how do I get only one address for all rows. Some offices have more than one address but all offices have atleast one address. So I want to get only one address for each office. If they have more than one address I only want the first one.
Really appreciate your input.

 

[MDXer]

do you have an identity column?


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[samcam]

Yes there is an identity column which we call objid and this is unique for every row.

 

[Golom]

You want at least one address but you don't care which one?
[blue][tt]
Select OfficeName, FIRST(Address)
From tbl
GROUP BY OfficeName
ORDER BY OfficeName, Address
[/tt][/blue]

 

[samcam]

Yes I do care which row. The identity should be greater. Is there a function called "First

 

[MDXer]

If you want the first address for a given office you might try

Select 
OfficeName,
Address
From tbl t1
Where objid = (select Min(objid) 
               from tbl where OfficeName = ti.OfficeName)

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck