Remove duplicate pairs
Posted on 2004-11-22
I have a table of street name intersections (street1, street2, x_coord, y_coord, fdname1, fdname2) - here is the current query...
select distinct *
WHERE STREET1 IS NOT NULL AND STREET2 IS NOT NULL
order by street1 asc, street2 asc
This works great to populate two drop down lists (using cf_jselect) but it includes duplicate pairs. I believe this is because the x and y coords are not identical for each pair (they are a little bit different) so they have multiple listings in the original table. I have to include the coords in the select because they are crucial to the map update after selection of the correct pair.
Here is the sql that creates the view...
CREATE VIEW dbo.VIEW_XSTREETS AS
FDPRE1 + ' ' + FDNAME1 + ' ' + FDTYPE1 AS STREET1,
FDPRE2 + ' ' + FDNAME2 + ' ' + FDTYPE2 AS STREET2,
WHERE (FDNAME1 IS NOT NULL) AND (FDNAME2 IS NOT NULL)
Question - How can I (any or all would help)...
1) change the original query that creates the view to remove duplicate combinations of the street1 + street2? or
2) Change my select query to filter them on the user end?
3) I am also interested in filtering the name pairs based on a comparison of coordinates - like select any unique pair of streets, but include duplicates if the coordinates are more than plus/minus 50 different (indicating it might actually be a significantly different location).
Thanks a million in advance!
I have been farting around with this for a couple days but I am kind of new to this stuff and have run out of time.
I really need this solved today (although I think it is probably fairly simple for an experienced sql person...)
- so higher points were assigned due to the urgency.