I am struggling to write a query that returns what I want. The table:
|---------------------|------------------|------------------|------------------|
| -- filename -- | -- url -- | -- pixels -- | -- id -- |
|---------------------|------------------|------------------|------------------|
| myfilename1 | url1 | 3100 | 1 |
|---------------------|------------------|------------------|------------------|
| myfilename2 | url1 | 1200 | 2 |
|---------------------|------------------|------------------|------------------|
| myfilename3 | url2 | 3000 | 3 |
|---------------------|------------------|------------------|------------------|
| myfilename4 | url3 | 4000 | 4 |
|---------------------|------------------|------------------|------------------|
| myfilename5 | url3 | 5000 | 5 |
|---------------------|------------------|------------------|------------------|
How I want to get the results:
|---------------------|------------------|------------------|------------------|
| -- filename -- | -- url -- | -- pixels -- | -- id -- |
|---------------------|------------------|------------------|------------------|
| myfilename4 | url3 | 4000 | 4 |
|---------------------|------------------|------------------|------------------|
| myfilename5 | url3 | 5000 | 5 |
|---------------------|------------------|------------------|------------------|
| myfilename1 | url1 | 3100 | 1 |
|---------------------|------------------|------------------|------------------|
| myfilename2 | url1 | 1200 | 2 |
|---------------------|------------------|------------------|------------------|
| myfilename3 | url2 | 3000 | 3 |
|---------------------|------------------|------------------|------------------|
Basically I'd like for the results to be ordered so that the url "groups" are ordered by pixel between themselves, and then each group internally is ordered by id. As you can see in the table of the results I'd like, the max of the pixels column is 5000, so the "url3" group is first, and it's ordered by id. Then comes the url1 group, for which the local max is the second highest (3100).
Please note that the rows could be in the tens of thousands, and there's more rows not pictured and even more that could be added in the future, so hardcoded answers are useless. This is meant to be paginated by url, so the query should also support limits and offset by urls, not filenames (eg, return all the rows belonging to the nth 20 urls ordered by this method). I have tried ordering by MAX, OVER PARTITION and similar but I just can't understand how to do it.
My latest attempt was shot down by the fact that I cannot mix MAX() and OVER PARTITION, because the only way I could think of approaching the problem was of partitioning the local max of each url. I suspect there might be some way to do this with an inner join but I just don't know where to start with it, because I need to select the entire rows, not just some columns, so I can't manually select each column.
You seem to want a window function in the order by:
order by max(pixels) over (partition by url) desc,
url, id