2

I am using MS SQL database. I need to selected rows as per Max value without duplicate. Tried SQL QUERY given below 

Select  CId, 
        Rate,
        Max(FDateTime) 
from FTable 
Where OId = 1413 and type = 'C'
Group by CId,Rate

But it gives duplicate values.

I need Rate value and CID as per MAX(FDateTime). How can I achieve this output?

I have tried Inner join concept also but I have faced some errors on that concept.

gofr1
  • 15,741
  • 11
  • 42
  • 52
NIKHIL K A
  • 328
  • 5
  • 16

5 Answers5

1

Try this

  SELECT CID ,Rate ,FDateTime 
  FROM (SELECT   CID ,Rate ,FDateTime,RANK() OVER (PARTITION BY CID  ORDER BY FDateTime DESC) rank
         FROM FTable WHERE OId = 1413 and TYPE = 'C') AS T  WHERE rank=1
 ORDER BY CID
Jibin Balachandran
  • 3,381
  • 1
  • 24
  • 38
0

Use row_number():

select ft.*
from (Select ft.*,
             row_number() over (partition by cid, rate order by fdatetime desc) as seqnum
      from FTable ft
      where ft.OId = 1413 and ft.type = 'C'
     ) ft
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

For that you should be using a window function (if your Sql-Server version allow it) :

WITH TMP AS (
 SELECT CId, Rate, ROW_NUMBER() OVER(PARTITION BY CId, Rate ORDER BY FDateTeim DESC) AS RNK
 FROM my_table
 WHERE .... )
SELECT CId, Rate
FROM TMP
WHERE RNK = 1

MSDN ROW_NUMBER()

Nemeros
  • 415
  • 2
  • 7
  • I have tried this one,but still getting duplicate records – NIKHIL K A Aug 30 '16 at 11:44
  • I would be really interested to see that. row_number guaranti you the uniqueness over the partition clause. So for the case asked it cannot return a duplicated line. Now if if you still think that you have some duplicated record then post the data in and the data out wanted with the structure of the table – Nemeros Aug 30 '16 at 11:46
0

You can use OUTER APPLY to get MAX(FDateTime) for particular OId and [type]:

;WITH FTable AS (
SELECT *
FROM (VALUES
(106, 6, '2016-05-12 16:15:06.340','C',1413),
(109, 6, '2016-07-28 15:33:06.247','C',1413),
(109, 6, '2016-08-09 20:37:33.643','C',1413),
(111, 6, '2016-08-08 21:23:00.487','C',1413),
(106, 7, '2016-05-18 12:50:35.210','C',1433)
) as t(CId,Rate,FDateTime,[type],OId)
)


SELECT  f.CId, 
        f.Rate,
        f.FDateTime
FROM FTable f
OUTER APPLY (
    SELECT MAX(FDateTime) fdate
    FROM FTable 
    WHERE OId = f.OID and [type] = f.[type]
    ) as t
WHERE f.OId = 1413 and f.[type] = 'C' and t.fdate=f.FDateTime

Output:

CId Rate    FDateTime
109 6       2016-08-09 20:37:33.643
gofr1
  • 15,741
  • 11
  • 42
  • 52
  • Yes,sorry for late reply.But I got this error "Aggregates on the right side of an APPLY cannot reference columns from the left side." – NIKHIL K A Aug 30 '16 at 12:46
  • What version of SQL Server do you use? I create temp table with samples like `(1,1,'2016-01-01,1413,'C') as (CId,Rate,FDateTime,[type],OId)` etc with various dates and all worked fine. – gofr1 Aug 30 '16 at 12:50
  • I add some sample data and output – gofr1 Aug 30 '16 at 12:54
0

All Rate and CID for MAX(FDateTime) among OId = 1413 and type = 'C'

select  CId, 
        Rate,
        FDateTime 
from FTable f
where OId = 1413 and type = 'C'
  and FDateTime = (select Max(f2.FDateTime)
                   from FTable f2             
                   where f2.OId=f.Oid and f2.type = f.type)
Serg
  • 22,285
  • 5
  • 21
  • 48