Re: [GENERAL] sub-select with multiple records, columns
On Jun 19, 2017, at 12:29 PM, Thomas Kellerer wrote: > > Israel Brewster schrieb am 19.06.2017 um 22:17: >> SELECT >> ... >> (SELECT >> array_agg(to_json(row(notedate,username,note))) >> FROM sabrenotes >> INNER JOIN users ON author=users.id >> WHERE ticket=sabretickets.id ) notes >> FROM tickets >> WHERE ... >> The only problem with this query is that the notes aren't sorted. Of >> course, simply adding an ORDER BY clause to the sub-select doesn't >> work - it throws an error about needing to use notedate in a GROUP BY >> clause or aggregate function. Is there some way I can get sorting as >> well here? Of course, I could just run a second query to get the >> notes, and combine in code, but that's no fun... :-) > > You can supply an ORDER BY to an aggregate function: > > array_agg(to_json(row(notedate,username,note)) order by ...) Thanks (and to David G. Johnston). Didn't realize I could do that, but it makes perfect sense. > > I have to admit, that I fail to see the the advantage of an array of JSON > objects, rather then having a single json with the elements inside. > > json_object_agg() or json_agg() might be better suited for this. You may be right. Actually, my first thought (and the ideal here) was to simply have an array of rows or the like. That is, wind up with a data structure where I could in my code do something like record['notes']['username'], or perhaps record['notes'][1]. However, while I didn't get any errors when I tried that, the parsing of the results fell apart at some point in the chain - I wound up with strings containing a bunch of escaped and double-escaped quotes and the like. Adding the to_json simply converted the rows to json strings, which I can work with easily enough. Since I do still have to parse the json anyway, perhaps making the entire array be a single json object that I could parse once would be a better approach. > > Thomas > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sub-select with multiple records, columns
On Mon, Jun 19, 2017 at 1:32 PM, David G. Johnston wrote: > On Mon, Jun 19, 2017 at 1:29 PM, Thomas Kellerer wrote: >> >> Israel Brewster schrieb am 19.06.2017 um 22:17: >>> >>> SELECT >>> ... >>> (SELECT >>> array_agg(to_json(row(notedate,username,note))) >>> FROM sabrenotes >>> INNER JOIN users ON author=users.id >>> WHERE ticket=sabretickets.id ) notes >>> FROM tickets >>> WHERE ... >>> >>> The only problem with this query is that the notes aren't sorted. Of >>> course, simply adding an ORDER BY clause to the sub-select doesn't >>> work - it throws an error about needing to use notedate in a GROUP BY >>> clause or aggregate function. Is there some way I can get sorting as >>> well here? Of course, I could just run a second query to get the >>> notes, and combine in code, but that's no fun... :-) >> >> >> You can supply an ORDER BY to an aggregate function: >> >>array_agg(to_json(row(notedate,username,note)) order by ...) >> >> I have to admit, that I fail to see the the advantage of an array of JSON >> objects, rather then having a single json with the elements inside. >> >> json_object_agg() or json_agg() might be better suited for this. >> You could also write: SELECT ..., ARRAY(SELECT to_json(...) [...] ORDER BY) AS notes FROM tickets David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sub-select with multiple records, columns
Israel Brewster schrieb am 19.06.2017 um 22:17: SELECT ... (SELECT array_agg(to_json(row(notedate,username,note))) FROM sabrenotes INNER JOIN users ON author=users.id WHERE ticket=sabretickets.id ) notes FROM tickets WHERE ... The only problem with this query is that the notes aren't sorted. Of course, simply adding an ORDER BY clause to the sub-select doesn't work - it throws an error about needing to use notedate in a GROUP BY clause or aggregate function. Is there some way I can get sorting as well here? Of course, I could just run a second query to get the notes, and combine in code, but that's no fun... :-) You can supply an ORDER BY to an aggregate function: array_agg(to_json(row(notedate,username,note)) order by ...) I have to admit, that I fail to see the the advantage of an array of JSON objects, rather then having a single json with the elements inside. json_object_agg() or json_agg() might be better suited for this. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sub select as a data column
you were close, try the following (untesting). You need the alias on the outer my_table for this to work select year report_year, sum(amount), sum(select amount from my_table where year <= a.year) from my_table a group by report_year; -- Original Message --- From: Phong & Ronni Bounmixay <[EMAIL PROTECTED]> To: pgsql-general@postgresql.org Sent: Mon, 28 Nov 2005 13:18:41 -0700 Subject: [GENERAL] sub select as a data column > I am having trouble coming up with words to describe exactly what I need so > searching for a solution is getting frustrating! If this has been answered > twelve hundred times - please forgive me! > > I want to do in postgresql what I do in oracle: > > select year report_year, > sum(amount), > sum(select amount from my_table where year <= report_year) > from my_table > group by report_year; > > I see lots of stuff on subselect in the from clause - but how do you > subselect as a data column? > > Thanks for the help - it is SEVERLY appreciated! > Ronni --- End of Original Message --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] sub select as a data column
You both are SO right! I sent that off and tried a couple of things and realized I was close but not close enough. I really appreciate the kind responses. Sometimes it's so hard to ask a question without feeling so foolish and then feeling worse when the answers are sarcastic. Thank you! Good luck to you both! You do such a good job in this group! RonniOn 11/28/05, Tom Lane <[EMAIL PROTECTED]> wrote: Phong & Ronni Bounmixay <[EMAIL PROTECTED]> writes:> I want to do in postgresql what I do in oracle:> select year report_year,> sum(amount), > sum(select amount from my_table where year <= report_year)> from my_table> group by report_year;That doesn't really work in Oracle does it? It violates the SQL specin at least three ways. Try something like select year as report_year, sum(amount), sum((select amount from my_table b where b.year <= a.year))from my_table agroup by year;(which should work in Oracle too, or any other SQL-spec-compliant database). Note the extra parentheses ... they're not optional.regards, tom lane
Re: [GENERAL] sub select as a data column
Phong & Ronni Bounmixay <[EMAIL PROTECTED]> writes: > I want to do in postgresql what I do in oracle: > select year report_year, > sum(amount), > sum(select amount from my_table where year <= report_year) > from my_table > group by report_year; That doesn't really work in Oracle does it? It violates the SQL spec in at least three ways. Try something like select year as report_year, sum(amount), sum((select amount from my_table b where b.year <= a.year)) from my_table a group by year; (which should work in Oracle too, or any other SQL-spec-compliant database). Note the extra parentheses ... they're not optional. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] sub select
On Thu, 30 Aug 2001, Ben-Nes Michael wrote: > Can I: > > select *, sum_rows as ( select count(*) from table2; ) from table1; I think you need to say: select *, (select count(*) from table2) as sum_rows from table1; But this won't be a very interesting query because the sum_rows column will be the same in every row. Where subselects get interesting is when you do a correlated subquery like this: select *, (select count(*) from table2 where table2.x = table1.y) as sum_rows from table1; -- Tod McQuillin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
