October 27, 2009 at 2:56 pm
From the table below I am trying to:
SELECT [id], [origin_latitude], [origin_longitude], [remote_latitude], [remote_longitude], [distance_miles] FROM [towergrid_reference_natowers]
WHERE
/* Not SQL, just a descriptiont */
Select the lowest distance, grouped by [remote_latitude] & [remote_longitude]
Table create statement because I was told this is the best way to get good answer.
============================================
CREATE TABLE [towergrid_reference_natowers] (
[id] [bigint] IDENTITY (1, 1) NOT NULL ,
[origin_latitude] [float] NOT NULL ,
[origin_longitude] [float] NOT NULL ,
[remote_latitude] [float] NOT NULL ,
[remote_longitude] [float] NOT NULL ,
[distance_miles] [float] NOT NULL ,
[bearing] [float] NULL ,
CONSTRAINT [PK_towergrid_reference_natowers] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
============================================
Thanks for any help.
October 27, 2009 at 4:33 pm
I came up with this but I am unsure if it is actually returning what I expect.
SELECT TOP 100 PERCENT towergrid_reference_natowers_1.origin_latitude, towergrid_reference_natowers_1.origin_longitude,
wpadmin.towergrid_reference_natowers.remote_latitude, wpadmin.towergrid_reference_natowers.remote_longitude,
MIN(wpadmin.towergrid_reference_natowers.distance_miles) AS distance
FROM wpadmin.towergrid_reference_natowers LEFT OUTER JOIN
wpadmin.towergrid_reference_natowers towergrid_reference_natowers_1 ON
wpadmin.towergrid_reference_natowers.id = towergrid_reference_natowers_1.id
GROUP BY wpadmin.towergrid_reference_natowers.remote_longitude, wpadmin.towergrid_reference_natowers.remote_latitude,
towergrid_reference_natowers_1.origin_latitude, towergrid_reference_natowers_1.origin_longitude
ORDER BY wpadmin.towergrid_reference_natowers.remote_latitude, wpadmin.towergrid_reference_natowers.remote_longitude
October 27, 2009 at 6:08 pm
SELECT [id], [origin_latitude], [origin_longitude], [remote_latitude], [remote_longitude], [distance_miles]
FROM [towergrid_reference_natowers] T
INNER JOIN (select MIN([distance_miles]) min_distance
from [towergrid_reference_natowers]
) M ON M.min_distance = [distance_miles]
_____________
Code for TallyGenerator
October 27, 2009 at 6:46 pm
Yeh, that only returns a single row
October 27, 2009 at 9:04 pm
You should know better which values to use for grouping:
SELECT [id], [origin_latitude], [origin_longitude], [remote_latitude], [remote_longitude], [distance_miles]
FROM [towergrid_reference_natowers] T
INNER JOIN (select [remote_latitude], [remote_longitude], MIN([distance_miles]) min_distance
from [towergrid_reference_natowers]
group by [remote_latitude], [remote_longitude]
) M ON M.[remote_latitude] = T.[remote_latitude] AND M.[remote_longitude] = T.[remote_longitude] AND M.min_distance = [distance_miles]
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply