Join 136,067 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,594 people online right now. Registration is fast and FREE... Join Now!
Ive done some searching and strangley I couldnt find an answer. I thought this would have come up often enough
Basically all I want to do is write a new record to Row No.1 in my table and move the present data in the table down i.e. Row 1 moves to 2 and Row 2 moves to 3 and so on...
Usually it is the internal matter of the database where your record is stored. Even two consecutive select query is allowed to return the results in different order (if no sorting is specified) So if you want to sort your records in a specific order, then specify an order by clause in your select and a column that defines the place of the given record. You can put the rank of that record into your custom column. When you want to push the whole thing down, simply run an update that increases the rank by one, and set the rank of the new record to one.
What 1lacca said, database don't care about order. Now you said:
QUOTE(davkav @ 8 Apr, 2008 - 05:46 AM)
Basically all I want to do is write a new record to Row No.1 in my table and move the present data in the table down i.e. Row 1 moves to 2 and Row 2 moves to 3 and so on...
WHY?!? This is a very bad idea. There's a reason no one would tell you how to do it. You should never be concerned with who's first, only if there is valid data that you can use to get the results you need. So, you want to order the data by based on sequence of insertion? Not a problem, SQL Server has an auto number mechanism built in...
In your table, add an IDENTITY column. We'll call it Seq. You usually plan for them in table creation, e.g.
CODE
create table Tank_Level( Seq int IDENTITY(1,1) NOT NULL, Date_Time datetime...
But you can put it in with an "Alter Table" if you need to.
Now, the identity column is sequential, it increments on each insert. You want the last row to be the first row? Simple, just present it that way:
CODE
select * from Tank_Level order by Seq desc
You can even grab the current max number, if you want to display Seq in a reverse order:
CODE
select (Seq-IDENT_CURRENT('Tank_Level'))+1 as Seq from Tank_Level order by Seq desc
The beuty of database like this, is You sort the data qhen You query it. It will allways be sorted by some logic, do not treat data tables like excel tables.
So, if You add an "auto increasement" ID to each row, and query by that You can sort the rows by time of adding them relative each other. (mm so the highest ID will be the lastone added, the lowest ID will be the firstone added)
Thanks guys. Yeah i think i have it stuck in my head thinking like excel.
Never even dawned on me to use a Index column, im a noob at DB
One thing though. I have the Seq column as the last column in my design and it auto increments. but when i try to write to the database now, in VB, its asking for data to be written because i now have 6 columns.
The first syntax looks fine, except I believe "insert INTO Tank_Level" may be required. You never insert an auto increment field, they won't even let you unless you ask real nice.
Also, if all you're doing for Date_Time is passing a now, you should let the database pick the current time. e.g.
It looks like this Seq field is not even required. The entire process could have been done using the Date_Time field instead. A simple descending sort on that field would yield the same results.
If the date_time field is of the datetime data type, its granularity is approximately 3.33ms. Unless the data is being inserted faster than one every 5 ms, the data in that field should be unique. (And if he is pushing data in that fast, I would hate to be the one ensuring that he has enough drive space for the massive growth of the database files!) This would change if the data type is smalldatetime which has a granularity of about 1 minute and would push the gap between inserts to 90 seconds to guarantee a unique stamp.
If the date_time field is of the datetime data type, its granularity is approximately 3.33ms. Unless the data is being inserted faster than one every 5 ms, the data in that field should be unique. (And if he is pushing data in that fast, I would hate to be the one ensuring that he has enough drive space for the massive growth of the database files!)
It is enough if data is pushed at the same time once, and it CAN happen only if a total of 2 records are inserted (Murphy's law and some nice examples at the Daily WTF). I know that this seems to be a theoretical debate from one side, but such design decisions can lead to systems failing spectacularly and misteriously. Sequences/auto increment fields were invented for a reason, and this is one of them. It is not the only solution, but using any kind of date_time type in such a scenario is a design fault.
| 