npertuset

I'm having an SQL query issue. I think I need to self join, but I can't exactly figure out what to do. I have a table that keeps track of student exams. ID is the unique ID, studentno is the student ID number, moduleid is the course id, sitting is each time they took the exam (they can take an exam for a course as many times as they want) and grade is their grade. I just need to select the studentno, moduleid, sitting and grade for each student/course, but I only want to display the final sitting (See this for example: http://pastebin.com/X1ELnFa2)

like this? [attachment deleted by admin]

Ok. I'm trying to figure out the best/most optimized way to design my tables. I have multiple purchases go through my website. For each purchase, a percentage is given to different places (the user, company profit, and a bonus pool - more needs to be able to be added at any time). I want to keep track of all of this, be able to trace every transaction, pull up transactions at will and know the balance of each account (company profit and bonus pool will only have 1 account, but there could be 50-10,000 users). One way I've thought of is to create an "account type" for each user. IE: Table: Type_Of_Accounts (this table holds the option for different payable accounts) id (primary key - auto increment) name Examples: 1 / user 2 / company_profit 3 / bonus_pool Table: Transaction_Log (this table holds all transaction information.) id (primary key - auto increment) type_of_account_id (matches Type_Of_Accounts) account_id (if it's a user account, this field will hold the user id. if not, it will be set to 0). amount time Examples: 1 / 1 (user account) / 40 (user_id) / 59.99 / <time> 2 / 3 (bonus pool) / / 19.99 / <time> Table: balances (this would just update each time a transaction went through, this way I don't have to calculate it on the fly each time). id type_of_account_id account_id balance Examples: (using data from above) 1 / 1 / 40 / 59.99 2 / 2 / / 19.99 Here are some example queries: Get a users transaction log: select * from Transaction_Log where type_of_account_id='1' AND account_id='x' Get the transaction log for the bonus pool: select * from Transaction_Log where type_of_account_id='3' and account_id='0' To get the balance of the companies profit you could do: select balance from balances WHERE type_of_account_id='2' AND account_id='0' ----------------- What are your thoughts on this, and do you have a better way of designing this? I want it to be optimized/designed the best way possible.