-1

I have a table where I save authors and songs, with other columns. The same song can appear multiple times, and it obviously always comes from the same author. I would like to select the author that has the least songs, including the repeated ones, aka the one that is listened to the least. The final table should show only one author name.

MT0
  • 86,097
  • 7
  • 42
  • 90
Elena
  • 11
  • 1
  • 1
    Please provide sample data and desired results. – Gordon Linoff Jan 21 '21 at 16:04
  • 1
    Also: What is your Oracle version (there are some things you can do in Oracle 12.1 and above that you can't do in earlier versions, for example); and also, your level of knowledge. Obviously this is some kind of homework; to help you, we need to know how much you have covered in class. It does you no good if you give you a simple, elegant, efficient solution that uses advanced features you haven't covered in class yet. – mathguy Jan 21 '21 at 16:10
  • Duplicate of https://stackoverflow.com/q/470542/1509264 – MT0 Jan 21 '21 at 16:19

2 Answers2

0

Clearly, one step is to find the count for every author. This can be done with an elementary aggregate query. Then, if you order by count and you can just select the first row, this would solve your problem. One approach is to use ROWNUM in an outer query. This is a very elementary approach, quite efficient, and it works in all versions of Oracle (it doesn't use any advanced features).

select author
from   (
         select author
         from   your_table
         group  by author
         order  by count(*)
       )
where  rownum = 1
;

Note that in the subquery we don't need to select the count (since we don't need it in the output). We can still use it in order by in the subquery, which is all we need it for.

The only tricky part here is to remember that you need to order the rows in the subquery, and then apply the ROWNUM filter in the outer query. This is because ORDER BY is the very last thing that is processed in any query - it comes after ROWNUM is assigned to rows in the output. So, moving the WHERE clause into the subquery (and doing everything in a single query, instead of a subquery and an outer query) does not work.

mathguy
  • 37,873
  • 5
  • 22
  • 47
  • thank you so much! I was definitely overcomplicating my code, but this worked perfectly – Elena Jan 21 '21 at 18:39
0

You can use analytical functions as follows:

Select * from
(Select t.*,
       Row_number() over (partition by song order by cnt_author) as rn
  From
(Select t.*,
       Count(*) over (partition by author) as cnt_author
  From your_table t) t ) t
 Where rn = 1
Popeye
  • 34,354
  • 4
  • 8
  • 30
  • I don't understand - why do you need to partition by **song** in the middle query? What does that have to do with the problem? – mathguy Jan 21 '21 at 16:19