Home Forums SQL Server 2014 Development - SQL Server 2014 Distinct of two columns? RE: Distinct of two columns?

  • December 19, 2017 at 10:25 am

    #1972739

    tacy.highland - Tuesday, December 19, 2017 9:43 AM

    Drew.allen - I think that might be it!

    I dumped the CASE data into a temp table and added a SELECT DISTINCT ADDRESS1, ADDRESS2 to get the distinct list and I think this works.  I used this query against the sample data I posted and it returns the same address sets as my ExpectedResults table.

    I never would have thought to use < to compare.

    Here's another challenge on this, should you all choose to accept it.....

    What if there were additional columns in the table, such as City, Lat, and Lon for each record?  So expand the original table from TripID, Pickup_Address, Dropoff_Address to TripID, Pickup_Address, PickupCity, Pickup_State, Pickup_Lat, Pickup_Lon, Dropoff_Address, Dropoff_City, Dropoff_State, Dropoff_Lat, Dropoff_Lon.

    I can't imaging that the < compare would still work here, would it?

    Yes, it does.  You just have to make sure that you are using the same field(s) for all of the comparisons.  Since you have latitude and longitude, I would be inclined to use those, since they would be less subject to variability.  That is, the same address can be given several different ways, and it might be hard to determine that they are actually the same, but a latitude and longitude will always be the same within a certain degree of accuracy.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA