how to group an ID field and select rows having highest value in another field PostgreSQL

Question

I don't know what title I should give for my question (never mind it).
below given is my select query

select gtab04.product,gtab05.productid,gtab05.mrp, gtab05.ptr,gtab05.ssr,gtab07.patent from gtab05 inner 
join gtab07 on gtab05.patentid=gtab07.patentid inner join gtab04 on 
gtab05.productid=gtab04.productid  where gtab05.qty-gtab05.iqty > 0 order by productid

and this will return 500+ rows, see the below sample,

product           |productid   |mrp     |ptr  |ssr  |patent
------------------+------------+--------+-----+-----+----------------- 
IBUGESIC Plus Tab |200         |12.80000|9.85 |8.87 |CIPLA LTD 
ANGICAM 2.5 Tab   |267         |9.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES 
ANGICAM 2.5 Tab   |267         |5.00000 |6.93 |6.24 |BLUE CROSS LABORATORIES 
ANGICAM 2.5 Tab   |267         |5.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES 
ANGICAM 2.5 Tab   |267         |5.00000 |7.359|6.24 |BLUE CROSS LABORATORIES 
ANGICAM 5 Mg Tab  |268         |14.00000|10.78|10.03|BLUE CROSS LABORATORIES 
ANGICAM 5 Mg Tab  |268         |12.00000|11.44|9.7  |BLUE CROSS LABORATORIES 
ANGICAM BETA Tab  |269         |17.00000|13.09|12.17|BLUE CROSS LABORATORIES 
ANGICAM BETA Tab  |269         |15.00000|13.9 |11.78|BLUE CROSS LABORATORIES 
HIBESOR 25 TAB    |270         |9.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES

i would like to modify the above result as following..

product           |productid   |mrp     |ptr  |ssr  |patent
------------------+------------+--------+-----+-----+----------------- 
IBUGESIC Plus Tab |200         |12.80000|9.85 |8.87 |CIPLA LTD 
ANGICAM 2.5 Tab   |267         |9.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES 
ANGICAM 5 Mg Tab  |268         |14.00000|10.78|10.03|BLUE CROSS LABORATORIES 
ANGICAM BETA Tab  |269         |17.00000|13.9 |11.78|BLUE CROSS LABORATORIES 
HIBESOR 25 TAB    |270         |9.00000 |6.93 |6.44 |BLUE CROSS LABORATORIES

my criteria : need to GROUP productid and from each group taking product's having max(mrp).

what i have tried as far as now.

    With cte as (
    select gtab04.product,gtab05.productid,gtab05.mrp, gtab05.ptr,gtab05.ssr,patent from gtab05 
    inner join gtab07 on gtab05.patentid=gtab07.patentid inner join gtab04 on 
    gtab05.productid=gtab04.productid  where qty-iqty > 0 order by productid limit 10
    )
    select productid,max(cte.mrp) as mrp  from cte group by productid order by  productid
    )

         RESULT
    --------------
   productid | mrp
          200|12.80000
          267|9.00000
          268|14.00000
          269|17.00000
          270|9.00000

I see it is the result of a JOIN (so it is a table *expression*) This Texp's PK is a function of the join-conditions and the base tables's PKs. In any case: your question in fact is a dedupping/find-the-first kind of problem. — wildplasser, Aug 13 '14 at 12:17
`my criteria : need to GROUP productid and from each group taking product's having max(mrp).` What if there are ties? (two tuples sharing the same mrp == max(mrp) in a group of equal productid's) — wildplasser, Aug 13 '14 at 12:50
Your query is ambiguous. We cannot see where the columns `patent` or `qty-iqty` come from. Add table-qualification where the origin is not obvious to the reader. — Erwin Brandstetter, Aug 13 '14 at 14:26
@ErwinBrandstetter > Your query is ambiguous. We cannot see where the columns patent or qty-iqty come from >> FIXED — , Aug 14 '14 at 04:24

Clodoaldo Neto · Accepted Answer · 2014-08-13T12:30:21.047

1

distinct on combined with order by

select distinct on (productid) 
    gtab04.product,
    productid,
    gtab05.mrp,
    gtab05.ptr,
    gtab05.ssr,
    patent
from
    gtab05
    inner join
    gtab07 using(patentid)
    inner join
    gtab04 using(productid)
where qty-iqty > 0
order by productid, gtab05.mrp desc

If there is any other untie criteria add it to the order by clause

http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT

edited Aug 13 '14 at 12:30

answered Aug 13 '14 at 11:58

Clodoaldo Neto

118,695
26
233
260

ERROR: syntax error at or near "using" LINE 13: gtab04 on using(productid) – Aug 13 '14 at 12:05
@keet Ok. Just took `on` out. – Clodoaldo Neto Aug 13 '14 at 12:07
ERROR: column "gtab04.product" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: gtab04.product, – Aug 13 '14 at 12:08
@keet Fixed. It is without the `grou by` – Clodoaldo Neto Aug 13 '14 at 12:30
1

This (now fixed) should probably be the accepted answer. – Erwin Brandstetter Aug 13 '14 at 18:01
@ErwinBrandstetter can you please tell how this works ? – Aug 14 '14 at 04:32
@keet: I've added another answer. – Erwin Brandstetter Aug 14 '14 at 05:28

score 0 · Answer 2 · answered Aug 13 '14 at 12:24

0

Reusing the original CTE:

WITH cte AS (
    select gtab04.product
       ,gtab05.productid ,gtab05.mrp, gtab05.ptr, gtab05.ssr
       ,patent -- NEEDS alias-designation
    from gtab05 
    inner join gtab07 on gtab05.patentid = gtab07.patentid -- NOT USED ...
    inner join gtab04 on gtab05.productid = gtab04.productid
    where qty-iqty > 0 -- NEEDS alias-designation
    order by productid -- NEEDS alias-designation
    -- limit 10
    )
SELECT * FROM cte t0
WHERE NOT EXISTS (
   SELECT * FROM cte t1
   WHERE t1.productid = t0.productid
   AND t1.mrp > t0.mrp
   )
order by  productid
   ;

answered Aug 13 '14 at 12:24

wildplasser

43,142
8
66
109

i have tried this _answer_ but it doesn't `GROUP` _productid_ – Aug 14 '14 at 04:21
You don't need to group. You only want to find the max of a "group" – wildplasser Aug 14 '14 at 07:52

score 0 · Answer 3 · edited May 23 '17 at 11:49

After clarifications in the question, I think this variant should perform faster:

SELECT g4.product, g5.productid, g5.mrp, g5.ptr, g5.ssr, g7.patent
FROM  (
   SELECT DISTINCT ON (productid)
          productid, mrp, ptr, ssr, patentid
   FROM   gtab05
   WHERE  qty > iqty
   ORDER  BY productid, mrp DESC
   ) g5
JOIN   gtab07 g7 USING (patentid)
JOIN   gtab04 g4 USING (productid);

Basically it's using DISTINCT ON, like @Clodoaldo already provided. Detailed explanation for that:

Select first row in each GROUP BY group?

Two things changed:

It's cheaper to eliminate duplicates before joining to more tables. The benefit grows with the number of rows per group. I am assuming, that joining to gtab07 and gtab04 does not produce more duplicates.
The WHERE condition can be simplified to WHERE qty > iqty. Slightly shorter and faster.
However, if that conditions is selective (less than ~ 5% of the table qualify), and you have to run this query a lot (and the gain outweighs the cost of index maintenance), then keep your original condition and support it with a functional index:
```
CREATE INDEX gtab05_qty_iqty_idx ON gtab05((qty - iqty));
```

Run EXPLAIN ANALYZE to test performance.

`EXPLAIN ANALYZE` of @ClodoaldoNeto 's [Answer](http://explain.depesz.com/s/fUVq) and [This Answer](http://explain.depesz.com/s/cCn) — , Aug 14 '14 at 06:02

how to group an ID field and select rows having highest value in another field PostgreSQL

3 Answers3