# Thread: Getting points minus lowest two entries

1. ## 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

2. 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. 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. Originally Posted by EliteAngler
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

#### Posting Permissions

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