Selecting Top 2 Records 2

[bxgti4x4]

I recently started thread 701-894603 in which I posed the following question:

I have a table named TblPresident containing the following three fields:

LngCompetitorNo
TxtUnit
Total

"Total" is the sum of the scores from two competitions.

For each TxtUnit there are four Totals, one for each LngCompetitorNo. I need to create a query which selects the lowest two scores only for each unit.

Amorous (Programmer) replied on 5 Aug 04 12:01
Try this:

SELECT t1.*
FROM TblPresident t1
WHERE t1.Total In (select top 2 t2.Total from TblPresident t2 where t2.TxtUnit = t1.TxtUnit ORDER BY t2.Total Asc);

This code worked well but, having now entered a load of scores, I find that there are times when the query returns three results for each TxtUnit because two competitors have the same total score. I have modified the table to include the scores for each competition as well as the total so the fields in the table are now:

LngCompetitorNo
TxtUnit
Total
Roupell
Whitehead

What I need is for the query to select the lowest two scores for each unit as before but to eliminate duplicate Total scores by sorting against first, the highest Whitehead score, and next, the highest Roupell score.

Can anyone please help me to sort this one out.

Thanks a lot
John

 

[ZmrAbdulla]

This should work
[tt]
SELECT t1.*
FROM TblPresident t1
WHERE t1.Total In (select distinct top 2 t2.Total from TblPresident t2 where t2.TxtUnit = t1.TxtUnit ORDER BY t2.Total Desc);
[/tt]

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[bxgti4x4]

Thanks ZmrAbdulla,

I'm afraid this code picks up the top two scores and not the lowest two. If I change the sort to "Asc" I get back to where I was originally, with the query selecting three competitors for one of the units, two of which have a Total score of 45 and the other having a score of 31. Of the two who have the total of 45 one has a Whitehead total of 40 and the other has a Whitehead score of 36. I need to eliminate the one with the Whitehead score of 36 i.e. select the one with the highest Whitehead score.

Best Regards
John

 

[ZmrAbdulla]

I think here is the answer by PHV thread701-1021115

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[bxgti4x4]

Thanks ZmrAbdulla, but I'm afraid I can't see how this fits into the existing code. Would appreciate a little more help if possible.

The other thought I have come with is to run a Totals query on TblPresident to sort out the duplicates and then run a delete query to remove them. Then I can run the original query to select the top 2.

Any thoughts?

Best Regards
John

 

[PHV]

You may try this (typed, untested)
SELECT A.*
FROM TblPresident AS A INNER JOIN (
SELECT TOP 2 B.Total, B.Whitehead, B.Roupell, B.LngCompetitorNo, B.TxtUnit
FROM TblPresident AS B WHERE B.TxtUnit = A.TxtUnit
ORDER BY B.Total ASC, B.Whitehead DESC, B.Roupell DESC
) AS C ON A.TxtUnit = C.TxtUnit AND A.LngCompetitorNo = C.LngCompetitorNo;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bxgti4x4]

PHV,

Sorry,this did not work; when I ran the code the programme asked me for TxtUnit. However, please don't spend any more time on this thread as I have found a better way to resolve the original problem by using a total query followed by a delete query. This sorts out the duplicates in the underlying table and deletes the ones I don't need. I can then run the original code and select the top 2.

Thanks to all who contributed to this thread, your efforts were much appreciated.

Best Regards
John