Just the most recent TOP or ??

[warway]

The following procedure returns the data I want but I need to show just the
latest instance of each s.CurrencyCode in order to populate a combobox, any
help greatly appreciated!


Alter PROCEDURE dbo.sp_ROECurrent3 @LessDate datetime

AS

SELECT s.ROEID,s.CurrencyCode, s.ExchangeRATE, s.ROEDate, o.ROEWeighting,
o.BaseCurrency, 'Weighted ROE' =

CASE

WHEN s.CurrencyCode <> o.BaseCurrency THEN s.exchangeRate - (s.ExchangeRATE
*(1+o.ROEWeighting) - s.exchangeRate)

ELSE 1

END,

CAST(o.CurrencyName AS varchar(20)) AS 'Currency'

FROM ROECurrentView o JOIN tblkpROE s ON o.ROEDate = s.ROEdate AND
s.CurrencyCode=o.CurrencyCode

GROUP BY
s.ROEID,s.CurrencyCode,s.ExchangeRate,s.ROEDate,o.ROEWeighting,o.BaseCurrenc
y,o.CurrencyName,o.CurrencyCode

HAVING MAX(DISTINCT o.ROEDate) <= @LessDate

ORDER by o.CurrencyCode



Warway

 

[Rod Scoullar]

Warway,

I'd try MAX(s.ROEDate) in the SELECT list and remove s.ROEDate from the
GROUP BY clause, but I'm likely to be wrong.

I'd also try posting to microsoft.public.access.adp.sqlserver and/or
microsoft.public.sqlserver.programming. You are more likely to find someone
able to help you.

If worst comes to worst you could try something like this.

Declare @n as integer
Declare @s as nvarchar(255)

Find the number of unique currency codes
Select @n = Count(CurrencyCode) From (Select Distinct CurrencyCodeFrom
Orders where CurrencyCode is not null) AS a

Select the top @n records from your query where XX is the text of your query
set @s = 'Select Top ' + cast(@n as nvarchar(255)) + ' * From (XX) As b'
exec sp_executesql @s