SQL Whizes, come on in

[dmurray14]

I need to make a query that is probably pretty easy, I just can't think how to do it. Basically I want to select distinct on one column, but return all the other column's info as well. If it helps, I have DB full of location information, with a city in one column, and two columns with latitude and longitude in the for that record. The lats and lons are the same for each city, but there are multiple records for each city. So I want to query the DB for the cities and their associated latitude and longitude, but I only want one of each. Make sense?

Thanks...

Dan

 

[junkerman123]

"Did I spell whizes right?"

No.

 

[LikeLinus]

Where's Nik to nef and increase his post count by saying you're posting in the wrong forums?

 

[tami]

y helo thar

whizzes.

select lat,lon from database where city="whatever";

maybe?

edit: i'm nik's little helper.

 

[blackdogdeek]

so there is a city column, a lat column and a long column?

and for each city, the correct lat and long exist?

and even though you want only one record returned for each city, there are multiple records per city, though the lat and long are the same for all of them?

if yes to all 3:

select distinct city, lat, long from locationTable

should work

whizzes

 

[cliftonite]

Originally posted by: dmurray14
I need to make a query that is probably pretty easy, I just can't think how to do it. Basically I want to select distinct on one column, but return all the other column's info as well. If it helps, I have DB full of location information, with a city in one column, and two columns with latitude and longitude in the for that record. The lats and lons are the same for each city, but there are multiple records for each city. So I want to query the DB for the cities and their associated latitude and longitude, but I only want one of each. Make sense?

Thanks...

Dan

Why would you have the same data repeated like that If city is the Primary Key, then are you even allowed to have multiple instances of it? Isnt this an inefficent design?

 

[yllus]

Sounds like you bought a zip code database off the Web like I recently did. You can try:

SELECT lat, lon FROM table WHERE city = 'CityName' LIMIT 1

That'll only return the first record the DB finds. This is probably not that useful though, considering that you probably want a JOIN in their to avoid multiple DB queries.

 

[nakedfrog]

Maybe use a SELECT MAX?

 

[bunker]

The lats and lons are the same for each city, but there are multiple records for each city

1. Shoot whoever designed this
2. Use blackdog's query
3. ........
4. Profit?

 

[torpid]

BlackDogDeek's answer is what I was thinking. Though I should note that this sounds like a VERY poorly designed table.

 

[dmurray14]

Originally posted by: blackdogdeek
so there is a city column, a lat column and a long column?

and for each city, the correct lat and long exist?

and even though you want only one record returned for each city, there are multiple records per city, though the lat and long are the same for all of them?

if yes to all 3:

select distinct city, lat, long from locationTable

should work

whizzes

Thanks - that kind of works, but I really need more than just the lat and long, although it is my main concern. Any way to get it all? IIRC, you can only have 5 distincts per query

 

[Gunslinger08]

http://forums.anandtech.com/messageview...atid=33&threadid=1608584&enterthread=y

 

[dmurray14]

Originally posted by: bunker

The lats and lons are the same for each city, but there are multiple records for each city

1. Shoot whoever designed this
2. Use blackdog's query
3. ........
4. Profit?

I designed it, and I don't want to shoot myself. There are reasons for it being like it is, but it is an internal site for my company and I cannot disclose too much. While the cities and assosicated lat/lons are the same, the rest of the information (quite a bit of it) is not. Due to the way the DB evolved, the lat & lot portion was somewhat of an afterthought.

 

[Gunslinger08]

A lot of membership databases use Lat/Long to display local events to their members. You can calculate distances based on them and only show upcoming events within 50 miles.

I don't know why anyone would care though.

 

[five40]

select distinct(City), Latitude, Longitude from SomeDumbCityTable

 

[dmurray14]

Originally posted by: joshsquall
http://forums.anandtech.com/messageview...atid=33&threadid=1608584&enterthread=y

Seems like you were looking for the same thing - which code did you go with? I am using mySQL, so the Oracle code probably wouldn't apply...

 

[torpid]

No one is doubting the need for lat and long, we are doubting the need for such a non-normalized table structure.

 

[dmurray14]

Originally posted by: torpid
No one is doubting the need for lat and long, we are doubting the need for such a non-normalized table structure.

OK, and what are you going to do about it? I have my reasons for doing what I have done, because it works for us.

 

[Gunslinger08]

Originally posted by: dmurray14

Originally posted by: joshsquall
http://forums.anandtech.com/messageview...atid=33&threadid=1608584&enterthread=y

Seems like you were looking for the same thing - which code did you go with? I am using mySQL, so the Oracle code probably wouldn't apply...

I ended up using the last one posted.

select a.*
from t_address a
where (a.chrCountry, a.rowid) in
(
select b.chrCountry, min(b.rowid)
from t_address b
where b.chrCountry not in (select nvcCountry from Country)
group by b.chrCountry)

The only command which might have an issue with mySQL is min, but that's supported by Transact-SQL, and I think mySQL supports it as well.

 

[torpid]

Originally posted by: dmurray14

Originally posted by: torpid
No one is doubting the need for lat and long, we are doubting the need for such a non-normalized table structure.

OK, and what are you going to do about it? I have my reasons for doing what I have done, because it works for us.

I'm going to post here and say that you might want to re-consider that table design. What are YOU going to do about it?

 

[isasir]

Originally posted by: dmurray14

Originally posted by: blackdogdeek
so there is a city column, a lat column and a long column?

and for each city, the correct lat and long exist?

and even though you want only one record returned for each city, there are multiple records per city, though the lat and long are the same for all of them?

if yes to all 3:

select distinct city, lat, long from locationTable

should work

whizzes

Thanks - that kind of works, but I really need more than just the lat and long, although it is my main concern. Any way to get it all? IIRC, you can only have 5 distincts per query

So why doesn't "distinct(city), all other variables you need" work? It should.

 

[dmurray14]

Originally posted by: joshsquall

Originally posted by: dmurray14

Originally posted by: joshsquall
http://forums.anandtech.com/messageview...atid=33&threadid=1608584&enterthread=y

Seems like you were looking for the same thing - which code did you go with? I am using mySQL, so the Oracle code probably wouldn't apply...

I ended up using the last one posted.

select a.*
from t_address a
where (a.chrCountry, a.rowid) in
(
select b.chrCountry, min(b.rowid)
from t_address b
where b.chrCountry not in (select nvcCountry from Country)
group by b.chrCountry)

The only command which might have an issue with mySQL is min, but that's supported by Transact-SQL, and I think mySQL supports it as well.

Thanks...I'll see if I can make it work.

 

[dmurray14]

Originally posted by: torpid

Originally posted by: dmurray14

Originally posted by: torpid
No one is doubting the need for lat and long, we are doubting the need for such a non-normalized table structure.

OK, and what are you going to do about it? I have my reasons for doing what I have done, because it works for us.

I'm going to post here and say that you might want to re-consider that table design. What are YOU going to do about it?

Nothing, because you don't send me a paycheck every week.

 

[dmurray14]

Originally posted by: isasir

Originally posted by: dmurray14

Originally posted by: blackdogdeek
so there is a city column, a lat column and a long column?

and for each city, the correct lat and long exist?

and even though you want only one record returned for each city, there are multiple records per city, though the lat and long are the same for all of them?

if yes to all 3:

select distinct city, lat, long from locationTable

should work

whizzes

Thanks - that kind of works, but I really need more than just the lat and long, although it is my main concern. Any way to get it all? IIRC, you can only have 5 distincts per query

So why doesn't "distinct(city), all other variables you need" work? It should.

Not sure, but it returns dupes for some reason...

 

[isasir]

Well obviously there's some column that has multiple different entries for the city. Lat and long work fine since it's the same for each city. You add a variable like, uh, gender, for example, and you'll start getting 2 results for each city. AFAIK, no way around that unless you get rid of that variable in the query.