Re: include ids in query grouped by multipe values
| От | Carlos Chapi |
|---|---|
| Тема | Re: include ids in query grouped by multipe values |
| Дата | |
| Msg-id | CAAk_w-pRO6uZ5c=s3RRuGOyfjCEX2y-QRqwTGf3pF+tcopNMRg@mail.gmail.com обсуждение исходный текст |
| Ответ на | include ids in query grouped by multipe values ("karsten" <karsten@terragis.net>) |
| Список | pgsql-sql |
Hi Karsten
--
2014-02-17 14:49 GMT-05:00 karsten <karsten@terragis.net>:
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;
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.
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
--
В списке pgsql-sql по дате отправления: