CODEWITHSUNDEEP
sqlsql-serverjoinstored-proceduresaggregate-functions
NIKHIL K A
NIKHIL K A

Reputation: 328

Return selected rows as per Max value without duplicate using MS SQL

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.

Upvotes: 2

Views: 106

Answers (5)

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

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

Upvotes: 1

gofr1
gofr1

Reputation: 15987

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

Upvotes: 0

Serg
Serg

Reputation: 22811

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)

Upvotes: 0

Nemeros
Nemeros

Reputation: 425

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()

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

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;

Upvotes: 0

Related Questions