How can I fetch multiple matching results, show the result sorted by two columns (and then transpose)?

I want to be able to add urls in a list, then have a formula than can fetch all results that matches the url and have it sorted by 1) Position 2) Volume.

I have a semi working solution but when I try to limit it to three results, the sorting is off.

https://docs.google.com/spreadsheets/d/1dSXTdqFKUjQokv8cbzDLnfkGughp5m3yFLciLjCmqFo/edit?usp=sharing

In detail:

I have a sheet with a lot of raw data. It's a list of urls and for each url there are columns with

Theme
Position
Volume
Other data

In another sheet, I want to be able to add a url and have a formula that returns a list of all Themes that is related to that particular url. Since each url can have multiple Themes I want to be able to sort the result by 1) Position and if there is a tie 2) by volume.

I plan on adding multiple urls in my manual list, so I need to use transpose so that it is displays horizontally.

I have managed to come up with a (kind of) working solution for this, but I suspect it's not optimal.

=UNIQUE(SORT(INDEX(SORTN(FILTER( 'Raw Data'!B$2:D, 'Raw Data'!A$2:A=B17),50,3,3,FALSE),,0),2,true))

The main problem I have is that when I try to limit the list to show only top three the sorting is wrong.

=UNIQUE(SORT(INDEX(SORTN(FILTER( 'Raw Data'!B$2:D, 'Raw Data'!A$2:A=H17),3,2,3,FALSE),,0),2,true))

I'm still learning how to use Google Sheets formula. Appreciate your help!

Solution

In the duplicated tab I entered

=SORTN(FILTER( 'Raw Data'!B$2:D, 'Raw Data'!A$2:A=H17), 3, 2, 2, 1, 3, 0)

and for transposing

=ARRAY_CONSTRAIN(TRANSPOSE(SORTN(FILTER( 'Raw Data'!B$2:D, 'Raw Data'!A$2:A=H17),3,2,2,1, 3, 0)), 1, 3)

Alternatively, you can use query()

=query( 'Raw Data'!A$2:D, "Select B, C, D where A = '"&H17&"' order by C asc, D desc limit 3")

and

=transpose(query( 'Raw Data'!A$2:D, "Select B where A = '"&H17&"' order by C asc, D desc limit 3"))

See if that works?