SQL doubt string_agg (Delphi+Postgres)

Asked

Viewed 32 times

0

Good evening. This sql was running exactly as I wanted, but yesterday started to get out of the field order g gallery.. It had an output (example) like this: GALLERY 10: 26 / GALLERY 11: 23 / GALLERY 12: 21 and so on. Now it’s totally out of order, I don’t understand. Someone sees the error?

WITH quantidade as(
    Select g.galeria, g.tipo, g.finalidade, count(p.galeria) as total
    from presos p
    full join galerias g
    on p.galeria = g.galeria
    group by g.galeria,g.tipo,g.finalidade
    order by g.galeria
) 
Select string_agg(cast(g.tipo as text) || ' ' || cast(g.galeria as text) || ': ' || q.total, '  /  ') 
from galerias g full join quantidade q on (q.galeria = g.galeria and q.tipo = g.tipo and q.finalidade = g.finalidade)
WHERE g.galeria <= 50
postgresql delphi

  • See if you include the order by g.galeria after WHERE g.galeria <= 50 resolves. It would: WHERE g.galeria <= 50 order by g.galeria. Maybe you deleted it by mistake or the sort could be configured in some Delphi component that you have replaced and not configured again. Take a test and give feedback.

  • Save Clarck...had already tried this......

  • The problem may be due to use in WITH quantidade as ... are using order by g.galeria and in charge Select string_agg(cast(g.tipo as text) is started with g.tipo. See the simulation performed on sqlfiddle. To improve understanding, I suggest you simulate your situation in another environment similar to what I did in the sqlfiddle.

No answers

Browser other questions tagged postgresql delphi

You are not signed in. Login or sign up in order to post.