Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
hspatil31
Posting Yak Master
182 Posts |
Posted - 2010-12-30 : 03:05:56
|
Dear All,I am having following query and output, i want highest Rate record ofspecific work_sheet_id, required output also as follows last one,Query:SELECT WS.ID as Work_Sheet_ID,NRS.ID AS Normalized_Rate_Sheet_ID, Call_Completion,NRS.RateFROM tb_lcr_normalized_rate_sheet NRSINNER JOIN tb_lcr_work_sheet WS ON NRS.Work_Sheet_ID=WS.ID INNER JOIN tb_lcr_rate_sheet RS ON RS.ID=WS.Rate_Sheet_ID INNER JOIN tb_lcr_carrier C ON C.ID=RS.Carrier_IDWHERE NPA=201 AND NXX=208 AND NRS.Call_Type_Id<>'INTERNATIONAL' and NRS.Status=1AND RS.ID IN (SELECT Rate_Sheet_IDFROM tb_lcr_rate_deck_detail RDINNER JOIN tb_lcr_rate_deck_carrier_rate_sheet RDRSH on RDRSH.Rate_Deck_Details_ID=RD.ID WHERE RD.ID='F74B6702-59B6-430D-9BD7-092F205C5B2A') ORDER BY NRS.Rate,C.priority,NRS.Work_Sheet_IDOutput:DDB8F149-BC6F-4E27-AAE9-2CFFB3F8823C 3FAA6D14-3AE9-4A93-B49B-79AB67D451D1 97 0.006274B65F02-DA5C-4504-8DCE-94523659AD13 9C622BD4-F231-4953-833F-1D3E0A9DBEA7 97 0.0063DDB8F149-BC6F-4E27-AAE9-2CFFB3F8823C 3351379A-9F9F-4101-A80C-342E2DA9B110 97 0.009274B65F02-DA5C-4504-8DCE-94523659AD13 F3430639-6677-4545-A6CB-86BEE29D402A 97 0.0148Required Output:DDB8F149-BC6F-4E27-AAE9-2CFFB3F8823C 3351379A-9F9F-4101-A80C-342E2DA9B110 97 0.009274B65F02-DA5C-4504-8DCE-94523659AD13 F3430639-6677-4545-A6CB-86BEE29D402A 97 0.0148Thanks, |
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-12-30 : 03:54:49
|
Use Row_number functionSelect * from (Select Row_number over (partitioned by WS.ID Order by NRS.Rate desc) as Srno ....) Where Srno = 1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|