1

For some reason the following DISTINCT doesn't work. I was expecting only one b@gmail.com from Email column, but I get two identical ones.

 1> SELECT DISTINCT t.Email,t.Lat_From,t.Lon_From 
      FROM tbl_Trip t,tbl_User u
      WHERE t.Email like u.Email AND u.Member_Of like 'yahoo'
 2> go

  Email         Lat_From            Lon_From
  ------------- ------------------- -----------------------
  b@gmail.com   41.773289200000001  -71.378798500000002
  b@gmail.com   41.908398699999999  -71.436531000000002

(2 rows affected)

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
Bruce
  • 2,133
  • 12
  • 34
  • 52

4 Answers4

5

Those are the correct results. The latitude and longitude are different, and therefore the rows are DISTINCT.

If you want the email only once, which latitude and longitude do you think should be reported for that email?

Also, because you don't have any wildcards in 'yahoo', you know that it's the same as using the = operator?

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
3

Distinct is applied to the whole resultset row, not to the field it was specified right before.

So in your case you get unique rows of t.Email,t.Lat_From,t.Lon

zerkms
  • 249,484
  • 69
  • 436
  • 539
2

if you want it to be distinct according to a column you need to add a group by clause.

SELECT DISTINCT t.Email
     , t.Lat_From
     , t.Lon_From
FROM tbl_Trip t,tbl_User u
WHERE t.Email like u.Ema il
AND u.Member_Of like 'yahoo'
GROUP BY t.Email

You will need to use an order by clause if you want to get a specific entry.

regality
  • 6,496
  • 6
  • 29
  • 26
  • This **doesn't** work in SQL Server. MySQL only, an aberration. – RichardTheKiwi Jan 25 '11 at 03:54
  • Which latitude and longitude value do you think this will return? – Larry Lustig Jan 25 '11 at 03:55
  • @cyberwiki: I don't think it's an aberration, it appears to be a carefully considered and (if used _carefully_) useful extension. But it is unique to MySQL. – Larry Lustig Jan 25 '11 at 03:59
  • I don't have an SQL server to test it on, but the docs on microsoft.com says it will work. If it has the same behavior as MySQL it will return the first row that matches, so order it so the one you want comes first. – regality Jan 25 '11 at 04:01
  • @regality: just tried `SELECT SupplierID, LastName, FirstName FROM People GROUP BY SupplierID;` and received the error message `Column 'People.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` SQLExpress 2008 R2. – Larry Lustig Jan 25 '11 at 04:20
  • Curses to everything made by microsoft. Try adding the other columns to the group by clause: SELECT SupplierID, LastName, FirstName FROM People GROUP BY SupplierID, LastName, FirstName; You have to do that or put them in an aggregate function such as sum() or max(), but that obviously won't work very well with strings. – regality Jan 25 '11 at 04:30
2

For SQL Server 2005 onwards, this should work for you if you only want one indiscriminately

SELECT Email,Lat_From,Lon_From
FROM
(
    SELECT t.Email,t.Lat_From,t.Lon_From, rn=row_number() over(
        partition by t.Email order by t.Lat_From)
      FROM tbl_Trip t,tbl_User u
      WHERE t.Email like u.Email AND u.Member_Of like 'yahoo'
) X
WHERE rn=1

Change order by t.lat_from to whatever order based on which you want the singular-record-per-email to be retained.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262