Depleting a table by successive selects

[PROPAR]

Hello,
I need an efficient algorithm to SELECT rows from a table, but NOT already previously SELECTed rows.

For ex., if table rows are

Col1 Col2 Col3
Id1 201 1
Id2 300 4
Id3 150 1
Id4 162 3
Id5 190 4
Id6 305 3

SELECT1 :

"SELECT * FROM Table WHERE Col3 = 3"
will selects rows Id4 and Id6.

SELECT2 :

"SELECT * FROM Table WHERE Col2 BETWEEN 150 AND 200"
will selects rows Id1, Id3 and Id5, but NOT Id4 as it was already selected by SELECT1.

And so on ...
(I need to keep original data unchanged, in a copy or else)

Thank you for your help in this, intelligent design will be recognize in this case.

 

[vbkris]

hey,

you will have to either create temporary tables or create one huge query with a list of derived tables and exclude the repetitive data...

Known is handfull, Unknown is worldfull

 

[PROPAR]

I think I can add a tag column (1=already selected, 0 to be selected) and adding some code in a select trigger ?

But the tag column would be changed even when I'm querying from Management Studio.

I then need to know if the query is an app query from my C# .SelectCommand(...) or a Management Studio query.

 

[RiverGuy]

If you want to determine whether or not your C# application is submitting the query, set the Application Name in your connection string and check it in your stored procedure/sql statement. Here's an example on how to set the connection string:

http://www.sqlteam.com/article/sql-server-connection-string.

You can then use the APP_NAME() function in your stored procedure/sql statement to check which application is submitting the query. See here for an example:

http://msdn2.microsoft.com/en-us/library/ms189770.aspx

 

[ESquared]

Would you give some more context and explanation about why you need to do this? There are so many answers to your question that knowing more of the situation will help. Why not select all the rows you'll need to work with into the client once, then work with them in whatever order you want?