SQL query to avoid duplicates and order by a row

Need a SQL qu开发者_开发百科ery using joins I need a help

Table name: RVW_TSK

RVW_ID UPC_CD CMPL_DATE

00001  10101010  10-10-2009

00002  10101010  13-10-2009

00003  20202020  5-11-2008

00004  20202020  8-11-2008

Expected result is like:

RVW_ID  UPC_CD    CMPL_DATE
00002   10101010  13-10-2009
00004   20202020  8-11-2008

I want the latest one (from CMPL_DATE) and no duplication of UPC_CD. Any help would be appreciated?

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

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.

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

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;

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.

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