CODEWITHSUNDEEP
sqlpostgresql
Balaji V
Balaji V

Reputation: 956

Postgres ordering not working as expected

Below is the query i am using :

 SELECT T.abcd,
             String_agg(T.yyy, ',') AS yyys,
             T.bbb
      FROM   (SELECT s.abcd,
                     up.yyy,
                     s.bbb,
                     s.secondary_id
              FROM   A s
                     join B su
                       ON su.search_term_id = s.id
                     join lll_yyy up
                       ON up.lll = su.lll
              ORDER  BY s.abcd
                        su.page_no,
                        su.position) T GROUP  BY T.abcd, T.bbb

Basically the order of data produced after my aggregated function is not as expected .

The output should be sorted by abcd and page_no and position . Expected output:

A | 1,2,3,4 | XX

Actual Output

A |2,4,1,3 | XX

the second column in not sorted based on page_no,position as given in the query .

The abcd column has a wide variety of data with numbers,special chars etc. example: 0900 dr jne pink, 0900 dr jne pink,098 lakhani shoe, iphone, c??mpu men shoe sport are some sample terms in the abcd column

I tried using collate "C" option

Is there a way to figure out which word is screwing up the sort order

Upvotes: 0

Views: 1049

Answers (2)

klin
klin

Reputation: 121889

Use the ORDER BY clause in the aggregate expression:

SELECT T.abcd,
         String_agg(T.yyy, ',' ORDER BY s.abcd, su.page_no, su.position) AS yyys,
         T.bbb
  FROM   (SELECT s.abcd,
                 up.yyy,
                 s.bbb,
                 s.secondary_id
          FROM   A s
                 join B su
                   ON su.search_term_id = s.id
                 join lll_yyy up
                   ON up.lll = su.lll) T GROUP  BY T.abcd, T.bbb

The ORDER BY clause in a derived table is ignored.

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You are showing an incomplete query. However, it seems to be like this:

SELECT ... FROM (SELECT ... ORDER  BY ...)

Your main select doesn't have an ORDER BY clause here. You can hence get the result rows in any order. The ORDER BY clause in the subquery can be ignored by the DBMS, because data in a table (which includes derived tables, i.e. subqueries) is considered an unordered set.

Upvotes: 0

Related Questions