Top not 1 but 2 in subquery
-
hey all,
I have the following code:
-----------------CODE-------------
select
[Transaction Date],
[Client1_Title],
[Client1_Forename],
Client1_Surname,
Client1_DOB,
[policy number],
[habitual residence],
[Overseas Location],
--travelpolicy.[policy_urn],
(select top 1 [Endorsement_Title] from Endorsement_Std where travelpolicy.policy_urn = Endorsement_Std.[policy_urn]) as Endorsement1,
[eff_date],
[Expiry_Date],
[Agent name]
from
accounts
INNER JOIN
(SELECT MAX([transaction counter]) AS [transaction counter], [policy_num]
FROM travelpolicy
GROUP BY [policy_num]) MaxTransAccounts ON MaxTransAccounts.[policy_num] = accounts.[policy number]
INNER JOIN
TravelPolicy
on
accounts.[policy number]=TravelPolicy.Policy_Num
and
Policy_Status not in ('Renewal Quote', 'NTU Renewal', 'NTU')
and
MaxTransAccounts.[transaction counter] = TravelPolicy.[transaction counter]
left outer join
agentdetails
on
travelpolicy.Agent_URN = agentdetails.[Agent URN]
------------------CODE END---------------
Now where i have a column called endorsement1 - i really need another called endorsement 2, and another endorsement 3.
So question - how do i get the 2nd row but not first one, then get the 3 row but not the first 2?
Many thanks
Dan
-
A couple of ideas:
1. Use a function pass parameters of policy_urn and row number. Then set the rowcount to the row number and return that row: eg (not tested but gives the idea)
create function fn_Endowment_Title (@policy_urn int, @row int)
returns varchar (50)
as
begin
declare @endowment_title varchar (50)
set rowcount @row
select @endowment_title = endowment_title
from Endorsement_Std where travelpolicy.policy_urn = @policy_unr
-- must reset rowcount
set rowcount 0
return @endowment_title
end
-- usage: select dbo.fn_endowment_title ([policy_urn], [row]) as EndorsementN
2. Someone will probably say that changing rowcount is not a good idea so here is another method:
( select top 1 endowment_title
from (select top 2 endowment_title from Endorsement_Std where travelpolicy.policy_urn = Endorsement_Std.[policy_urn] order by endowment_title) ) as Endorsment2
order by endowment_title desc) as Endorsment2
The inner select is ordered by endowment_title and the outer select picks the first row when ordered backwards.
HTH
Jeremy
-
TOP clause without ORDER BY does not returns deterministic values.
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply