how do i take the results of my subqueries to join with another table?

Question

. trying to link together the results from borrower,book, to AUTHOR.

desired results:

AUTHORID         AUTHORFIRSTNAME      AUTHORLASTNAME
1                     JIM                   SPARKS
2                     JAMES                 ALLEN
3                     MARCUS                RASHFORD
20                    PAUL                  POGBA
22                    THIERRY               HENRY

but am unsure how to link the returned, top authorids to retrieve the authorfirstname and lastname but i have not mentioned author table in the subquery

Sample data and desired results would make the question much more understandable. — Gordon Linoff, Nov 10 '19 at 00:17
What is the datatype of column `borrowdate`: is it a `date` or some kind of string? — GMB, Nov 10 '19 at 00:17
@GordonLinoff cheers - i attempted to make my question more understandable. appreciate the feedback — , Nov 10 '19 at 00:37

Barbaros Özhan · Accepted Answer · 2019-11-10T11:32:31.783

You can join those three tables along with rank() analytic function in the descending order for count within a subquery and then take less than equal to five in the main query :

SELECT authorid, authorfirstname, authorlastname
  FROM
  (
  SELECT a.authorid, a.authorfirstname, a.authorlastname, 
         rank() over (order by count(*) desc)  as rnk
    FROM AUTHOR a
    LEFT JOIN BOOK bk ON a.authorid = bk.authorid
    LEFT JOIN BORROWER br ON br.bookid = bk.bookid
   WHERE br.borrowdate between date'2017-01-01' and date'2017-12-31'
   GROUP BY a.authorid, a.authorfirstname, a.authorlastname
   )
  WHERE rnk <= 5
  ORDER BY rnk

If you're using DB version 12c+, it's easier to fetch them :

SELECT a.authorid, a.authorfirstname, a.authorlastname, 
       rank() over (order by count(*) desc)  as rnk
  FROM AUTHOR a
  LEFT JOIN BOOK bk ON a.authorid = bk.authorid
  LEFT JOIN BORROWER br ON br.bookid = bk.bookid
 WHERE br.borrowdate between date'2017-01-01' and date'2017-12-31'
 GROUP BY a.authorid, a.authorfirstname, a.authorlastname
 ORDER BY rnk 
 FETCH FIRST 5 ROWS WITH TIES

where I used br.borrowdate between date'2017-01-01' and date'2017-12-31' instead of to_char(br.borrowdate) like '%2017' to be able to benefit the index on the column borrowdate if exists any.

Those queries above return the rows with ties, e.g. they bring more rows than five provided that multiple rows match the value of 5th row.

Do not use rownum pseudocolumn for ranking purposes, since the value of it is calculated before ordering and might yield wrong results.

score 0 · Answer 2 · answered Nov 10 '19 at 01:26

0

So, if i get it correctly, you want to do something like this:

select authorid, authorfirstname, authorlastname 
 from 
  (select a.authorid, a.authorfirstname, a.authorlastname 
   from author a, borrower b, book c 
   where a.authorid = c.authorid and c.bookid = b.bookid
   and b.borrowdate like '%2017' 
    group by c.bookauthor 
    order by count(*) desc) xx 
 where rownum <=5

answered Nov 10 '19 at 01:26

Georgy

428
2
16

oh my goodness! yes! something like this but unsure how to format it in order to display results. this is so much neater than what i was thinking! have been trying to mess around but perhaps still too bad at SQL. grrr. thanks for the help – Nov 10 '19 at 02:40

how do i take the results of my subqueries to join with another table?

2 Answers2