0

I use SqlPlus.

There is a lot of solutions and examples out there for related problems, but I haven't been able to fix my issue.

Expected result: 1 line that gives information about a library member that borrowed a book for the longest time. (displaying the amount of time: ex. Johnson John has ...: 31 days)

My current query:

SELECT DISTINCT m.firstname || ' ' || m.lastname || ' has borrowed for the longest time: ' || ROUND(MAX(l.date_of_return - l.date_borrowed)) || ' days' "Longest time borrowed" 
FROM loans l
JOIN members m
ON l.memberid = m.memberid
WHERE l.date_of_return - l.date_borrowed = (SELECT MAX(date_of_return - date_borrowed) FROM loans)

/

Tables used:

LOANS:
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
  ISBN                                                  NOT NULL VARCHAR2(20)
  SERIAL_NO                                             NOT NULL NUMBER(2)
  DATE_BORROWED                                         NOT NULL DATE
  DATE_OF_RETURN                                                 DATE
  MEMBERID                                              NOT NULL VARCHAR2(11)
  EXTEND                                                         VARCHAR2(5)

MEMBERS:
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
  MEMBERID                                              NOT NULL VARCHAR2(11)
  LASTNAME                                              NOT NULL VARCHAR2(20)
  FIRSTNAME                                                      VARCHAR2(20)

Error:

ERROR at line 1: ORA-00937: not a single-group group function

I think I'm overlooking a simple solution. Thanks in advance.

Mayfair
  • 358
  • 1
  • 11
  • Drop `MAX` in the select list, use `ROWNUM` pseudo-column to restrict the number of rows to be returned. – Serg Jan 24 '21 at 14:45
  • @Serg I am not familiar with ROWNUM. And wouldn't dropping the MAX ruin my expected output? Perhaps you could post a code example/snippet to further explain your comment. – Mayfair Jan 24 '21 at 14:49

2 Answers2

1

Try

SELECT m.firstname || ' ' || m.lastname || ' has borrowed for the longest time: ' || ROUND(l.date_of_return - l.date_borrowed) || ' days' "Longest time borrowed" 
FROM loans l
JOIN members m
ON l.memberid = m.memberid
WHERE l.date_of_return - l.date_borrowed = (SELECT MAX(l2.date_of_return - l2.date_borrowed) FROM loans l2) 
  AND ROWNUM <=1
Serg
  • 22,285
  • 5
  • 21
  • 48
0

You might want to avoid the additional calculation based comparison outside of the inner select (which would likely end up as an additional loop over loans during execution significantly stretching the run time). It should be possible to collect the id inside the inner select as well as the calculation result and use it outside.

Try something like this:

SELECT m.firstname, m.lastname, b.maxtime FROM members m, loans l INNER JOIN ( SELECT li.memberid id, MAX(li.date_of_return - li.date_borrowed) maxtime FROM loans li GROUP BY li.memberid ) b ON m.memberid = b.id ORDER BY b.maxtime

BTW: There's a pretty good post covering similar topics here (just in case you haven't found this one while searching), which might contain some interesting ideas for what you're trying to do: SQL select only rows with max value on a column

Peter Bugla
  • 38
  • 4