Mysql query task

Question

I have a problem with my mysql query.

My database:

sections id section_name grades id user_id section_id date grade 

I want my data displayed as follows:

Section_class

But I want the rating to be closest to today's date ... This is what I have, but it does not show the last class. instead he orders id (I think)

 SELECT * FROM grades, sections WHERE sections.id = grades.section_id AND grades.user_id = '.$id.' GROUP BY grades.section_id ORDER BY grades.date DESC 

EDIT: the variable $ id is the identifier of the user from the session.

Answer 1

My solution involves using a subquery to get the identifier of the last class, and then pass it into the query, which then joins both tables and score tables to return the partition_name and class. Please consider this as psuedocode than a valid sql query, as I don't have time to test it. May come back later to edit it.

SELECT section_name, grade FROM sections, grades WHERE sections.id = grades.id AND grades.id = (SELECT id FROM grades WHERE section_id = '$Id' ORDER by date DESC LIMIT 1)

Answer 2

I would request a query based on the specific user_id you need and find the maximum date for each section. Then re-join the sections and classes (now that this preliminary query will extremely limit the result set for the union), then get the section name and, finally, a suitable grade for the date corresponding to the particular student (user), taking the course.

 select STRAIGHT_JOIN PreQuery.Section_ID, Sections.section_name, PreQuery.LastDatePerSection from ( select section_id, user_id, max( date ) as LastDatePerSection from grades where user_id = YourUserIDParameter group by section_id ) PreQuery join sections on PreQuery.Section_ID = Sections.ID join grades on PreQuery.Section_ID = grades.Section_ID AND PreQuery.User_ID = grades.User_ID AND PreQuery.LastDatePerSection = grades.Date 

Answer 3

If I get this right, you want to get the last class per section. This cannot be done simply with MySQL.

You will need a window function or sequence generator, none of which are available in MySQL. However, there is a workaround using variables. See this question for a similar use case:

MySQL query: using UNION and getting row number as part of SELECT

Further background information here:

SQL / mysql - select single / UNIQUE, but return all columns?

What you're most likely to end up with is a large subquery, which will look like this:

 select t.* from (select @rownum := @rownum + 1 rownum, t.* from (select * from grades join sections on sections.id = grades.section_id where user_id=$id order by grades.date desc ) t, (select @rownum := 0) r ) t where t.rownum = 1 

Answer 4

I recently had to do something similar, but with T-SQL, so you have to change it yourself to make it work in MySQL (which I did not install here, so I can not check you. I'm afraid).

Add to WHERE clause:

 AND grades.date = (SELECT TOP 1 date FROM grades WHERE date > GETDATE() ORDER BY ABS(CONVERT(FLOAT,GETDATE() - date))) 

GETDATE() equivalent to NOW() , TOP 1 is similar to LIMIT 1 , and other functions, well, I don't know the MySQL equivalents from the top of my head!

Answer 5

If I understand correctly:

 SELECT grades.*,sections.* FROM grades INNER JOIN sections ON sections.id = grades.section_id WHERE user_id=$id ORDER BY grades.date DESC LIMIT 1 

Answer 6

 SELECT sections.section_name, grades.grade FROM grades, sections WHERE sections.id = grades.section_id AND grades.user_id = '.$id.' ORDER BY grades.date DESC LIMIT 1 

try it?

Answer 7

 SELECT grades.*,sections.* FROM grades inner join sections on grades.sections_id = sections.id where grades.user_id = '.$id.' GROUP BY grades.section_id ORDER BY grades.date DESC 

you can use this, it works 100%

Answer 8

Suppose we are looking for user ratings with id = 1 (just to test a request without php). You can replace it with "$ Id" later.

If the date column is accurate enough to make sure it is unique for each class you can use:

 SELECT s.section_name, g1.grade FROM ( SELECT g.section_id, max(g.date) AS last_grade_date FROM grades g WHERE g.user_id = 1 GROUP BY g.section_id ) gd JOIN grades g1 ON g1.date = gd.last_grade_date JOIN sections s ON s.id = gd.section_id 

If your date is not unique, you need to join the classes back to yourself by the identifier found in the dependent subquery:

 SELECT s.section_name, ga.grade FROM ( SELECT g1.section_id, max(g1.date) AS last_grade_date FROM grades g1 WHERE g1.user_id = 1 GROUP BY g1.section_id ) gmax JOIN grades ga ON ga.id = ( SELECT g2.id FROM grades g2 WHERE g2.user_id = 1 AND g2.section_id = gmax.section_id AND g2.date = gmax.last_grade_date LIMIT 1 ) JOIN sections s ON s.id = gmax.section_id 

This query requires an index (user_id, section_id, date).

 ALTER TABLE grades ADD INDEX user_section_date( user_id, section_id, date ) ; 

@comment: Well, I’ll try to explain the second query, since it gives the correct results for any case.

In table g1, we take the rows from the ratings for the user with id = 1, group them into sections and in each section we find the maximum date, which means the latest date. At the moment, we still do not know exactly which line contains the most recent date, because we can select only the columns that are in the group by or aggregate functions (for example, max ()). Mysql allows you to select other columns, such as a class, called hidden columns (other dbs just throw a syntax error), but can return any row from each group, usually not the one we need, and we want the date. If all lines have the same value, for example user_id, this is normal, but we need ratings that can be different in each group. For small tables, the selection may return the correct one, so some people may require ordering by date, they may help, because they check it on small tables and see the correct results, but it doesn’t comply with the rules, when the table grows, row updates appear, are deleted, etc. .d. on the.

Basically we have a list of sections and the most recent dates, and we need to know the ratings. Therefore, we need to join this table g1, which we just got in the grading table, to find the row that contains the most recent date for each section. An identifier is the only column that will undoubtedly be unique (if we do not join a unique column or a unique list of columns, we get more rows and we want exactly one), so we try to find this id in the dependent subquery g2 (its subquery, which refers to values ​​from outside, from gmax in this case, which is just an alias for g1, explained earlier).

As soon as we have an estimate for each section, it remains only to join this table in section_id to get the section_name, and not its id value.