selecting Max Data - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

selecting Max Data

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-05 : 04:06:25

Hey guys

I know it’s earlier but I am hoping you will be able to help me

Aim-
Select the maximum “Fee_Wholesale_date” per “fee_sequence “, via the FDMSaccountno

This is my table

SELECT [FDMSAccountNo]
,[fee_sequence]
,[fee_retail_amt]
,[fee_wholesale_date]
FROM [FDMS].[dbo].[Audit_FDMS_Billing_Fees_Hist]
where FDMSAccountNo = '878226943883'
and fee_sequence = '7ZY'

and it provides the following results

FDMSAccountNo fee_sequence fee_retail_amt fee_wholesale_date
878226943883 7ZY 0.00000000 2012-11-29
878226943883 7ZY 0.01680000 2011-02-18
878226943883 7ZY 0.01732000 2012-11-29

The required result should return

FDMSAccountNo fee_sequence fee_retail_amt fee_wholesale_date
878226943883 7ZY 0.01732000 2012-11-29

would appreciate any help Available

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-05 : 04:17:10

[code]
SELECT [FDMSAccountNo]
,[fee_sequence]
,[fee_retail_amt]
,[fee_wholesale_date]
FROM
(
SELECT [FDMSAccountNo]
,[fee_sequence]
,[fee_retail_amt]
,[fee_wholesale_date]
,ROW_NUMBER() OVER (PARTITION BY FDMSaccountno,fee_sequence ORDER BY Fee_Wholesale_date DESC) AS Seq
FROM [FDMS].[dbo].[Audit_FDMS_Billing_Fees_Hist]
where FDMSAccountNo = '878226943883'
and fee_sequence = '7ZY'
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources