agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedinclude ids in query grouped by multipe values
3+ messages / 3 participants
[nested] [flat]
* include ids in query grouped by multipe values
@ 2014-02-17 19:49 karsten <karsten@terragis.net>
2014-02-17 21:49 ` Re: include ids in query grouped by multipe values Carlos Chapi <carlos.chapi@2ndquadrant.com>
2014-02-17 22:12 ` Re: include ids in query grouped by multipe values David Johnston <polobo@yahoo.com>
0 siblings, 2 replies; 3+ messages in thread
From: karsten @ 2014-02-17 19:49 UTC (permalink / raw)
To: pgsql-sql
Hi Group,
for several days I have been trying to resolve the following task below, but
all my attempts so far appear to give me back too many rows:
I have data kind like this in TableA:
id IDone IDtwo sale
1 010 200 8000
2 010 200 7851
3 010 200 517
4 020 210 5730
5 020 210 2000
6 020 230 3170
7 020 230 3170
8 020 230 2051
9 030 230 0
With the query below I can basically select the maximum sale for each IDone
- IDtwo combination - so almost what I need:
select IDone, IDtwo, max(sale) as maxsale FROM TableA group by IDone, IDtwo;
But it would want to also select the id column ( and all other additonal 20
columns of TabelA not shown above) and need that there is only one record
returned for each IDone - IDtwo combination. I tried
SELECT a.id, a.IDone, a.IDtwo, a.sale FROM TableA a
inner join ( select IDone, IDtwo, max(sale) as maxsale FROM TableA group by
IDone, IDtwo ) b
on a.IDone = b.IDone and b.IDtwo = b.IDtwo
and a.sale = b.maxsale;
but that returns too many rows and I do not understand why
How can I resolve this ?
Karsten Vennemann
Terra GIS LTD
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: include ids in query grouped by multipe values
2014-02-17 19:49 include ids in query grouped by multipe values karsten <karsten@terragis.net>
@ 2014-02-17 21:49 ` Carlos Chapi <carlos.chapi@2ndquadrant.com>
1 sibling, 0 replies; 3+ messages in thread
From: Carlos Chapi @ 2014-02-17 21:49 UTC (permalink / raw)
To: karsten@terragis.net; +Cc: pgsql-sql
Hi Karsten
2014-02-17 14:49 GMT-05:00 karsten <karsten@terragis.net>:
>
> but that returns too many rows and I do not understand why
>
It returns more than the expected rows because there can be multiple rows
fulfilling the conditions (in the example, rows with id 6 and 7 will both
appear as both have the max(sale) for idone = 020 and idtwo = 230)
> How can I resolve this ?
>
>
It depends if you want to show all the rows with max(sale) (which you're
already doing so I suppose you don't want that), if you want to show the
first one to be found or if you want to show the last one to be found.
If you only want the first one, you could do something like:
SELECT min(a.id) as id, a.IDone, a.IDtwo, a.sale
FROM TableA a
INNER JOIN (SELECT IDone, IDtwo, max(sale) AS maxsale FROM TableA GROUP BY
IDone, IDtwo) b
ON a.IDone = b.IDone
AND a.IDtwo = b.IDtwo
AND a.sale = b.maxsale
GROUP BY a.IDone, a.IDtwo, a.sale;
and add the other columns as desired
--
Carlos Chapi
http://www.2ndQuadrant.com/<http://www.2ndquadrant.com/;
Professional PostgreSQL: Soporte 24x7 y capacitación
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: include ids in query grouped by multipe values
2014-02-17 19:49 include ids in query grouped by multipe values karsten <karsten@terragis.net>
@ 2014-02-17 22:12 ` David Johnston <polobo@yahoo.com>
1 sibling, 0 replies; 3+ messages in thread
From: David Johnston @ 2014-02-17 22:12 UTC (permalink / raw)
To: pgsql-sql
Karsten-3-2 wrote
> select IDone, IDtwo, max(sale) as maxsale FROM TableA group by IDone,
> IDtwo;
>
> But it would want to also select the id column ( and all other additonal
> 20
> columns of TabelA not shown above) and need that there is only one record
> returned for each IDone - IDtwo combination. I tried
>
> SELECT a.id, a.IDone, a.IDtwo, a.sale FROM TableA a
> inner join ( select IDone, IDtwo, max(sale) as maxsale FROM TableA group
> by
> IDone, IDtwo ) b
> on a.IDone = b.IDone and b.IDtwo = b.IDtwo
> and a.sale = b.maxsale;
>
[Not Tested]
SELECT b.IDone, b.IDtwo, b.sale, array_agg(a) AS matching_on_tableA
FROM TableA a
NATURAL JOIN ( SELECT IDone, IDtwo, max(sale) AS sale FROM TableA GROUP BY
1, 2 ) b
GROUP BY 1, 2, 3;
In this solution you simply save the entire tableA record, as a composite
typed column, into an array so that you now have a single row for each
"IDone, IDtwo, (max)sale" combination - which you omitted in the description
above - but can still access to relevant matching rows using the array. Add
"ORDER BY" - e.g., array_agg(...ORDER BY) - to setup a desired sort order.
You can do something like (against, not tested):
SELECT IDone, IDtwo, sale, (array_agg[0]).* AS row_from_tablea FROM <the
above query>
to get to the relevant data in the array.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/include-ids-in-query-grouped-by-multipe-values-tp5792470p579...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-02-17 22:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-02-17 19:49 include ids in query grouped by multipe values karsten <karsten@terragis.net>
2014-02-17 21:49 ` Carlos Chapi <carlos.chapi@2ndquadrant.com>
2014-02-17 22:12 ` David Johnston <polobo@yahoo.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox