retrieving a previous record

guys,

i ll explain example

i have table columns, county capital language continent

the values are                     ind delh hindi asia

                                          w'ton eng na

                                          uk lon    eng  eur

                                       japan tok jap     asia

now when value asia, need extract previous record same continent

when come across asia record of japan, need retrieve india record.

can suggest me

 

following solution sql server 2005 , on. use partitionid see if there previous record. apply sequenceid find previous record. need have key territoryid in data for sequencing.

/****** sequencing data 2 ways row_number ******/  select sequenceid = row_number() over(order by [group], territoryid)   ,partitionid=row_number() over(partition by [group] order by territoryid)   ,territory=[name]   ,[countryregioncode]   ,[group]   ,[salesytd]   ,territoryid  from [adventureworks2008].[sales].[salesterritory]  order by territoryid     /* sequenceid	partitionid	territory	countryregioncode	group	salesytd	territoryid  4	1	northwest	us	north america	7887186.7882	1  5	2	northeast	us	north america	2402176.8476	2  6	3	central	us	north america	3072175.118	3  7	4	southwest	us	north america	10510853.8739	4  8	5	southeast	us	north america	2538667.2515	5  9	6	canada	ca	north america	6771829.1376	6  1	1	france	fr	europe	4772398.3078	7  2	2	germany	de	europe	3805202.3478	8  10	1	australia	au	pacific	5977814.9154	9  3	3	united kingdom	gb	europe	5012905.3656	10  */  

---

kalman toth, sql server & bi training; sqlusa.com

SQL Server  >  Transact-SQL