SQL query to avoid duplicates and order by a row

Answer 1

SELECT   B.RVW_ID, B.UPC_CD, B.CMPL_DATE 
FROM     (SELECT UPC_CD, MAX(CMPL_DATE) CMPL_DATE
          FROM RVW_TSK
          GROUP BY UPC_CD)TBL JOIN RVW_TSK B
ON        TBL.UPC_CD = B.UPC_CD AND TBL.CMPL_DATE = B.CMPL_DATE

Answer 2

This seems like a straight forward group by unless i am reading something wrong here. Since i dont have any database to test it now..cannot vouch for the exact syntax but here goes...assuming that the CMPL_DATE is a datetime field. It might need some conversions etc if it is a string

Select MAX(RVW_ID), UPC_CD, MAX(CMPL_DATE)
FROM RVW_TSK
GROUP BY UPC_CD

Answer 3

If rvw_id and cmpl_date have the same order, you can get away with:

select max(rvw_id), upc_cd, max(cmpl_date)
from rvw_tsk
group by upc_cd

If not, you have to do something slightly fancier, as in Bharat's or Baaju's answers.

Answer 4

select  MAX(RVW_ID), distinct UPC_CD, max(CMPL_DATE) from RVW_TSK group by UPC_CD

Answer 5

SELECT *
  FROM (SELECT rvw_id,
               upc_cd,
               cmpl_date,
               RANK () OVER (PARTITION BY upc_cd ORDER BY cmpl_date DESC)
                  my_rank
          FROM rvw_tsk)
 WHERE my_rank = 1;

Answer 6

Select RVW_ID, UPC_CD, CMPL_DATE
From RVW_TSK As T
Where T.CMPL_DATE = (
                        Select Max(CMPL_DATE)
                        From RVW_TSK As T1
                        Where T1.UPC_CD= T.UPC_CD
                        )

This assumes that for a given UPC_CD, that the dates are unique. It is unclear from your post whether this is true and if not how duplicates should be handled.