How can I get result from group by on With clause(cte) in tsql

Viewed 70

I use this query for get these result :

code:

With Res
As
(
SELECT GroupID ,HV.[HadithVerID] As HadithId ,CenturyID
FROM JHadithVer HV 
INNER JOIN JHadith H ON HV.HadithID = H.HadithID 
    WHERE HV.SourceID=28
)
Select * From Res

Result:

GroupId Hadithid    CenturyId
7565    27062   4
22206   27063   4
125903  27064   6
60671   27065   4
125903  27074   4
125903  27083   4

How can I change my select of with(Res) for get HadithId that between common GroupId with less CenturyId. For example between

125903  27064   6

and

125903  27074   4

and

125903  27083   4

result is : 27074(Between 2 result 27074 & 27083, I want 27074 that is less!)

3 Answers
Related