CODEWITHSUNDEEP
phpmysql
Patrioticcow
Patrioticcow

Reputation: 27058

mysql, php, how to compare numbers

i have a database: database with a table: table and some fields:

id  fname       dphone      count_pic   dup_id  

6055903 Karla       5126xxx798  1       57  
6173767 Aaliyah     4082xxx534  4       39  
5611411 Aaliyah     4082xxx534  15      39  
5611211 Aaliyah     4082xxx534  18      39  
4234798 Abby        3057xxx974  31      16  
6166691 Walter      6178xxx280  1       74  
3375576 Walter      6178xxx280  17      74

what i am trying to do is to select the fields that have the smallest count_pic and the ones that have the bigger count_pic and that have the same dup_id

any ideas how to do this in mysql?

thanks.

Upvotes: 0

Views: 393

Answers (1)

zerkms
zerkms

Reputation: 255155

With this query you'll select the smallest and the biggest values of count_pic for every dup_id

  SELECT MIN(count_pic) AS minpic,
         MAX(count_pic) AS maxpic,
         dup_id
    FROM `table`
GROUP BY dup_id

If you also need corresponding rows, then you could use something like

    SELECT *
      FROM `table` t1
INNER JOIN (SELECT MIN(count_pic) AS minpic,
                   MAX(count_pic) AS maxpic,
                   dup_id
              FROM `table`
          GROUP BY dup_id) t2 ON t1.dup_id = t2.dup_id
                             AND (t1.count_pic = minpic
                               OR t1.count_pic = maxpic)

Upvotes: 5

Related Questions