SQL query question :(?

Question

I had 20 SQL query questions and managed to somehow complete 17 but these 3, under a similar theme, aren't working :(

The basic set-up is that there is a big Access data project with several databases, I'm giving you the relevant ones here with the relevant fields:

-GPA (this one is actually an earlier query but it's OK). fields: GPA, name
-students (suppose names are unique, I don't mind this): name, group

So the queries:

1. Show the top 5 (on the basis of GPA) students from every group
2. Show the top 5 and bottom 5 from every group
3. Show the top 40% students from every group

How should I implement this? I think if I solve 1 I can solve the other 2 by myself. Thanks!

Answer 1

It's a little tricky to do ranks in Access. You basically have to join/or select from the tables twice; once to get the GPA per student and then again to see how many students have a better GPA per GROUP. When you do this, you can see where the student ranks and then select them if they're in the top 5. Doing it like this, instead of just saying TOP 5 ORDER BY GPA DESC - allows you to get the top 5 PER GROUP in a single query, instead of running the same query several times, substituing each group as you do.



I'm pretty confident 1 and 2 will work. Number 3 was a little trickier, because I'm not sure what top 40% means. Does it mean you rank each student by GPA and then just take the top 40% of those (so 4 students if each group has 10) - OR - Does it mean, find the average GPA and take all students who scored in the top 40%?? I ran it based on the first one (I think), but you're mileage may very with it.



Good Luck!



1)

SELECT Name, Group, GPA

FROM (

SELECT Student.Name, Student.Group, GPA.GPA, (SELECT COUNT(*) FROM GPA GPA2 INNER JOIN Student Student2 ON GPA2.Name = Student2.Name WHERE GPA2.GPA > GPA.GPA and Student.Group = Student2.Group)+1 AS Ranked

FROM GPA INNER JOIN Student ON GPA.Name = Student.Name

) as RankedGPA

Where Ranked <= 5

Order By Group, GPA Desc



2)

SELECT Name, Group, GPA, Type

FROM (

SELECT Student.Name, Student.Group, GPA.GPA, (SELECT COUNT(*) FROM GPA GPA2 INNER JOIN Student Student2 ON GPA2.Name = Student2.Name WHERE GPA2.GPA > GPA.GPA and Student.Group = Student2.Group)+1 AS Ranked, 'Top' as Type

FROM GPA INNER JOIN Student ON GPA.Name = Student.Name

) as RankedGPA

Where Ranked <= 5

Order By Group, GPA Desc



UNION ALL

SELECT Name, Group, GPA, Type

FROM (

SELECT Student.Name, Student.Group, GPA.GPA, (SELECT COUNT(*) FROM GPA GPA2 INNER JOIN Student Student2 ON GPA2.Name = Student2.Name WHERE GPA2.GPA < GPA.GPA and Student.Group = Student2.Group)+1 AS Ranked, 'Bottom' as Type

FROM GPA INNER JOIN Student ON GPA.Name = Student.Name

) as RankedGPA

Where Ranked <= 5

Order By Group,Type, GPA Desc



3)

SELECT Name, Group, GPA

FROM (

SELECT Student.Name, Student.Group, GPA.GPA, (SELECT COUNT(*) FROM GPA GPA2 INNER JOIN Student Student2 ON GPA2.Name = Student2.Name WHERE GPA2.GPA > GPA.GPA and Student.Group = Student2.Group)+1 AS Ranked

FROM GPA INNER JOIN Student ON GPA.Name = Student.Name

) as RankedGPA

Where Ranked <= (Select Count(*) *.40 FROM Student Student3 Where Student3.Group = RankedGPA.Group)

ORDER BY Group, GPA Desc

Answer 2

for number 1 you can sort the gpa by ASC and put LIMIT 5 at the end of the SELECT statement



for number 2 you can make two SELECT statements, one is as number 1, the other is like nubmer 1 but with DESC , you can combine them with a UNION



eg SELECT name FROM table

UNION

SELECT name FROM table2



number 3 throws using limit out of the window. It sounds quite a challenge, can't really think of a solution on the spot



I would try adding COUNT() AS myCount as a field for the SELECT statement, i would attempt to provide myCount/100*40 as the LIMIT value, but its doubtful you can do that,failing this i would then try adding another field and try to assign incrementing values based on the order of the table, so row 1 would have 1, row 2 woudl have 2,etc. or i would look into finding another way to get the row number, a WHERE clause could then try and check if this value is within the first 40% of myCount

Answer 3

1) Sort descending, limit to 5



That's not going to help you with 2 or 3. You have to do them in code.

Answer 4

Are you allowed to use OLAP queries?