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

---

Answer 1

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.

Answer 2

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

Answer 3

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
   ;