DISTINCT only certain columns in on table and pull their IDs

Please help.? (I am using ASP, VB, SQL)

I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.

So for example I might have?

ID 1 - Office 1 - Chicago

ID 2 - Building 2 - Chicago

So I want to show

Office 1 - Chicago (my link will contain the ID in it)

So far I have

SELECT DISTINCT City , State FROM dbo.Offices WHERE (City IS NOT NULL) AND name like 'office%' ORDER BY City ASC

This works as far as pulling unique cities and names but now I need to get those City's ID. Can I use DISTICT for certain columns? Or how do I do it so that when it does pull out the unique cities it pulls their IDs as well?

?? Select ID and (DISTINCT City , State) FROM dbo.Offices ??

Thank you.

sscoder,

are you saying that the cities id is in another table. And are you just looking for the set of distinct city, state, id 's? is so just add id into your select statement

select distinct ID, City, State FROM Offices

if this is not what you are asking, can you include a bit more info...-- jp

|||City, State, ID are in the same table.

I want to find unqiue cities and then their IDs.?I'm using the ID in the hyperlink for a string.?

If I add ID to the distinct query?it will take priority and it will not list out unique cities. So instead of displaying

Chicago
Florida
Las Vegas

It will display this if I add ID to distinct
Chicago
Chicago
Chicago
Florida
Florida
Las Vegas

I need unique cities and then their ids.? Is there a way?

Thank you.

|||i see what you are saying but it you want the distinct city and a city may have more than 1 id, then how do you know which id you want to be returned?|||would only one of the city records be of type 'office'?|||there are many cities but each office has a different name. The every main office name start with 'Office' so I only want to pull up unique cities with name starting with 'Office%'. So there will never be two same cities. Only one office per city. That's where I put "WHERE name like 'office%' "

So the list will be:

Chicago - Office 1

Florida - Office 2

Las Vegas - Office 3

The query does this already. I just can't get the ID for that one row. That's what I need help on. If that "Office 1" in "Chicago" has an ID of 2. I want to get that. How??