SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting points minus lowest two entries

    So I'm trying to get a sum of fishing tournament points for each team in a club on my website, but I need to subtract the lowest two (2) entries for each team before displaying them. To complicate things, some teams may not fish all the tournaments in a given year, so I guess I would need to start by finding out using the current date how many tournaments there have been in the current year for the club (using my tournaments table) and how many tournaments have been fished in the current year by the team (using my results table). I would need to use a zero (0) for any "no shows". I'm really stuck.

    Here's an example of code I use for another club that throws away just the lowest score, and lucky for me, it can't be a zero (0), so I can just use min() here.

    Code:
    SELECT 
    	CONCAT(a1.fname, ' ',a1.lname, '/', a2.fname, ' ',a2.lname),
    	sum(points),
    	min(points),
    	sum(points)-min(points) as pnts
    FROM
    	anglers a1,
    	anglers a2,
    	teams t,
    	results r,
    	tournaments tt
    WHERE
    	t.id1=a1.anglerID AND
    	t.id2=a2.anglerID AND
    	t.teamID=r.teamID AND
    	t.clubid='$_REQUEST[cID]' AND
    	tt.tourid=r.tourid AND
    	YEAR(tt.date)='$_REQUEST[year]' AND
    	r.active=1
    GROUP BY
    	r.teamid
    ORDER BY
    	pnts DESC
    If anyone can help me with this, it would be greatly appreciated.

    Scott
    Last edited by EliteAngler; Jan 13, 2009 at 19:54.

  2. #2
    SitePoint Member
    Join Date
    Oct 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No help?
    OK, let's simplify it. What if I just wanted to remove the two lowest points? Lets say for a team I had 90, 100, 66, 87 and 100. How could I query the data, strip out the 66 and 87 and get a total of 290?

  3. #3
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    My guess is, you'll have to do it with PHP (or whatever language you use). Maybe I could get it done in SQL (I'm not sure), but it would be a very big and complicated query.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by EliteAngler View Post
    OK, let's simplify it. What if I just wanted to remove the two lowest points?
    Code:
    SELECT T.angler
         , SUM(T.points)
      FROM results AS T
     WHERE ( SELECT COUNT(*)
               FROM results
              WHERE angler = T.angler
                AND points < T.points ) > 1
    GROUP
        BY T.angler
    this throws away each angler's two lowest points before summing the rest

    you will have to test this thoroughly, to see what happens in the case of ties
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •