remove duplicate records from sql query's result

Question

SELECT 
          Distinct  loc.name,             
             loc.latitude, 
             loc.longitude, 
            loc.imageurl,           
             c.userid, 
             c.locationid, 
             c.time,
             (SELECT Count(*)   FROM   tbl_likecheckin  WHERE  checkinid = c.id) AS TOTALCheckin,
            (SELECT Count(*) FROM   tbl_likecheckin  WHERE  userid = 57 AND checkinid =  c.id) As Checkinflag, 
             CONVERT(DECIMAL(16,2),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144))) 
             AS 
             Distance 
      FROM   tblcheckin AS C 
             INNER JOIN tbluser AS u 
                     ON c.userid = u.userid 
             INNER JOIN tblgetlocation AS loc 
                     ON c.locationid = loc.venueid 
      WHERE  c.flag = 'C'    
      and   CONVERT(DECIMAL(16,4),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144)))<=(select maxdistance from tblDistance)
         ORDER  BY c.time DESC

enter image description here

Here is the output of my above query, i have applied distinct but till the data are duplicated because of imageurl column
so how can i remove duplicated record from result no matter which duplicated row is removed from result.
I just want a single record.I need imageurl also.

possible duplicate of [remove duplicate records by specific id in sql server 2008](http://stackoverflow.com/questions/20219194/remove-duplicate-records-by-specific-id-in-sql-server-2008) — Lamak, Nov 27 '13 at 13:36
This is a duplicate of your own previous question. And this version still doesn't clarify the criteria to choose which row to delete and which row to keep — Lamak, Nov 27 '13 at 13:37
You might not got answers for your previous question, but you should not duplicate the question. Read [How do I get attention for old, unanswered questions?](http://meta.stackexchange.com/questions/7046/how-do-i-get-attention-for-old-unanswered-questions) — huMpty duMpty, Nov 27 '13 at 13:48
http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group — JsonStatham, Nov 27 '13 at 14:32
@Lamak In my output, u can see userid=218 and it's longitude, latitude, locationid,userid fields it's couple of time but, imageurl is single time, so i want any one of them. it's possible to count column with where 'longitude, latitude, locationid, userid, name', these field. because these all fields are same, except imageurl and time. — Pratham4950, Nov 29 '13 at 10:25

score 1 · Answer 1 · edited May 23 '17 at 11:53

1

See this : http://www.w3schools.com/sql/sql_distinct.asp

Ur using DISTINCT over all the columns that's why.
If the "imageurl" column is creating trouble for you take it out of the DISTINCT keyword's scope.

This could be useful : SQL - Select distinct but return all columns?

Example here : http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_distinct
Run this query:

SELECT DISTINCT Country,City FROM Customers;

and this one:

SELECT Country,City FROM Customers Group By Country;

edited May 23 '17 at 11:53

Community

1
1

answered Nov 27 '13 at 13:58

Sahil Sareen

1,813
3
25
40

this works for me to remove duplicate results ,thank you so much – Amila Thennakoon Aug 14 '17 at 03:52

score 1 · Answer 2 · answered Nov 27 '13 at 14:21

Difficult to answer this without having the data to play with, something along these lines. The left join should mean that the original CTE result set stays the same and hopefully will just grab the first imageurl match it finds keeping the number of records the same.

with cte as (
SELECT 
Distinct  loc.name,             
loc.latitude, 
loc.longitude, 
c.userid, 
c.locationid, 
c.time,
(SELECT Count(*)   FROM   tbl_likecheckin  WHERE  checkinid = c.id) AS TOTALCheckin,
(SELECT Count(*) FROM   tbl_likecheckin  WHERE  userid = 57 AND checkinid =  c.id) As Checkinflag, 
CONVERT(DECIMAL(16,2),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144))) 
AS 
Distance 
FROM   tblcheckin AS C 
INNER JOIN tbluser AS u 
ON c.userid = u.userid 
INNER JOIN tblgetlocation AS loc 
ON c.locationid = loc.venueid 
WHERE  c.flag = 'C'    
and CONVERT(DECIMAL(16,4),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144)))<=(select maxdistance from tblDistance)
)
select cte.name, cte.latitude, cte.longitude, cte.userid, cte.locationid, cte.[time], cte.Distance, imagetable.imageurl
from cte
left outer join (SELECT 
Distinct 
loc.name,
loc.imageurl,
FROM   tblcheckin AS C 
INNER JOIN tbluser AS u 
ON c.userid = u.userid 
INNER JOIN tblgetlocation AS loc 
ON c.locationid = loc.venueid 
WHERE  c.flag = 'C'    
and CONVERT(DECIMAL(16,4),(dbo.Distancebetween(loc.latitude, loc.longitude, 22.302647, 73.190144)))<=(select maxdistance from tblDistance)) imagetable
on cte.name = imagetable.name

remove duplicate records from sql query's result

2 Answers2