|
sqldev6363
Yak Posting Veteran
54 Posts |
 Posted - 2012-03-04 : 23:57:37
|
| here is the logic in detailed.create table #temp (id int,reqNo int,code int)insert into #temp values(1000,10,4)insert into #temp values(1000,08,4)insert into #temp values(1000,09,4)insert into #temp values(1000,07,2)insert into #temp values(1000,06,4)insert into #temp values(1000,05,4)insert into #temp values(1001,09,4)insert into #temp values(1001,08,4)insert into #temp values(1001,07,5)insert into #temp values(1001,06,4)insert into #temp values(1001,05,0)insert into #temp values(1001,04,4)insert into #temp values(1001,03,4)insert into #temp values(1001,10,2)insert into #temp values(1002,09,4)insert into #temp values(1002,08,4)insert into #temp values(1002,07,5)insert into #temp values(1002,06,1)insert into #temp values(1002,05,4)insert into #temp values(1002,04,4)select * from #temp where ID = 1000 order by reqNo descI have a table with some values like this.I need a query or function for the logic below.First we need to order by desc based on reqNo for each ID.we need to check the "Code" is having 4 or 5 value for each ID, if it has 4 or 5 Then we need to check whether the first ID in the desc order(reqNo) is having 1 or 2 or 3 for the "code".If it has either value then we need to skip that row and then just go on to the value where we will find 4 or 5 "code" for a particular ID.If the value for "code" field is having 4 or 5 and if it is followed by 1 or 2 or 3 then we need to pick the prior value of that.that means we need to pick the min value of 4 or 5 "code" prior to that.let me put in example.just look for the id 1000 if we put in desc order by reqNo we have the "code" values are like this.select * from #temp where ID = 1000 order by reqNo deschere in the above query code 4 followed by 2 at reqNo 7. so we need to retrive the 3rd record. i.e.,1000,8,4select * from #temp where ID = 1001 order by reqNo desc in the above query in order by reqNo desc the first value is 2 for "code". if it is 2 then we need to skip that and check for the value 4 or 5 then it is followed by 0 then just go down. if you didnt find 1 or 2 or 3 followed by 4 or 5 then we have retrieve the min value of "reqNo". output we need to retrieve is 1001,3,4 record, because that is the min value of reqNo. select * from #temp where ID = 1002 order by reqNo descIn the above query the 5 followed by 1 so we need to retreive the record which is min of the above 4 or 5 "code"values.output value should be 1002,7,5I need a query in function or in any other thing by using while loop or any kind. I would be having the records around 50k.Here we may pass ID as parameter can any one please help me out to get query for the above logic.thanks in advancedev |
|
retrieve a record based on the logic