Top 2 in the one record query 1

[shaunk]

I hope this is the correct forum.

I need a view that returns the Top 2 (most recently added) records in a table called Person_For_Contact. I want the top 2 record items to be contained in the one record i.e PFC_1_Address1, PFC_1_Phone etc and PFC_2_Address1, PFC_2_Phone etc.
I know how to do this in a stored procedure, but I need a view to do the job as I am linking to it from an Access database.
I have not had any success in a view.

Thanks

 

[thegeezza]

Hi,

Have you considered using your stored procedure to create/calculate the data you require and add it as a record to a table? You could then simply create a view on this table.

Cheers,
Thegeezza

 

[maswien]

You probablyly can do this:

select 
t2.*, t3.*
from 
 (select top 1 * from (select top 2 * from myTable order by dt desc) t0 order by dt desc ) t2, 
 (select top 1 * from (select top 2 * from myTable order by dt desc) t1 order by dt asc ) t3

 

[shaunk]

Just getting back on this one:
Code below did the job

CREATE TABLE #Array_NOK ( 
			Array_Key smallint Identity(1,1) , 
			Array_NOK_individual_client_relshp_id numeric(5),
			Array_NOK_Client_Id numeric(5),
			Array_NOK_Name3 varchar(30),Array_NOK_Name2 varchar(30),Array_NOK_Name1 varchar(30),
			Array_NOK_Address3 varchar(60),Array_NOK_Address2 varchar(60),Array_NOK_Address1 varchar(60),
			Array_NOK_Suburb varchar(60), Array_NOK_PostCode varchar(12),Array_NOK_State varchar(3),
			Array_NOK_Home_Phone varchar(20),Array_NOK_Work_Phone varchar(20)
			)

INSERT INTO  #Array_NOK (Array_NOK_individual_client_relshp_id ,Array_NOK_Client_Id,
			Array_NOK_Name3 ,Array_NOK_Name2 ,Array_NOK_Name1 ,
			Array_NOK_Address3 ,Array_NOK_Address2 ,Array_NOK_Address1 ,
			Array_NOK_Suburb , Array_NOK_State , Array_NOK_PostCode ,
			Array_NOK_Home_Phone ,Array_NOK_Work_Phone )
			SELECT 
			individual_client_relshp_id,NOK_Client_Id,
			NOK_Name3, NOK_Name2, NOK_Name1,
			NOK_Address3, NOK_Address2, NOK_Address1,
			NOK_Suburb, NOK_State, NOK_Postcode,
			NOK_Home_Phone, NOK_Work_Phone
			FROM 
			(SELECT Top 100 percent V_NOK.* 
			 FROM	  	V_Hahs_IMRU_CAxF_SR_Details AS V_SR
			 LEFT  JOIN	V_Hahs_IMRU_NOK V_NOK 
			 ON V_SR.SR_Client_Id =  V_NOK.NOK_Client_Id	
			 WHERE 	V_SR.SR_ID = @SR_ID 
			 ORDER BY individual_client_relshp_id DESC ) V_NOK	
SELECT  V_SR.*,
	NOK_1.Array_NOK_Name3 as NOK_1_Name3,NOK_1.Array_NOK_Name2 as NOK_1_Name2,NOK_1.Array_NOK_Name1 as NOK_1_Name1,
	NOK_1.Array_NOK_Address1 as NOK_1_Address1, NOK_1.Array_NOK_Address2 as NOK_1_Address2, NOK_1.Array_NOK_Address3 as NOK_1_Address3,
	NOK_1.Array_NOK_Suburb as NOK_1_Suburb, NOK_1.Array_NOK_State as NOK_1_State, NOK_1.Array_NOK_Postcode as NOK_1_Postcode,
	NOK_1.Array_NOK_Home_Phone as NOK_1_Home_Phone, NOK_1.Array_NOK_Work_Phone as NOK_1_Work_Phone,
	NOK_2.Array_NOK_Name3 as NOK_2_Name3,NOK_2.Array_NOK_Name2 as NOK_2_Name2,NOK_2.Array_NOK_Name1 as NOK_2_Name1,
	NOK_2.Array_NOK_Address1 as NOK_2_Address1, NOK_2.Array_NOK_Address2 as NOK_2_Address2, NOK_2.Array_NOK_Address3 as NOK_2_Address3,
	NOK_2.Array_NOK_Suburb as NOK_2_Suburb, NOK_2.Array_NOK_State as NOK_2_State, NOK_2.Array_NOK_Postcode as NOK_2_Postcode,
	NOK_2.Array_NOK_Home_Phone as NOK_2_Home_Phone, NOK_2.Array_NOK_Work_Phone as NOK_2_Work_Phone,
		INTO #Temp_SR_NOK_Issues 
FROM 	V_Hahs_IMRU_CAxF_SR_Details V_SR
	LEFT JOIN #Array_NOK as NOK_1
	ON  NOK_1.Array_NOK_Client_Id = V_SR.SR_Client_Id  and NOK_1.Array_Key = 1
	LEFT JOIN #Array_NOK as NOK_2
	ON  NOK_2.Array_NOK_Client_Id = V_SR.SR_Client_Id and NOK_2.Array_Key = 2

Sorry about the format.
The trick was to populate a table with an identity column so the table acts as an array and the identity column as the subscript.