https://pinot.apache.org/ logo
#troubleshooting
Title
# troubleshooting
l

Luis Fernandez

04/06/2022, 9:03 PM
is ORDER BY multiple fields not working properly on pinot?
Copy code
SELECT product_id, COUNT(*) as views
FROM table
ORDER BY views, product_id DESC
in regular RDBMS I would expect for it to order by the views first and then product id so something like
Copy code
views, product_id
3      3
3      2
3      1
but pinot does something completely different is doing
Copy code
views, product_id
1      10
1      9
1      8
anyone has an idea why this may be?
d

Diogo Baeder

04/06/2022, 9:05 PM
The result seems correct to me, it's ordering by descending order on both columns, except that
views
has only value 1
l

Luis Fernandez

04/06/2022, 9:06 PM
but I'm specifying I want it to be sorted by views
first so I want products with higher counts up front
d

Diogo Baeder

04/06/2022, 9:09 PM
Are you GROUPing BY?
r

Rong R

04/06/2022, 9:21 PM
upon checking the code. DESC applies to each individual column
e.g. the syntax that will achieve what you wanted is
Copy code
ORDER BY views DESC, product_id DESC
k

Ken Krugler

04/06/2022, 9:23 PM
Except isn’t
views
just a
COUNT(*)
without any group? So what would that mean? I think there’s a missing
GROUP BY product_id
l

Luis Fernandez

04/06/2022, 9:24 PM
oooo so I just have to specify on both
r

Rong R

04/06/2022, 9:25 PM
i believe this is common practices upon searching on stackoverflow. i don’t know however whether this ANSI standard.
“this” as “specifying DESC/ASC on each column individually”
l

Luis Fernandez

04/06/2022, 9:26 PM
cool cool thanks I will test it out
r

Rong R

04/06/2022, 9:26 PM
but what @User mention is another point to investigate, upon checking your SQL wouldn’t compile since it is unclear what you want to count(*) on
l

Luis Fernandez

04/06/2022, 9:27 PM
yea sorry I think I miss copied the query the group by is missing
👍 3
r

Rong R

04/06/2022, 9:27 PM
that’s what i’ve guessed 😆