DISTINCT help 1

[bmpsu]

I need a little help with this distinct query.

Table example:
------
ID CompanyName Region
1 South Inc. Northwest
2 USA Inc. Southwest
3 North Inc. Southeast
4 USA Inc. Southwest
------

I want to distinctly select on the CompanyName while selecting all of the columns above. I found that using Distinct will look at all columns selected in the sql statement, but I need to only select distinctly on companyname and grab other columns as well. In this situation, USA, Inc. will only show once in the results.

Life would probably be easier if the DB was normalized, but this is not the case or an option at the time.

I have messed around with sub-queries and it seems like this may be the way to go, but I cant seem to get it done.

Is this possible?

Thanks

 

[PHV]

USA, Inc. will only show once
And what is the criteria to select the desired row ?
If doesn't matter you may try this:
SELECT Max(ID) AS MaxID, CompanyName, Region
FROM yourTable
GROUP BY CompanyName, Region

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bmpsu]

I should have been more specific about the criteria.

Before I read your post I came up with this:
----------
SELECT DISTINCT CompanyName
FROM Survey
WHERE
EXISTS
((SELECT Id, Companyname, Region FROM survey)) AND (Region LIKE 'criteria%') AND (CompanyName LIKE 'criteria%')
----------

This worked fine, but I think I like your statement better because it will give me the most recent entry based off the MaxID. So I am thinking I might use this:
----------
SELECT MAX(ID) AS MaxID, CompanyName, Region
FROM Survey
WHERE (CompanyName LIKE 'criteria%') AND (Region LIKE 'criteria%')
GROUP BY CompanyName, Region
----------

Any thoughts on which one to use even though they both return the desired results?

Thanks

 

[bmpsu]

PHV -

Thanks for your help. I don't know if this is star worthy, but you got me out of a bind.

Thanks