Arithan

Using MAX(badge_id) works for the badge_id, but what I want to show is the badge_name and badge_description. I could use another query to select all the rows with those badge id's after selecting them, but it doesn't seem like an efficient way to do it since it would have to loop through and select each row one by one that match the badge id's. For scaleability, it would take the page a long time to load if there are a lot of rows.

The trigger column in the badges table could be used to rank the badges, or even the badge id itself since they are in ascending order. However, my original idea was to use the badge_user table with a join to the badges table to only show the most recent earned badge in each of the 3 categories for a specific user on their profile page. I tried grouping the badge_type_id column and sorting by the date (in the badges_user table) in descending order but it would only return the first of each type instead of the last. Here are some examples of what I have in the badges and badges_user tables: badges badge_id badge_type_id badge_name badge_description trigger 1 1 Newbie Uploaded 1 file 1 2 1 Contributor Uploaded 10 files 10 3 2 Likeable 10 ratings received 10 4 2 Popular 50 ratings received 50 5 3 Hot 10 downloads received 10 6 3 Burning 100 downloads received 100 badges_user badge_user_id badge_id user_id date 1 1 1 dates stored using datetime 2 2 1 3 1 2 4 5 3 5 3 4 6 4 4 So what I'm trying to do is to use the badge_type_id column in the badges table to determine the badge type, and the date column in the badges_user table to determine the lastest badge earned for each type for a specific user.

I'm working on creating a badge/achievement system for a website but am having problems trying to only show the highest badge for a specific type earned. Currently, I can use the following query to have it show all badges earned for a user: SELECT b.badge_name, b.badge_description FROM badges AS b INNER JOIN badges_user AS bu USING (badge_id) WHERE bu.user_id=$id $id is the id of the user. There are 3 different type of badges total and 10 badges for each type. I want it only show the highest badge earned for each type. Badges are earned after a certain number of uploads, ratings, and downloads. Here are the 3 MySQL tables involved: badges badge_id badge_type_id badge_name badge_description trigger badges_user badge_user_id badge_id user_id date badges_progress badge_progress_id user_id rank uploads ratings downloads The badges table has all 30 badges that can be earned as well as the number threshold (trigger) needed to earn the badge. The badges_user table has all the users that have earned badges. The badges_progress table keeps track of the progress of users. So what I'm trying to do is to create a SQL query that will only show the highest badge earned for each of the 3 types (uploads, ratings, downloads) for a specific user. I tried using: SELECT badges.badge_name, badges.badge_description FROM badges, badges_user WHERE badges.badge_id=badges_user.badge_id AND badges_user.user_id=$id GROUP BY badges.badge_type_id but it only shows the first one of each type instead of the lastest one. Putting a ORBER BY badges_user.date DESC didn't do anything. After doing some research online and in the book about joins, I found this from http://stackoverflow...d-in-each-group: SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id) WHERE m2.id IS NULL but it couldn't figure out how to modify it to work with all 3 of my tables. I managed to get it to show the highest possible badge for each type only if the user has earned it with the following query, but for those who haven't earned the highest, it shows nothing: SELECT b1.badge_name, b1.badge_description FROM badges AS b1 INNER JOIN badges_user AS bu ON (bu.badge_id=b1.badge_id) LEFT JOIN badges AS b2 ON (b1.badge_type_id = b2.badge_type_id AND b1.badge_id < b2.badge_id) WHERE b2.badge_id IS NULL AND bu.user_id=$id I just want it to show the highest badge for each type that a specific user has earned. Sorry if something is unclear, it was hard trying to explain everything. Thanks in advance for the help. Apache: 2.2.22 PHP: 5.2.17 MySQL: 5.1.61-community-log