February 13, 2004 at 7:36 am
Ok lets say that I have a column that is indexed.
Is there a way to reference a value in that column by indexed position
So I could say give me a value where it's INDEXED position is (n)
Thanks
Will
February 13, 2004 at 8:12 am
SQL is a DECLARATIVE language. you are supposed to tell the engine WHAT you need not HOW to get it!
Indexes help the engine improve/determine the execution plan (based on cost!)
What are you trying to accomplish?
* Noel
February 13, 2004 at 11:59 am
Well it's not all that easy to explain really..
Lets say that you have a Parent Component relationship setup. And the View or Select that you are using puts records in a NON Linear order.
ComponentID 1
ChildID 23
ChildID 54604
ChildID 10
ComponentID 5
ChildID 99
ChildID 3467
ChildID 100
ComponentID 3
ChildID 34
ChildID 128
ChildID 100
I would like to say Where ComponentID is 3 Give me the two components before that. As you know this would require an insert into a temp table to get a linear order. Then use that NEW order to get any previous records. My thought is that because I have a clustered index on the Component id I sould be able to reference it's position in that index. Kind of like you would in an array. And in the example below you would get someting like
SELECT * FROM MyTable WHERE ComonentID = (SELECT ComponentID FROM MyTable WHERE INDEX<2)
Well I hope that I have explained it well enough. Thanks for the help
Will
February 13, 2004 at 1:18 pm
Your question is certainly valid, but you do not want to try and reference an index to accomplish your objective. One possibility for ordering your data in a linear format would be to order it within your select statement. For instance
Select * from table where ChildID in (
select max(ChildID) from table where ChildID < (
Select ChildID from table where componentID = 3))
Once you have this child id you can get the second by using this one. Just use the above query and replace 3 this componentid.
Of course it will help speed tremendously if you have the clustered index on childid and a regular index on componentid. If this helps and you want an example of how to write it all procedurally with specific columns let me know.
February 13, 2004 at 1:20 pm
Will,
The order in the RELATIONAL Model is not guarantee at the Table level that is why you should not relay on clustered indexes and use the ORDER BY clause when you need it.
I am not very sure I understand what you meant by "the two components before that"
If you are trying to maintain a parent-child with single Inheritance you can find a very good start HERE
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply