Difficult Table Query

Question

Post reply

Difficult Table Query

RICHTER-311234

Old Hand

Points: 311
More actions
May 21, 2008 at 10:28 pm

#187998

How would a guy go about selecting data out of a table in this situation.
I want to start at the beginning of a table say itemnum 1 in the table and do a query on every item in that table.
When a certain criteria is found I want to select that row into a temp table and continue selecting each row after that into the table also, until another set of criteria is met and then continue on down through the file.
For example if I have rows 1-5000 and row 10 meets the criteria that I want to start writing to a temp table. I would want it to put row 10 into the temp table and then continue with each row after that until it comes to the next criteria say that is found in row 20. So in my temp table I would have rows 10-20, then I would want the query to continue through the table until it finds the correct criteria and start inserting then when it finds the stop criteria stop inserting.
IF this does not make sense please do let me know and I can try to be more clear, what I am asking I don’t think is that hard, however with my limited knowledge I am struggling and Would really appreciate a little help.
Thanks so much in advance!!!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Anam Verma SSCertifiable Points: 5365 More actions · Answer 1

i have few questions

1. How many criterias are there?

2. Is it possible that criteria 1 would come again after row 20 say at row 200?

3. What is the main purpose behind doing the required task this way?

preetha.sathyamoorthy Ten Centuries Points: 1180 More actions · Answer 2

Hi,

Actually, I tried something based on your explanation.

declare @i int

set @i = 0;

while ( @i < = 290)

begin

if ((@i >= 10 ) and (@i <= 20 ))

begin

insert into #temp

select * from HumanResources.Employee where EmployeeID = @i

end

set @i = @i + 1

end

select * from #temp

If you explain your requirement still clearly, I will try to find out the solution for that. Just try this.

raja_saminathan SSCommitted Points: 1607 More actions · Answer 3

Hi,

Provide us some TableDefinitions along with sample Datas and expected Results from them along with the conditions to match in the Table to help you in querying

Rajesh

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

As others have already pointed out, to really answer the question you need to post some structures and some sample code.

But, taking a stab at it, it sure sounds like you're just talking about a regular old query. Don't get hung up on this idea of walking through the data row-by-row. That's not set-based thinking. Think in terms of operating against the entire data set, all at once, or in a couple of passes, but not pushing & pulling data into temporary tables matching each row to some arbitrary value. That's what Jeff Moden calls rbar, Row-By-Agonizing-Row, processing.

Instead, determine that you have a base set of data, your table, that has to meet one piece of criteria, col1 is greater than 10, and another set of criteria, col1 is less than 20. Now you have the beginning of your query. The engine will worry about the row-by-row matches. You worry about arriving at the set:

SELECT *

FROM TableA AS a

WHERE a.Col1>10 AND a.Col1 <20

Then, if you determine that every time there's an odd number in Col1 you need to show the string "Odd" and the column value but every time it's "Even" you need to show that and the column value, again, you adjust this set, not try to walk this set or the original table, row-by-row.

SELECT a.Col1

,CASE WHEN (Col1%2=0) THEN 'Even'

ELSE 'Odd' END AS ColName

FROM TableA AS a

WHERE a.Col1>10 AND a.Col1 <20

No walking through the table. It's a set. Always think about the set that you want to arrive at and how best to arrive at it. Don't think about rows.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RICHTER-311234 Old Hand Points: 311 More actions · Answer 5

First off Let me say thanks for your prompt response, to all who replied. I hope this makes it much more clear what I am trying to accomplish.

The query I was refering to earlier would use the following data, but much much more of it. I would want to start pulling the rows where k1=200050 and k6=9 and then stop when K1!=200050 and k6=9. So for example If I could have a query do what I wanted on this table the first section it would grab would be from itemnum 2930 - 2934 then it would start skipping rows until it came to itemnum 2941 and would then select until it hit itemnum 2943. So the groupings of items I woudl like to see would be in this example

itemnums

2930 - 2934

2941 - 2943

2974 - 2991

2998 - 3001

My start point is the k1=200050 and k6=9

My stop point would be where k1!=200050 and k6=9

I need it to go through the whole table starting and stoping. Please let me know your thoughts.

item-K1 K2 K3 K4 K5 K6K7K8K9

num

293020005001001.500200020000459008290251

29311050100118.50020002000046601008290251

2932564857601600.00020000470009210033

2933987654868355.000200004800044444444

293412597869165.0002000200004951008290251

29351072702399.600200020000509008290251

293610503001369.23020002000051703008290251

2937200050220067.6002000200005251008290251

293885236956601962.77020000530009210070

29395000500121.30020002000054373008290251

29402000502201121.3002000200005551008290251

29412000500106.400200020000569008290251

294265498712120140.40020000570009210033

2943200070140266.0002000200005851008290251

29442000700525.000200020000599008290251

29452000502203275.2002000200006051008290251

294657600010249.800200020000615000056100000

294820006060141.0002000200006351008290251

294920006060258.5002000200006451008290251

2950300110078.20020002000065145008290251

29513000800199.00020002000066109008290251

2952105030040.40020002000067703008290251

295320006060326.1002000200006851008290251

2954200080159232.5002000200006951008290251

29552000801591100.0002000200007051008290251

29564000700500.00020002000071209008290251

29579025650500.00020002000072145008290251

295840005001000.00020002000073209008290251

295995162847356871000.000200007400044444444

29603000500120.00020002000075109008290251

29612000701400116.6002000200007651008290251

296210503003.40020002000077703008290251

2963105010075.00020002000078601008290251

2964300060075.00020002000079145008290251

2965300070040.00020002000080109008290251

29661045001040.000200020000815000056100000

29673482280219.95020002000082373008290251

29682000701403219.9502000200008351008290251

29693528100197.20020002000084375008290251

29702000801593197.2002000200008551008290251

29742000500375.250200020000069008290251

2975105030030.30020002000007703008290251

29762000801595111.3002000200000851008290251

297720006060677.2502000200000951008290251

29782000701401156.4002000200001051008290251

297940008002500.00020002000011209008290251

29801234567251612500.000200001200044444444

29814000600250.00020002000013209008290251

298257600010250.00020002000014500008290251

29833000800200.00020002000015109008290251

298456897412789945.000200001600044444444

2985578915321699155.000200001700044444444

2986300070080.00020002000018109008290251

298756897412789880.000200001900044444444

29883000600100.00020002000020109008290251

29891050300100.00020002000021703008290251

2990105010025.00020002000022601008290251

2991300050025.00020002000023145008290251

29922000700310.300200020000249008290251

29931050300103.30020002000025703008290251

299420006060054.1002000200002651008290251

299520006060433.3002000200002751008290251

2996200050220544.6002000200002851008290251

2997200080159475.0002000200002951008290251

29982000500225.000200020000309008290251

2999105030057.30020002000031703008290251

3000200070140711.5002000200003251008290251

30012000801597156.2002000200003351008290251

30022000800633.200200020000349008290251

3003105030023.40020002000035703008290251

30052000701405101.2002000200003751008290251

3006200050220225.3002000200003851008290251

rroque SSC Veteran Points: 295 More actions · Answer 6

SQL will only return a entire recordset. If you need to inspect some column in each row and do something with the column. You will need to use the FETCH method. Below is an example.

DECLARE @company varchar(5), @program varchar(5)

DECLARE personnel_cursor CURSOR FOR

SELECT company ,program FROM HITSInventoryPersonnel

ORDER BY company

OPEN personnel_cursor

-- Perform the first fetch.

FETCH NEXT FROM personnel_cursor

INTO @company, @program

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

If @company = 'AFL'

Begin

update HITSInventoryPersonnel set program='AFL'

where current of personnel_cursor -- update current fetched row

End

If @company = 'HITS'

Begin

update HITSInventoryPersonnel set program='HITS'

where current of personnel_cursor -- update current fetched row

End

FETCH NEXT FROM personnel_cursor

INTO @company, @program

END

CLOSE personnel_cursor

DEALLOCATE personnel_cursor

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Or, as has been sugested previously - you can do it in a set-based fashion.

this should do pretty well, especially when compared to Cursor methods. It does rely on making sure that your sequences are well formed (so you'd have to have perfectly alternating "starts and ends". Two starts or 2 ends in a row would lead to junk).

--build some test data

drop table items

create table items(itemnum int ,K1 int,K2 int,K3 numeric(18,9),K4 int,K5 bigint,K6 int,K7 int,K8 int,K9 bigint)

insert items(itemnum,K1,K2,K3,K4,K5, K6,K7,K8,K9)

select 2930,200050,0,1001.50,0,20002000045,9,0,0,8290251 Union all

select 2931,105010,0,118.50,0,20002000046,601,0,0,8290251 Union all

select 2932,564857,601,600.00,0,2000047,0,0,0,9210033 Union all

select 2933,987654,868,355.00,0,2000048,0,0,0,44444444 Union all

select 2934,12597,869,165.00,0,20002000049,51,0,0,8290251 Union all

select 2935,10727,0,2399.60,0,20002000050,9,0,0,8290251 Union all

select 2936,105030,0,1369.23,0,20002000051,703,0,0,8290251 Union all

select 2937,200050,2200,67.60,0,20002000052,51,0,0,8290251 Union all

select 2938,852369,56601,962.77,0,2000053,0,0,0,9210070 Union all

select 2939,500050,0,121.30,0,20002000054,373,0,0,8290251 Union all

select 2940,200050,2201,121.30,0,20002000055,51,0,0,8290251 Union all

select 2941,200050,0,106.40,0,20002000056,9,0,0,8290251 Union all

select 2942,654987,121201,40.40,0,2000057,0,0,0,9210033 Union all

select 2943,200070,1402,66.00,0,20002000058,51,0,0,8290251 Union all

select 2944,200070,0,525.00,0,20002000059,9,0,0,8290251 Union all

select 2945,200050,2203,275.20,0,20002000060,51,0,0,8290251 Union all

select 2946,5760001,0,249.80,0,20002000061,500,0,0,56100000 Union all

select 2948,200060,601,41.00,0,20002000063,51,0,0,8290251 Union all

select 2949,200060,602,58.50,0,20002000064,51,0,0,8290251 Union all

select 2950,300110,0,78.20,0,20002000065,145,0,0,8290251 Union all

select 2951,300080,0,199.00,0,20002000066,109,0,0,8290251 Union all

select 2952,105030,0,40.40,0,20002000067,703,0,0,8290251 Union all

select 2953,200060,603,26.10,0,20002000068,51,0,0,8290251 Union all

select 2954,200080,1592,32.50,0,20002000069,51,0,0,8290251 Union all

select 2955,200080,1591,100.00,0,20002000070,51,0,0,8290251 Union all

select 2956,400070,0,500.00,0,20002000071,209,0,0,8290251 Union all

select 2957,902565,0,500.00,0,20002000072,145,0,0,8290251 Union all

select 2958,400050,0,1000.00,0,20002000073,209,0,0,8290251 Union all

select 2959,95162847,35687,1000.00,0,2000074,0,0,0,44444444 Union all

select 2960,300050,0,120.00,0,20002000075,109,0,0,8290251 Union all

select 2961,200070,1400,116.60,0,20002000076,51,0,0,8290251 Union all

select 2962,105030,0,3.40,0,20002000077,703,0,0,8290251 Union all

select 2963,105010,0,75.00,0,20002000078,601,0,0,8290251 Union all

select 2964,300060,0,75.00,0,20002000079,145,0,0,8290251 Union all

select 2965,300070,0,40.00,0,20002000080,109,0,0,8290251 Union all

select 2966,1045001,0,40.00,0,20002000081,500,0,0,56100000 Union all

select 2967,348228,0,219.95,0,20002000082,373,0,0,8290251 Union all

select 2968,200070,1403,219.95,0,20002000083,51,0,0,8290251 Union all

select 2969,352810,0,197.20,0,20002000084,375,0,0,8290251 Union all

select 2970,200080,1593,197.20,0,20002000085,51,0,0,8290251 Union all

select 2974,200050,0,375.25,0,20002000006,9,0,0,8290251 Union all

select 2975,105030,0,30.30,0,20002000007,703,0,0,8290251 Union all

select 2976,200080,1595,111.30,0,20002000008,51,0,0,8290251 Union all

select 2977,200060,606,77.25,0,20002000009,51,0,0,8290251 Union all

select 2978,200070,1401,156.40,0,20002000010,51,0,0,8290251 Union all

select 2979,400080,0,2500.00,0,20002000011,209,0,0,8290251 Union all

select 2980,1234567,25161,2500.00,0,2000012,0,0,0,44444444 Union all

select 2981,400060,0,250.00,0,20002000013,209,0,0,8290251 Union all

select 2982,5760001,0,250.00,0,20002000014,500,0,0,8290251 Union all

select 2983,300080,0,200.00,0,20002000015,109,0,0,8290251 Union all

select 2984,56897412,7899,45.00,0,2000016,0,0,0,44444444 Union all

select 2985,57891532,1699,155.00,0,2000017,0,0,0,44444444 Union all

select 2986,300070,0,80.00,0,20002000018,109,0,0,8290251 Union all

select 2987,56897412,7898,80.00,0,2000019,0,0,0,44444444 Union all

select 2988,300060,0,100.00,0,20002000020,109,0,0,8290251 Union all

select 2989,105030,0,100.00,0,20002000021,703,0,0,8290251 Union all

select 2990,105010,0,25.00,0,20002000022,601,0,0,8290251 Union all

select 2991,300050,0,25.00,0,20002000023,145,0,0,8290251 Union all

select 2992,200070,0,310.30,0,20002000024,9,0,0,8290251 Union all

select 2993,105030,0,103.30,0,20002000025,703,0,0,8290251 Union all

select 2994,200060,600,54.10,0,20002000026,51,0,0,8290251 Union all

select 2995,200060,604,33.30,0,20002000027,51,0,0,8290251 Union all

select 2996,200050,2205,44.60,0,20002000028,51,0,0,8290251 Union all

select 2997,200080,1594,75.00,0,20002000029,51,0,0,8290251 Union all

select 2998,200050,0,225.00,0,20002000030,9,0,0,8290251 Union all

select 2999,105030,0,57.30,0,20002000031,703,0,0,8290251 Union all

select 3000,200070,1407,11.50,0,20002000032,51,0,0,8290251 Union all

select 3001,200080,1597,156.20,0,20002000033,51,0,0,8290251 Union all

select 3002,200080,0,633.20,0,20002000034,9,0,0,8290251 Union all

select 3003,105030,0,23.40,0,20002000035,703,0,0,8290251 Union all

select 3005,200070,1405,101.20,0,20002000037,51,0,0,8290251 Union all

select 3006,200050,2202,25.30,0,20002000038,51,0,0,8290251

--actual query starts here

;with startrangecte as (

select row_number() OVER (order by itemnum) RN,

itemnum

from items

where k1=200050 and k6=9),

Endrangecte as (

select row_number() OVER (order by itemnum) RN,

itemnum

from items

where k1!=200050 and k6=9)

select startrangecte.rn seq,

items.*

from startrangecte

inner join endrangeCTE on startrangecte.rn=endrangeCTE.rn

join items on items.itemnum>=startrangecte.itemnum and items.itemnum<endrangeCTE.itemnum

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

rroque SSC Veteran Points: 295 More actions · Answer 8

Instead of manually creating the table import it using dts or ssis.

John Rowan SSC Guru Points: 56440 More actions · Answer 9

Richter,

Stay away from the cursors/loops solutions and stick with the set based methods that Grant and Matt have posted. If you can, post back some feedback so we all know if you've been helped.

Another question I would ask is, what are you going to do with it once you have it in a temp table? You may not even need the temp table at all.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 10

I'll go one further, use Matt's solution. That looks good from where I sit.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

RICHTER-311234 Old Hand Points: 311 More actions · Answer 11

Guys this is great, it works perfect in 2005, however it turns out I am going to need it to work in 2000, is there a simple conversion to make it work there?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

RICHTER (5/22/2008)
Guys this is great, it works perfect in 2005, however it turns out I am going to need it to work in 2000, is there a simple conversion to make it work there?

gets a lot less obvious - but it can be done. 2000 solution at the bottom:

--build some test data

drop table #ranges