How to use SELECT DISTINCT as part of a larger query?

I have a table that is poorly designed and I want to replace it with a couple of other tables, but first I have to extract data.

Here are some of the columns of the table:

Location_id, Item_id, Location, Description

The Item_id refers to objects (from another table) that are stored in a location in the location table (location being a room number in a building).

The way it is now, there are multiple repeated values of Location and Description in the table.

I would like to extract Location, Description from that table into a view but I also need to have the view retain one of the Location_id values (it's not important which one, I could use the MINIMUM), but so far I haven't been able to do that.

I can do this:  select distinct location, description but how do I add the minimum location_id into my result set?

Should I be using group by instead of distinct?

Thanks for your help.
gateguardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Faiga DiegelSr Database EngineerCommented:
SELECT DISTINCT location, description, MIN(location_id)
FROM yourtable
GROUP BY location, description
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
knightEknightCommented:
select min(Location_id) as Location_id, location, description
from yourTable
group by location, description
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.