CODEWITHSUNDEEP
phpmysqlsql
aero
aero

Reputation: 69

Mysql Query Selecting Results With A Distinct Column Value

I have a table that displays books. However I would like only only 1 book to be shown per unique email address (strContactEmail). I tried the below query, it didn't work. Any help greatly appreciated.`

$sql =  "SELECT lngbookid, strTitle, strAuthor, strcoursecode, strISBN, ".
        " strcontactname, DISTINCT(strContactEmail) AS strContactEmail, ".
        " curPrice, ysnsalerent, dtmpostdate, memcomments, school, ".
        " ASIN, BookIMG, ISBN10, ISBN13, Updated, ".
        " datetime, user_ip, NoOtherBooks ".
        " FROM tblbooks ".
        " WHERE strcoursecode LIKE '%$search%'  ".
        " ORDER BY datetime DESC LIMIT 50";

Upvotes: 0

Views: 148

Answers (2)

WojtusJ
WojtusJ

Reputation: 1318

The easiest way:

SELECT max(lngbookid) as lngbookid,
       max(strtitle) as strtitle,
       max(strauthor) as strauthor,
       max(strcoursecode) as strcoursecode,
       max(strisbn) as strisbn,
       max(strcontactname) as strcontactname,
       strcontactemail,
       max(curprice) as curprice,
       max(ysnsalerent) as ysnsalerent,
       max(dtmpostdate) as dtmpostdate,
       max(memcomments) as memcomments,
       max(school) as school,
       max(asin) as asin,
       max(bookimg) as bookimg,
       max(isbn10) as isbn10,
       max(isbn13) as isbn13,
       max(updated) as updated,
       max(datetime) as datetime,
       max(user_ip) as user_ip,
       max(nootherbooks) as nootherbooks
FROM   tblbooks
WHERE  strcoursecode LIKE '%$search%'
GROUP BY strcontactemail
ORDER  BY datetime DESC
LIMIT  50

EDIT Well, the above was actually too "dummy". Better way to do this is (providing that column "lngbookid" is a primary key):

SELECT a.lngbookid,
       a.strtitle,
       a.strauthor,
       a.strcoursecode,
       a.strisbn,
       a.strcontactname,
       a.strcontactemail,
       a.ontactemail,
       a.curprice,
       a.ysnsalerent,
       a.dtmpostdate,
       a.memcomments,
       a.school,
       a.asin,
       a.bookimg,
       a.isbn10,
       a.isbn13,
       a.updated,
       a.datetime,
       a.user_ip,
       a.nootherbooks
FROM   tblbooks AS a
       JOIN (SELECT strcontactemail,
                    Max(lngbookid) AS lngbookid
             FROM   tblbooks
             GROUP  BY strcontactemail) AS b
         ON ( a.strcontactemail = b.strcontactemail
              AND a.lngbookid = b.lngbookid )

Upvotes: 1

cjsissingh
cjsissingh

Reputation: 64

Try a GROUP BY statement:

http://www.w3schools.com/sql/sql_groupby.asp

Upvotes: 1

Related Questions