| Author |
 Topic  |
|
|
GhorZonia
New Member
Finland
60 Posts |
 Posted - 02 August 2001 : 17:09:08
|
My database table contains a big bunch of fields. Two of them are Points and Member.
I need to build a TOP 3 Points list on one my pages from these two fields. That is no problem, I could just use a simple "Select top 3 Points, Member from table order by points desc" statement. The problem is that members can have several records in the database. I want to build the list so that there are three different members in it even if one member has the three records with the highest points.
I don't know how to explain it so here is a example:
MemberA's points:
1300
1250
1200
MemberB's points:
1090
980
900
MemberC's points
1130
1010
900
So the TOP 3 list should look like this:
MemberA - 1300 points
MemberC - 1130 points
MemberB - 1090 points
Not like this:
MemberA - 1300 points
MemberA - 1250 points
MemberA - 1200 points
The question is: how should the SQL statement look like if I want to do this?
P.S. I hope you understand what I'm trying to do because I really don't know how to explain it better  |
|
|
Cyber Paladin
New Member
55 Posts |
Posted - 02 August 2001 : 21:14:04
|
It would help to know how your database is set up... If each members points is stored in it's own table then you'll have to do a SQL Join and compare them all. If you have a whole table consisting of a row for each point total like this:
Member A - 2250
Member B - 1290
Member A - 3405
Member C - 2780
etc...
Then you need to order by point titles using a distinct statement in the SQL.
The last kind I can figure on is having a members table with each member in their own row. Having the points stored with them. Like this: (or maybe just one total per person)
Member A - 2250 - 2830 - 2810
Member B - 2100 - 1420 - 3480
Member C - 1250 - 3230 - 1910
etc...
For that one I'd have to really get into it to be able to order it like that.
By far I'd say the easiest would be the second way I memtioned. But, if you post how your doing it I'll try to give my help. :)
|
 |
|
|
GhorZonia
New Member
Finland
60 Posts |
Posted - 03 August 2001 : 03:18:14
|
Thanks for your interest Cyber Paladin!
My table consists of user submitted records. Users can submit their own records after they have registered. That's why I have another members table which stores user info. That table has theese fields: ID (autoincrement), Username, Password, Registered (when they have registered, date and time) and a few other not so important fields (homepage etc).
The records table holds all of the records. One row for each record. And the member field holds the ID of the member who submitted the record.
The records table is basically set up like this:
Record_ID - MemberID - points - submitted - other info etc.
And the records look like this
1 - 5 - 1070 - 6/10/2001 10:00:00 - other not so important info
2 - 74 - 1105 - 6/11/2001 12:05:00 - other not so important info
3 - 41 - 960 - 6/14/2001 04:42:00 - other not so important info
4 - 5 - 1198 - 6/18/2001 18:09:00 - other not so important info
5 - 5 - 989 - 6/21/2001 23:03:00 - other not so important info
6 - 23 - 1090 - 6/24/2001 14:30:00 - other not so important info
As you can see, a member can have several records.
quote:
Then you need to order by point titles using a distinct statement in the SQL.
Could you show me how you do that?
Thanks!
Edited by - GhorZonia on 03 August 2001 03:19:06 |
|
|
|
GhorZonia
New Member
Finland
60 Posts |
Posted - 03 August 2001 : 03:53:45
|
I did a search on SELECT DISTINCT and I found a few good links.
The problem with SELECT DISTINCT seems to be that you can only select one field in that table with it. If I SELECT DISTINCT MemberID then I don't get their points. |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 03 August 2001 : 05:29:42
|
You may then use the for next loop to just retain the 3 unique members with highest individual points.
Select MemberID, Points from TableName order By Points Desc
for i = 0 to rs.recordcount
if counter = 0 then
PointsA = rs("Points")
MemberA = rs("MemberID")
counter = counter + 1
end if
if not rs("MemberID") = MemberA then
if counter = 1
PointsB = rs("Points")
MemberB = rs("MemberID")
counter = counter + 1
end if
end if
if rs("MemberID") = MemberA or rs("MemberID") = MemberB then
counter = counter
else
if counter = 2 then
PointsC = rs("Points")
MemberC = rs("MemberID")
counter = counter + 1
i = rs.recordcount
end if
end if
next
Since the query is getting the records in descending order by points, it will hit the second member immeiately after the first memberid changes and third member and points immediately after the second memberid changes.
Share A Square at forumSquare
gauravbhabu
There is only one miracle...That is LIFE!
Edited by - gauravbhabu on 03 August 2001 05:35:59 |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 03 August 2001 : 06:38:18
|
you could try something like
select member_id,max(POINTS) from TABLE group by member_id
|
|
|
|
GhorZonia
New Member
Finland
60 Posts |
Posted - 03 August 2001 : 06:59:40
|
GauravBhabu, thanks, it works (although you had forgotten the rs.movenext in the code). I'll use this code if I don't come up with a more simple solution.
HuwR, I need also the record_ID of the records. When I use this
Select Record_ID, Member_ID, Max(Points) from RECORDS Group by Member_ID
it gives me this error:
You tried to execute a query that does not include the specified expression 'Record_ID' as part of an aggregate function. |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 03 August 2001 : 07:38:13
|
Post it here , If you find a better solution. Else You have the Points.
Share A Square at forumSquare
gauravbhabu
There is only one miracle...That is LIFE! |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 03 August 2001 : 07:48:07
|
Okay you may use this sql
SELECT MemberID, Max(Points) AS maxPoints, First(RecordID) AS recID
FROM Tablename
GROUP BY MemberID
ORDER BY Max(points) DESC;
oops!! The record ID will be incorrect.
Share A Square at forumSquare
gauravbhabu
There is only one miracle...That is LIFE!
Edited by - gauravbhabu on 03 August 2001 08:00:20
Edited by - gauravbhabu on 03 August 2001 08:00:52 |
|
|
| |
Topic |
|
Topic Locked
