using DISTINCT

adamhw · July 2nd, 2010, 09:27 AM

Hi there

I have a number of identical values in the rows of one of my tables in my database

How do I only pull one of those out using DISTINCT?

This is my SQL but it doesn't seem to be working - there's no error but it doesn't just pull out 1 record for each card, it returns the whole lot still.

SELECT DISTINCT tbl_WebserviceLog.cardNumber, tbl_Club.gym, tbl_Account.clubid, tbl_Account.Name, tbl_Account.Surname, tbl_WebserviceLog.LogID, tbl_WebserviceLog.AccountID, tbl_WebserviceLog.Dated, tbl_WebserviceLog.credit, tbl_WebserviceLog.Type, tbl_WebserviceLog.returnVal FROM tbl_WebserviceLog, tbl_Account, tbl_Club WHERE tbl_WebserviceLog.AccountID = '521' AND tbl_WebserviceLog.AccountID = tbl_Account.AccountID AND tbl_Club.gymid = tbl_Account.clubid GROUP BY tbl_WebserviceLog.cardNumber,tbl_Club.gym, tbl_Account.clubid, tbl_Account.Name, tbl_Account.Surname, tbl_WebserviceLog.LogID, tbl_WebserviceLog.AccountID, tbl_WebserviceLog.Dated, tbl_WebserviceLog.credit, tbl_WebserviceLog.Type, tbl_WebserviceLog.returnVal ORDER BY tbl_WebserviceLog.Dated

Any help greatly appreciated.

thanks

Adam

Old Pedant · July 2nd, 2010, 07:48 PM

SELECT DISTINCT will remove all ENTIRELY DUPLICATED rows. So if even ONE FIELD in your list of fields has even ONE CHARACTER difference between rows, then all rows will be returned.

Generally speaking, this means that if your SELECT list includes ID fields that are primary keys in any table except the first one, you *will* get duplicates.

Anyway, you should be able to just look carefully at the data being returned and find the (likely minor) differences yourself. If any field shows different values in different records, then you can *NOT* include that field in SELECT DISTINCT.

*************

Comment: Often, what you want isn't really SELECT DISTINCT. Often what you really want is the first (or last or...) record in a group where you get to select the characteristics of the group. If there is only one field that differs amongst the entire group, then instead of using SELECT DISTINCT, you can use MIN() or MAX() and GROUP BY. Example:

Code:

SELECT U.name, U.email, MAX(P.postingDate) AS lastPostingDate
FROM users AS U, postings AS P
WHERE U.userid = P.userid
GROUP BY U.name, U.email

But in more complex situations, you might not even be able to do that.

I won't bore you with methods you won't need, but if you think you need somethiing more advanced/complex, ask again.