So I'm doing this in MySQL. I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their columns.  students student_id | name | email | GPA  classes class_id | name | room  student_class_rel student_id | class_id  I want to get a student_id of the 5 students for each class with the highest GPAs (along with the class info). How would I do this? For example, this query gets 100 classes and then gets every student_id for all students in that class SELECT *,      JSON_ARRAYAGG(student_id)  FROM classes      INNER JOIN student_class_rel      USING(class_id)  GROUP BY class_id;  LIMIT 100  I want that query, expect the JSON_ARRAYAGG(student_id) only holds the ID to the top 5 students in terms of GPA for each class rather than every student. Also, assume there could be upwards of millions of students and hundreds of thousands of classes and that there are around 50 to 1000 students per class. So performance is key for this query. And I'm aware of hacky type solutions (like make multiple queries, once for each class) but so far those have been WAY too slow. I've spent too much time trying to figure this out, any help is greatly appreciated! (Also let me know if there's any additional info needed).
120689
I am trying to figure out how I can output any machine that has had the latest 3 backups be larger than 10GB. My SQL Table looks similar to the below table:  ComputerID | BackupImageName | WindowsVolumeGUID | FileSize | TimeCreated 1234       | PCBackup.spi    | kjsdfai-dadad-ddd | 10400    | 05-22-20 1234       | PCBackup1.spi   | kjsdfai-dadad-ddd | 10500    | 05-23-20 1234       | PCBackup2.spi   | kjsdfai-dadad-ddd | 10300    | 05-24-20 1234       | PCBackup3.spi   | kjsdfai-dadad-ddd | 10900    | 05-25-20 1225       | PCBackup4.spi   | kjsdfa5-dadad-ddd | 10600    | 05-22-20 1225       | PCBackup5.spi   | kjsdfa5-dadad-ddd | 5000     | 05-23-20 1225       | PCBackup6.spi   | kjsdfa5-dadad-ddd | 10000    | 05-24-20 1225       | PCBackup7.spi   | kjsdfa5-dadad-ddd | 4588     | 05-25-20   I need to output all machines (computerID's) or Volumes (WindowsVolumeGUID) that have their latest 3 files in the list greater than 10000 file size. So from the above table it would output the computerID of 1234 because its latest 3 are greater than 10000 file size and even though the computerID 1225 has 2 files greater than 10000 it would not output the result because there is a break in-between the two large files. The search will need to take into account the WindowsVolumeGUID since some machines have more than one volume and I need to take into account the individual volume backups and count the latest entries based on that.  Alternatively, I can also accomplish the same thing if there is a way to output the top 3 results for each unique WindowsVolumeGUID and not just across the board.   Any help would be greatly appreciated!
102621
Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the parent_id column this is a good and normalized technique i agree but there is a disadvantage also, it’s slow and inefficient. This is mainly caused by the recursion like for each parent we have to run the query again and again to make a tree  SELECT id FROM `table` WHERE parent_id=something   I have looked at the solutions some might try to do it with any programming language by running query again and again which makes a loads on server , Some have provided the stored procedure but that also involves the recursion.  So my question is can we do it with one database query for the tree(joins or subqueries) if we know the depth or if we don't know if it is possible so how can we get the top most parent(i.e parent_id=0) of any child if it is not possible then why this technique is so famous while it has the flaws or we have another solution for this? . i have added the sql fiddle but it only has the schema
69885
"Group" is a group of pupils. "Topic" is a topic of lessons.  To every group corresponds exactly one topic. (So there may be multiple groups per topic.)  To a pupil corresponds multiple groups, but maximum one group per topic.  How to implement this in terms of UNIQUE indexes and foreign keys in MySQL?
74456
I want to write a query like this:  SELECT o.OrderId, MAX(o.NegotiatedPrice, o.SuggestedPrice) FROM Order o   But this isn't how the MAX function works, right?  It is an aggregate function so it expects a single parameter and then returns the MAX of all rows.     Does anyone know how to do it my way?
56163
I have dates in 2 tables. Table student is as below:   ID|Name|Examdate 1  John 01/10/2015 2  Fred 11/12/2014   Table retake as below:   ID|RetakeDate 1  02/10/2016 2  05/11/2015   The IDs in retake relate to the students' IDs in student, i.e. they are the same.  I am trying to create another table where only students who have taken a retake after 12 months or more exist. The difference between examdate and RetakeDate needs to be >= 12months.  How do I achieve this?  There could be multiple rows in both tables for the same student. There will be multiple rows on both tables, as this will allow me to see how many times they have taken a retake and how many times an exam. At this moment in time I don't want to see if the retake is for the latest exam. I just want to see how many retakes they have taken all together. End result should be comparing each Exam date with Retake date. Some more explanation:     Q: So  if a student has dates in 2012, 2013, 2014 and then 2 retakes in 2016?       A: There should 3 rows in the result (one for each date)      Q: What if it's three exams – in 2012, 2013, 2014 – and (a) two retakes – in 2014 and 2016? or (b) one retake – in 2014? What should be expected as the result in each of those cases?          A: It doesn't matter. As long as the (a) retake was taken after 12 months or more, they should appear in the result.
115603
How to find Accounts with no child Accounts? I tried Aggregate Query but its not returning proper result and inner query isn't supported for Self-Relation. Thoughts?
43671
I have written below inner join query to get the data from my database tables. But  output of this data is coming up with almost 140+ lines. Output is below. I need data in only one row with the most recent value by "TimeSampled"    select DisplayName, ObjectName, CounterName, InstanceName, SampleValue, max(TimeSampled) as TimeSampled from PerformanceDataAllView pdv with (NOLOCK) inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId where ObjectName = 'Processor'  AND TimeSampled &gt;= DATEADD(MINUTE,-5,GETDATE()) AND DisplayName Like 'BLRW1ETST01.jcp.com' AND CounterName = '% Processor Time' GROUP BY DisplayName, ObjectName, CounterName, InstanceName, SampleValue ORDER BY  TimeSampled desc   How I can retrive results with only last Time Sampled row with respect to DisplayName. I have multiple servers (DisplayName) like this to retrieve the data.  Thanks, Ramu Chittiprolu
33666
I am running a query like   select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';   on table that looks like                                      Table "public.students"           Column       |            Type             |             Modifiers                   -------------------+-----------------------------+------------------------------------      id                | uuid                        | not null default gen_random_uuid()      school_id        | uuid                        |  Indexes:     "students_pkey" PRIMARY KEY, btree (id)     "students_school_id_idx" btree (school_id)   The query plan for the select statement with just where looks like below-  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d';                                             QUERY PLAN                                             --------------------------------------------------------------------------------------------------  Bitmap Heap Scan on students  (cost=581.83..83357.10 rows=24954 width=16)    Recheck Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)    -&gt;  Bitmap Index Scan on students_school_id_idx  (cost=0.00..575.59 rows=24954 width=0)          Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)   This is fairly fast.  Now we add order by to the query with id that degrades the query.(Such a query is generated by Rails like student.first with some condition)  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                  QUERY PLAN                                                  ------------------------------------------------------------------------------------------------------------  Limit  (cost=0.43..488.51 rows=1 width=16)    -&gt;  Index Scan using students_pkey on students  (cost=0.43..12179370.22 rows=24954 width=16)          Filter: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)   How can I improve the speed to return the results of this query? Currently there are around 4990731 records in the table and is taking more than 2 minutes! Its running on RDS with db.t2.medium instance.  UPDATE After running Analyze students;  explain select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                        QUERY PLAN                                                         -----------------------------------------------------------------------------------------------------------------      Limit  (cost=8.46..8.46 rows=1 width=16)        -&gt;  Sort  (cost=8.46..8.46 rows=1 width=16)              Sort Key: id              -&gt;  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16)                    Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)      explain analyze select id from students where school_id='67153fb1-8f79-441d-a747-ca3778cf6d3d' order by id asc limit 1;                                                                           QUERY PLAN                                                                              -----------------------------------------------------------------------------------------------------------------------------------------------------------     Limit  (cost=8.46..8.46 rows=1 width=16) (actual time=1.853..1.855 rows=1 loops=1)      -&gt;  Sort  (cost=8.46..8.46 rows=1 width=16) (actual time=1.851..1.852 rows=1 loops=1)            Sort Key: id            Sort Method: quicksort  Memory: 25kB            -&gt;  Index Scan using students_school_id_idx on students  (cost=0.43..8.45 rows=1 width=16) (actual time=1.841..1.843 rows=1 loops=1)                  Index Cond: (school_id = '67153fb1-8f79-441d-a747-ca3778cf6d3d'::uuid)     Planning time: 0.145 ms     Execution time: 1.874 ms
118129
Given database of the website "eMovies" that manage data about cinema movies.      Write in SQL query the most common genre (that have the biggest number of movies)   The tables:  Actors      ActorID      PK     Firstname      LastName  Directors      DirectorID   PK     Firstname      LastName  Genres      GenreID      PK     GenreDescr   Movies      MovieID      PK     MovieName      Year  ActorsMovies      MovieID      PK     ActorID      PK   DirectorsMovies      MovieID      PK     DirectorID   PK   GenresMovies      MovieID      PK     GenreID      PK    My attempt:   SELECT GenresDescr  FROM GenresMovies  NATURAL JOIN Genres  WHERE GenresMovies.GenresID=Genres.GenresID  GROUP BY GenreDescr  HAVING  MAX(COUNT(GenresMovies.MovieID));   I'm stuck, my code does not work.       EDIT: attampt number 2 after using @Darwin von Corax hint:     SELECT Genres.GenreDescr,COUNT(GenresMovies.GenreID) AS MCount   FROM GenresMovies,Genres   WHERE GenresMovies.GenreID = Genres.GenreID   GROUP BY GenresMovies.GenreID,Genres.GenreDescr   HAVING MCount=MAX(MCount);      But still the code does not work
6875
I am trying to find some database tutorials,books and similar, since I have started this course in college, and received some material, but it is really hard to manage through all the info on the net. I searched through the questions on the site but didn't find any. Thankfull for all your input.
55154
I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry.  I use recent versions of PostgreSQL and MySQL. I have 2 tables:  CREATE TABLE authors (     id INT,     name VARCHAR )  CREATE TABLE posts (     id INT,     author_id INT,     text VARCHAR,     date DATE )   I need to select one most recent post for each author. Thanks!  UPDATE  Thanks, both links provide answers to my question with some exception. All the following queries give the same result (which one is the most efficient btw?) The issue is when there is more than one post from the same author with the same date. Then the returned result set contains all such posts. How should I modify these queries to return exactly one post per author?  SELECT p1.* FROM posts p1 LEFT JOIN posts p2 ON p1.author_id = p2.author_id AND p1.date &lt; p2.date WHERE p2.author_id IS NULL ORDER BY p1.author_id;  SELECT p1.*  FROM posts p1 INNER JOIN (   SELECT author_id, MAX(date) AS max_date   FROM posts   GROUP BY author_id) p2   ON p1.author_id = p2.author_id AND p1.date = p2.max_date ORDER BY p1.author_id;  SELECT * FROM posts p1 WHERE date = (SELECT MAX(p2.date)               FROM posts p2               WHERE p1.author_id = p2.author_id) ORDER BY author_id;  SELECT * FROM (     SELECT author_id, MAX(date) date     FROM posts GROUP BY author_id ) p1 INNER JOIN posts p2 USING (author_id, date) ORDER BY author_id;
71635
I have a table with fields  Hour, PathId, Duration, Event, CellId, Channel   I have 50+ CellId. Each CellId has four PathId (i.e. 0, 1, 2, 3). Each PathId has many Events and Durations. Now I want to display the top 10 records (each PathId) for each CellId.  Sample Table    Sample Output
82308
I'm trying to optimize some of the database queries in my Rails app and I have several that have got me stumped.  They are all using an IN in the WHERE clause and are all doing full table scans even though an appropriate index appears to be in place.  For example:  SELECT `user_metrics`.* FROM `user_metrics` WHERE (`user_metrics`.user_id IN (N,N,N,N,N,N,N,N,N,N,N,N))   performs a full table scan and EXPLAIN says:  select_type: simple type: all extra: using where possible_keys: index_user_metrics_on_user_id  (which is an index on the user_id column) key: (none) key_length: (none) ref: (none) rows: 208   Are indexes not used when an IN statement is used or do I need to do something differently?  The queries here are being generated by Rails so I could revisit how my relationships are defined, but I thought I'd start with potential fixes at the DB level first.
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
10446
How do you return 1 value per row of the max of several columns:  TableName  [Number, Date1, Date2, Date3, Cost]   I need to return something like this:  [Number, Most_Recent_Date, Cost]   Query?
62729
I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 tables?  As far as what I think, it may slow tiny bit but it is necessary otherwise how can we save additional data about many-to-many relationships. Or is there any other way of which I am not aware of?
60488
I am opening this question after reading this:   I have this query [SELECT id, DISTANCE(some_field__c, GEOLOCATION(0,0), 'mi') distance FROM Parent__c LIMIT 1] and that's working fine.   However when I try to execute this query starting from the child:  [SELECT id, DISTANCE(Parent__r.some_field__c, GEOLOCATION(0,0), 'mi') distance FROM Child__c LIMIT 1] I get an error saying No such column 'distance' on entity Child__c. Is this a salesforce limitation or am I doing something wrong?