SQL Help - Limit maybe?

Databases
1

I’m trying to do a JOIN that will limit the number of records per ID to 16. There are 6 tables being joined together, but I think it should be the same answer if there are only 2, so to keep it simple,
Students(student_rowid INTEGER PRIMARY KEY, student_name) //and 10 other columns
Student_Grades(sg_rowid INTEGER PRIMARY KEY, sg_student_rowID INTEGER, sg_letter_grade_rowid INTEGER, sg_course_rowid INTEGER, sg_DateTaken) // where the _rowID columns are pointing to Primary Keys Columns in other tables

So I only want the last 16 courses that each student has taken I don’t care about course 17 even if it’s sg_DateTaken is the same as 16 nor do I care which one of these 2 courses show.
I am wondering if there is a way to LIMIT the number of courses that come back from the database PER student_rowid.
I am using

SELECT student_name, ru_letter_grade, cn_coursename , sg_DateTaken FROM students JOIN student_grades ON student_grades . sg_student_rowid = student_rowid JOIN ru_letters ON sg_letter_grade_rowid = ru_letters.ru_letter_rowid JOIN courses ON courses . course_rowid = student_grades . sg_course_rowid

I am wondering if there is a way to LIMIT the number of courses that come back from the database PER student_rowid. Right now I’m getting all their courses and ignoring anything over 16, but with 1000s of students, this is a lot of wasted time.

TIA

2

At the end of your query you can specify, LIMIT 16. To make sure it’s the last 16 you can do this:

SELECT student_name, ru_letter_grade, cn_coursename , sg_DateTaken FROM students JOIN student_grades ON student_grades . sg_student_rowid = student_rowid JOIN ru_letters ON sg_letter_grade_rowid = ru_letters.ru_letter_rowid JOIN courses ON courses . course_rowid = student_grades . sg_course_rowid ORDER BY student_rowid DESC LIMIT 16
That should give you your results in reverse order and only the first (or last) 16.

Hope this helps. :slight_smile:

3

Thanks, but that limits the whole SELECT to 16, I need:
Student 1 - 16 results
Student 2 - 16 results
Student3 - 16 results

For 1000+ students

4

Oh, sorry, I misunderstood.

Maybe this:

[code]SELECT student_name, ru_letter_grade, cn_coursename , sg_DateTaken
FROM students
JOIN student_grades ON student_grades . sg_student_rowid = student_rowid
JOIN ru_letters ON sg_letter_grade_rowid = ru_letters.ru_letter_rowid
JOIN courses ON courses . course_rowid = student_grades . sg_course_rowid
HAVING COUNT(student_rowid) = 16;

5

For some reason I can’t edit that:

SELECT student_name, ru_letter_grade, cn_coursename , sg_DateTaken FROM students JOIN student_grades ON student_grades . sg_student_rowid = student_rowid JOIN ru_letters ON sg_letter_grade_rowid = ru_letters.ru_letter_rowid JOIN courses ON courses . course_rowid = student_grades . sg_course_rowid HAVING COUNT(student_rowid) = 16;

6

Have you tried ScriptCase for web database applications?

7

How does that help on a Xojo forum?

8

I have to decide between these.

9

If you’d like to discuss this, please create a separate thread in the “Off Topic” channel rather than replying in the middle of this conversation. You’ll probably get better results, assuming anyone here has heard of ScriptCase.

10

Well the error I got was I needed the Group By in order to use Having, so I stuck in Group By student_name (not really … student_id) and I got no error, but rs.EOF = true, so no data returned :frowning:

11

Are you able to query your database outside of Xojo? Often times I’ll use something like PgAdmin to put together my queries, then enter them into Xojo once I’ve verified they’re working.

What type of database are you working with?

12

Yes, I’m not actually testing on Xojo, just thought it would make more sense if I used code to explain rs.EOF It’s an SQLite and I have a plethora of tools, I’m using SQLiteStudio now as it’s the only one that allows for Foreign Keys

13

Interesting. I’m honestly having a bit of a tough time wrapping my mind around your exact query.

So you have a table, students, that is relational to a couple other tables. You want the name, grade, course, and date for the student. But, you only want each unique student_rowid to appear within 16 results?

Am I understanding you correctly?

14

Pretty close that. The students table has A RowID, Student number, Student Name and Last Update (date) all are real values. It has 2 Integer columns that are the rowids where the actual values are in their own tables School_code and Major_code.

So all the students are listed in students table 3,000 of them

The student_grade table has A RowID, DateTaken both real values. It also has 3 Integer columns Course, GPA and Students rowid that are the rowids of the tables Courses, GPAs and Students

So all the grades are in student_grade table 50,000 of them

Right now when I query I get
Student_Rowid, Student_Number, Student_Name, School, Major, GPA, Letter_Grade, Course_Code, Course_Name, DateTaken, student_grade_rowid
for all 50,000 grades. Some of the students have taken 2 courses, some have taken 40 courses. I am trying to automate the Dean’s List, so I only need to look at the last maximum of 16 (minimum of 8) courses taken by each student. I’ve weeded out all the students that don’t meet the requirements i.e. don’t have a minimum of 8 courses, have Failed a course, have an Academic Misconduct charge … so now I’m down to 45,000 rows returned and a lot of them are the 17th plus course which I don’t need. I have code now that loops through the record set and skips the courses after 16, but if there’s a way to not return records 17+ for the student it would make the program go that much faster.

15

What database are you using? If you can use CTEs you can do something like this:

with x as
(
select allOfTheFieldsICareFor,
row_number() over ( partition by Student_Rowid order by Date DESC ) as numOfCourses
from student_grade
)
select * from x
where numOfCourses < 17

16

Sorry, I should have posted I figured it out, it was a simple IN clause I needed to put into the where. I was over thinking the whole thing.