Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-23 : 09:06:00
|
| John writes "Okay all you propeller heads out there. Got stuck on this query and am looking for some ideas.Problem: In my application I have a documents table from which the user can use a search interface to query out individual rows representing a document. The columns in the document table vary by database that the application is using, but an unique identity column called DocID is required. What I need to do is construct a query to find the next document in sequence from the documents table where the sequence is determined by a field in the documents table that is selected by the user. First Attempt:For this example.. Docid of current document = 12 Sequence field selected is called seqSELECT TOP 1 DOCID FROM Documents WHERE (DOCID<>12) AND (SEQ>=(SELECT SEQ FROM Documents WHERE DOCID=12))ORDER BY [BegBates] ASCFirst (and Current) Issue:The above example works great UNLESS the field (SEQ in the example above) contains more than 2 instances of a single value. In this case repeatedly calling the function will just bounce you back and forth between the rows containing the first and second instance of the value. Partial Solution: My first stab at this was to determine what my query SHOULD do in this situation and decided to add a secondary sort (after the user selected sort field (SEQ) on the DOCID field which is always unique. However I still haven't worked out how to incorporate logic into my query to get the next value based on the user selected (SEQ) field and use the DocID to be the tiebreaker in the case of duplicates in the user selected field.Ideas anyone? Bueller? Bueller? Bueller?" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-24 : 10:01:43
|
First,Always post your DDL (your create table/index statements) when asking a question. Remember that your audience is wholly unfamiliar with your schema, and can't always discern from descriptions what your tables actually look like.Second,Post some sample data. Run through an example of what you want the query to return ( explicitly, using your sample data ) and compare it to what you have know.The answers will come pouring in ...quote:
John writes "Okay all you propeller heads out there. Got stuck on this query and am looking for some ideas.Problem: In my application I have a documents table from which the user can use a search interface to query out individual rows representing a document. The columns in the document table vary by database that the application is using, but an unique identity column called DocID is required. What I need to do is construct a query to find the next document in sequence from the documents table where the sequence is determined by a field in the documents table that is selected by the user. First Attempt:For this example.. Docid of current document = 12 Sequence field selected is called seqSELECT TOP 1 DOCID FROM Documents WHERE (DOCID<>12) AND (SEQ>=(SELECT SEQ FROM Documents WHERE DOCID=12))ORDER BY [BegBates] ASCFirst (and Current) Issue:The above example works great UNLESS the field (SEQ in the example above) contains more than 2 instances of a single value. In this case repeatedly calling the function will just bounce you back and forth between the rows containing the first and second instance of the value. Partial Solution: My first stab at this was to determine what my query SHOULD do in this situation and decided to add a secondary sort (after the user selected sort field (SEQ) on the DOCID field which is always unique. However I still haven't worked out how to incorporate logic into my query to get the next value based on the user selected (SEQ) field and use the DocID to be the tiebreaker in the case of duplicates in the user selected field.Ideas anyone? Bueller? Bueller? Bueller?"
setBasedIsTheTruepath<O> |
 |
|
|
|
|
|
Next item in sequence