CODEWITHSUNDEEP
sqlsql-server
Joshua Deshazer
Joshua Deshazer

Reputation: 193

Trying to get a row that has the lat and long from SQL Server

I have some data that I am trying select everyone with a good lat long and also the ones that do not have a lat long below is my data:

enter image description here

The reason why some of the data has a lat and long of 1 and 0 is because their orders came into the erp system before they shipped and we are wanting to see the radius of these orders in a map. So we want the facts to go into the fact table even though we may or may not have a lat or long for this customer.

Basically if I have a customer like josh Deshazer and he has 2 duplicate rows because he came into the system without a lat and long eventually he got a lat and long on his record I want to only select the record with the good lat and long, Then if I have a customer like mike davis even though he doesn't have a good lat and long I still want to select him all in the query

Essentially I would want josh Deshazer 14.5 and the 12.2 and the ken johnson with the -93 and 45 only and the mike davis with the 1 and 0 all in one query. Thank you anyone for your help open to any suggestions this one is killing me.

Upvotes: 0

Views: 86

Answers (2)

Vash
Vash

Reputation: 1787

Try this: 

(SELECT long, Lat, FirstName, LastName, OrderAmount, OrderNumber    
FROM
your_table
WHERE OrderNumber IN (SELECT OrderNumber FROM your_table GROUP BY OrderNumber HAVING COUNT(*) > 1)
AND (long <> 1 AND Lat <> 0))   --Conditions in this line can be tweaked based on how your definition of bad lat/long works
UNION ALL   
(SELECT long, Lat, FirstName, LastName, OrderAmount, OrderNumber    
FROM
your_table
WHERE OrderNumber IN (SELECT OrderNumber FROM your_table GROUP BY OrderNumber HAVING COUNT(*) = 1));

Upvotes: 0

Alfaiz Ahmed
Alfaiz Ahmed

Reputation: 1728

you can Achieve This By Using ROW_NUMBER FUNCTION

SELECT
       Long,Lat,FirstName,lastName,OrderNumber FROM
    (
        SELECT Long,Lat,FirstName,lastName,OrderNumber,
            ROW_NUMBER() OVER (PARTITION BY Long,Lat ORDER BY Long,Lat  ) rn
        FROM #TMP 
    ) t
    WHERE t.rn = 1
    ORDER BY FirstName,lastName

Check THe Image Shows you Desire Outpt You Are looking for

Upvotes: 1

Related Questions