| Author |
Topic |
|
mikejohnson
Posting Yak Master
153 Posts |
 Posted - 2005-01-25 : 11:44:48
|
| I have data in a field that is ordered every other. For each record starting with a '5', i need to read the value in the next record after it, how do i do this? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-25 : 12:31:19
|
select top 1 col1 from MyTablewhere col1>5order by col1 ascGo with the flow & have fun! Else fight the flow  |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-01-25 : 12:32:23
|
| The order of data in a table is meaningless....post the DDL of the table and some sample dataBrett8-) |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2005-01-25 : 13:23:26
|
| Actually, in my case, the order of the records DOES matter. here is a better example, here are my records:6127test6137test2what i have to do is for every record starting with a 6, i need to take everything after the 7 of the next record and use it to update the record starting with 6. i have no other fields in the table that identify the records or put a relationship between 6/7 type records |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-01-25 : 13:32:02
|
| Mike,You are missing the point. There is no inherent way of saying "the next record". A database table is an unordered set. You need to define (using the data in the table) the rules for ordering the data. If it's chronological, define the date column to use; if it's numeric, define the number to use; if it's a string, (Well, I'm sure that you see the pattern without my prattling on any further).HTH=================================================================Our elections are free, it's in the results where eventually we pay. -Bill Stern, sports announcer (1907-1971) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-25 : 13:57:14
|
| well you could use a cursor...Go with the flow & have fun! Else fight the flow |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2005-01-25 : 14:48:14
|
| No, I do get the point. I know how a database works. But if i select the records from the table, they'll come out the same order they went in...which is what i need. I don't have anything to order these by, not an identity or date field or anything else that would work. I thought about using a cursor, but how would that work? i'll play around with the cursor idea... until then, anymore ideas? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-01-25 : 15:08:44
|
quote:
they'll come out the same order they went in
That's not always the case methinks. If you did a compact database or maybe a reindex, things may come out differently methinks.I highly suggest putting something in that table that you can order by to get this thing done.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
mikejohnson
Posting Yak Master
153 Posts |
Posted - 2005-01-25 : 15:10:49
|
| ok, that's a good idea and i tried that. but, i'm using bulk insert to read a text file in. since the text file only has one field, how do i have another field in the table i'm inserting into? also, no field names are in the text file..... |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-25 : 16:07:43
|
| Use DTS to transform the table. Load it into a table with an identity column. Then do your updates.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
value from next record to update current record