| Author |
Topic |
|
topokek
Starting Member
7 Posts |
 Posted - 2015-03-09 : 12:30:23
|
| Hello, I have a table written in to sql by the program Wincc flex wich is currently constantly saving data in to it. I want to reed this data back in to Labview and update it’s content on Labview constantly to plot a graph that keeps updating over time. I have achieved this after some research but my problem is that I need a way of just reading the last row (meaning most recent row, not last row as the bottom one) inserted in to the table written by wincc and saved in sql WITHOUT having to read all the data and selecting for example the higest value of a timestamp column (which is added by wincc) because this would cause too much lag since the table has a lot of elements and I need to constantly read it. One of the main problems is that the data written by wincc is written in a circular buffer, which does delete 20% of the data when it fill’s up and start writing new data in to the free space, so that the temporal order of the data does nothing to do (at least nothing simple) with the index order in the table. Is there anything like a method to save the index of the last row modified in the table so that labview could read it and directly access the most recent recordset in the table just by index specification on a query? (My apologize for the structure of the question and probably bad English) |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2015-03-09 : 12:36:14
|
| [code]SELECT TOP (1) *FROM YourTableORDER BY TimestampColumn DESC[/code] |
 |
|
|
topokek
Starting Member
7 Posts |
Posted - 2015-03-09 : 12:40:17
|
quote:
Originally posted by Ifor
SELECT TOP (1) *FROM YourTableORDER BY TimestampColumn DESC
If I use order this would require to read all data in the table and then ordering it, wich causes lag. I just want to reed last row. or am I wrong? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-09 : 12:48:36
|
quote:
Originally posted by topokek
quote:
Originally posted by Ifor
SELECT TOP (1) *FROM YourTableORDER BY TimestampColumn DESC
If I use order this would require to read all data in the table and then ordering it, wich causes lag. I just want to reed last row. or am I wrong?
No it just retrieves one row.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
topokek
Starting Member
7 Posts |
Posted - 2015-03-09 : 13:00:57
|
quote:
Originally posted by tkizer
quote:
Originally posted by topokek
quote:
Originally posted by Ifor
SELECT TOP (1) *FROM YourTableORDER BY TimestampColumn DESC
If I use order this would require to read all data in the table and then ordering it, wich causes lag. I just want to reed last row. or am I wrong?
No it just retrieves one row.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Yes, I know this method only retrieves one row, but to do so, it needs to order the hole table by desc an then it answers. This requiers to manage all the data wich is slow, rigth? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-09 : 13:04:10
|
| If the clustered index has that column as the first column or if you have a different index with that column as the first column, then that solution will be very fast. It is an efficient solution even if the table has billions of rows in it.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
topokek
Starting Member
7 Posts |
Posted - 2015-03-09 : 13:09:26
|
quote:
Originally posted by tkizer
If the clustered index has that column as the first column or if you have a different index with that column as the first column, then that solution will be very fast. It is an efficient solution even if the table has billions of rows in it.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
This is want I want to do, just refer to the index of the column I want (wich is the newest column in the table) but i dont know how to do this |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-09 : 13:14:54
|
| You don't refer to an index. You refer to a column. SQL handles which index to use. Just make sure the column is indexed.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
topokek
Starting Member
7 Posts |
Posted - 2015-03-09 : 13:43:03
|
I dont understand your answer or how to solve my problem, It would be very helpfull some link in to a manual or reference. Remember that I want to retriver the most recent column added in a table without using max/order (and so) and i dont have a ID column.quote:
Originally posted by tkizer
You don't refer to an index. You refer to a column. SQL handles which index to use. Just make sure the column is indexed.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
|
|
|
|
topokek
Starting Member
7 Posts |
Posted - 2015-03-09 : 13:44:43
|
| and the most recent column could be located anywhere in the table becouse it is a circular buffer |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-09 : 14:42:19
|
| Did you create an index on the column? If not, you can use CREATE INDEX to create one. Here is an example: CREATE INDEX ix_TableName_ColumnName ON TableName(ColumnName). Then you are all set to use TOP 1/ORDER BY.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
How to get last updated row in a table?