SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Next bigger value?

    Hi all,
    Assume a MySQL table with columns for name, eye colour and weight. Given a specific name, say Bob, I'd like to see what Bob's eye colour is (easy), then select everyone with the same eye colour (also easy). Then comes the hard part: I also know Bob's weight (still easy), and I would like to know the next biggest value for individuals with the same eye colour - and that I'm finding very hard to implement... So the question would be "Who is the individual that, having the same eye colour, has a weight closest to Bob's, but higher?"... I've started writing a lot of PHP loops to query the table, but I feel that I'm missing something obvious and that there's a simple and elegant solution to the problem... Any bright ideas out there? Many thanks.

  2. #2
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Regina, SK
    Posts
    318
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT name,weight
    FROM people
    WHERE eye_colour='$bobs_eye_colour'
    AND weight > $bobs_weight
    ORDER BY weight DESC
    LIMIT 1
    Reduce or eliminate spam on your sites without CAPTHCAs
    - maybe it is possible: http://formantispam.thekerrs.ca/

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select p.name, p.weight
    from people p
    join people s on
     s.eye_colour = p.eye_colour
     and s.name = 'bob'
    where p.weight > s.weight
    limit 1
    the difference between my query and nos's query is that you don't need to already know bob's eye color and weight, just that you want bob to be your baseline.

  4. #4
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I knew I was making things unnecessarily difficult... It must be the time of the year, the phase of the moon, whatever... Thanks again.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •