Postgres ordering not working as expected

Question

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

When sorting strings, '10' will be lower then '2'. If you want to treat them as numbers see here: stackoverflow.com/questions/tagged/postgresql+natural-sort — user330315
– user330315, Commented Oct 26, 2017 at 14:31
If you want to get helpful answers, then please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots. edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Oct 26, 2017 at 14:31
What order do you get you consider incorrect? Give an example of, say, five strings, the order you get and the order you want instead along with an explanation why you consider your order correct. — Thorsten Kettner
– Thorsten Kettner, Commented Oct 26, 2017 at 14:54

klin · Accepted Answer · 2017-10-26 16:25:22Z

1

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.

edited Oct 26, 2017 at 16:25

answered Oct 26, 2017 at 15:10

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2017-10-26 15:00:04Z

0

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.

answered Oct 26, 2017 at 15:00

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

Postgres ordering not working as expected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related