| Author |
Topic |
|
jpiscit1
Posting Yak Master
130 Posts |
 Posted - 2002-11-18 : 20:59:12
|
| Im looking to query for the last record in the data base. Sounds pretty simple. Can someone help? I do have a date_time column and a timestamp column.Thanks,John |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-18 : 23:35:50
|
quote:
Sounds pretty simple.
Sounds like a homework assignment - ever heard of BOL?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-11-19 : 03:24:43
|
| Homework in John's age? Naaa...I don't think so.So, try a TOP 1 select statement with a descending ORDER BY... |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2002-11-19 : 08:21:21
|
| Once again!! This is not homework folks. Is this forum for helping or making judgement? I am a controls engineer for a large paper company. Not a student in college. Any helpful responses are appreciated....John |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2002-11-19 : 08:23:21
|
| By the way...what is BOL? I admit, I am new to SQL.John |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-11-19 : 08:27:16
|
| BOL means Books On Line, an online help for SQL server. It's included in every SQL instalation. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-11-19 : 08:42:01
|
| You must excuse our antipathy occasionally.....but some 'bad days at work' can lead to poor form when answering questions in the forums (which is done voluntarily and for free!!)....especially when we see the same (style) question again and again...(even with 20000+ posts in all the forums here, you would be surprised at how relatively few unique problems are posted here!!!)BOL = Books On-Line....SQL Server's help....installed with the product....filled with 95% of everything you need to know....and the remaining 20% (and I KNOW my maths doesn't add up) is in a book by Ken Henderson (do a forum search here for the name (and the corresponding recommendations!!!)).Going back to the beginning and clarifying some things for you....LAST is not a relational database concent....nor is FIRST....so there is no true answer to your question...however, TOP and BOTTOM are valid R-DB concepts...but these only apply to SORTED DATA...and sorting implies an element of control over the order of the data.....most relational data is saved (and accessed) unordered...sounds wierd, but that's the way it is...But if the question is re-phrased/re-structured to be..."how do I get the record with the latest timestamp value"....then "SELECT TOP 1 * FROM TABLE1 ORDER BY DATETIMECOLUMNAME DESC" will solve your problem.Secondly....a timestamp column is not the same as a DATETIME column....the data in it is more an "internal number/value guaranteed to be unique by SQL server"....TRY an example of same...and look at the results....getting an 'intelligible' order on it, might be difficult.regards.........A |
|
|
|
burbakei
Yak Posting Veteran
80 Posts |
Posted - 2002-11-19 : 09:28:56
|
| you can use the system function @@DBTS. timestamp is unique within a databaseand @@DBTS returns the current value of timestamp for the current database.so you can writeSELECT *FROM tableWHERE Time_Stamp_Col = @@DBTSwhich returns the last record. but @@DBTS is also affected by updates.but it is only for one table not the last affected record within the database.so if you want to get the last record you must use something like INFORMATION_SCHEMA.COLUMNS and dynamic sql. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-19 : 09:49:01
|
quote:
which returns the last record. but @@DBTS is also affected by updates. but it is only for one table not the last affected record within the database. so if you want to get the last record you must use something like INFORMATION_SCHEMA.COLUMNS and dynamic sql.
This post makes too many assumptions and is misleading. The original question never said "last affected", just "last record". And what's this piece about INFORMAION_SCHEMA.COLUMNS view? The only "last" thing that may help you find is the column with the highest ordinal position. Now, even though the word "record" is mis-substituted for the word "row", neither a record nor a row will be enumberated in this view ...Andrew is very correct about last/first/next/previous ... all these things imply knowledge of physical storage. In the relational model, you can only select a row based on its column values. Nothing else. There is not concept of row ordering.jpiscit1, you need to further qualify your requirements and any schema information important to the question.Jay White{0} |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2002-11-19 : 12:47:46
|
| Your correct. The post does make to many assumptions. Probably because i said "last record in database" when in fact I meant "last record in Table".Here is my situation. We automatically write to a MS SQL database through our Human Machine Interface (HMI) system on the production floor. The criteria that determines when we write is defined in our industrial computers (PLC's). These PLC trigger our HMI to write a record which captures production data at that time. Unfortuantely, like many cases, the data captured is only part of what we need. The other part of the data needs to be entered by an operator. In order for me to effectively do this, and not jeopardize them writing over the wrong record, I need to specify the LAST RECORD written in the where clause. So in essence what I am looking to do is UPDATE fieldsFROM mytableWHERE criteria = the LAST record in the table.Hoefully this is better defined. Sorry for the confusion. I did not see anything in the forum search that related specifically to this.Thanks |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-11-19 : 12:56:20
|
| Quick comment about "TOP 1" ....From what I can tell, this is not an efficient way to get the last record. According to the query analzer,SELECT TOP 1 * FROM Table ORDER BY DateStamp DESCis MUCH slower thanSELECT A.* FROM Table A INNER JOIN(SELECT Max(DateStamp) as MaxStamp) B on A.DateStamp = B.DateStampPut them side by side in the query analyzer as a two step query and the first one takes 95% of the processing time!!The TOP 1 solution requires the entire table to be sorted and then 1 row is returned. The MAX() solution just uses indexes pretty quickly.So I might recommend the second option, or a variation of it. I don't like the idea of sorting an entire table or view just to return 1 record!Any thoughts?- JeffEdited by - jsmith8858 on 11/19/2002 12:59:00Edited by - jsmith8858 on 11/19/2002 12:59:41 |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-11-19 : 13:21:19
|
| You're absolutely right Jeff. It is the slowest solution, but considering that John is a total newbee to SQL, giving him a complicated one didn't seem to me as the right approach right now... |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2002-11-19 : 14:17:17
|
| Thanks to all of you. And thanks even more for putting up with me. Your input was very valuable.John |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-19 : 17:22:03
|
Putting up with you? Aw - come on, now you're just trying to make me feel even worse (boy did I make the wrong call...) --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-11-20 : 05:35:38
|
| couple of observations.....a)jpiscit1..."welcome to SQLTeam"..."Putting up with you? Aw - come on, now you're just trying to make me feel even worse (boy did I make the wrong call...)"......sometimes we loose the run of ourselves....b)SELECT A.* FROM Table A INNER JOIN (SELECT Max(DateStamp) as MaxStamp) B on A.DateStamp = B.DateStamp should have read.....SELECT A.* FROM Table A INNER JOIN (SELECT Max(DateStamp) as MaxStamp FROM TABLE) B on A.DateStamp = B.DateStamp c)jsmith8858.....I am not getting the same sort of perofrmance results you suggest you are getting....in fact on 2 different queries i got the 2nd query taking up 55-60% of the overall plan costI can see the principle of the structure of the query, but not how (or under what condition) it is meant to be better.also a SET ROWCOUNT 1, SELECT * FROM TABLE1 ORDER BY DATETIMECOLUMNAME DESC seems to be another equivalent action...Performance numbers for me are coming off a small database.....maybe DB size matters in the numbers??? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-11-20 : 07:49:26
|
| Actually, it should read:SELECT A.* FROM Table A INNER JOIN (SELECT Max(DateStamp) as MaxStamp FROM TABLE) B on A.DateStamp = B.MaxStampSorry, I typed it quickly as an example!I used a table with actually about 20,000 records. Also the field I used was an indexed field (in this case, the datestamp field). Not sure if that makes a difference. It was only a quick example. But from the execution plan, the second seems much more efficient.- Jeff |
|
|
|
sherrer
64 Posts |
Posted - 2002-11-20 : 15:09:31
|
| jsmith,I did some playing around with your method and got a few different results.First, if you are ordering on an indexed field it is much quicker to use top 1. If you are using an non-indexed field your method is much quicker, and of course if the field is not unique, it returns more than one row. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-11-20 : 16:07:16
|
| you know what ... I got different results this time, too. I swear the first few times I tried it the first was slower. But then I tried it again today and they came out the same, and I used an even bigger table. I think I used a view the first time if that was a factor...Oh well ! Long live TOP 1 !!!!- Jeff |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-11-21 : 02:01:52
|
| prolly caching - very hard to assess which method is quicker by just running it and comparing time before and after - how did you measure?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
jpiscit1
Posting Yak Master
130 Posts |
Posted - 2002-11-25 : 09:19:18
|
| I ended up using Andrew's suggestion. This worked well.SELECT A.* FROM Table A INNER JOIN (SELECT Max(DateStamp) as MaxStamp) B on A.DateStamp = B.DateStamp should have read..... SELECT A.* FROM Table A INNER JOIN (SELECT Max(DateStamp) as MaxStamp FROM TABLE) B on A.DateStamp = B.DateStamp Thanks again folk!! |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2002-12-06 : 11:47:31
|
| >> I'm looking to query for the last record [sic] in the database [sic]. Sounds pretty simple. Can someone help? I do have a date_time column and a timestamp column. <<This whole question is wrong. Tables are sets of rows; sets have no ordering, so asking for "first", "last" , "next" etc. makes absolutely no sense. That is how sequential files and navigational database work. Rows are not records -- MAJOR differences. Next, a database is not a table. Were you trying to ask about finding the most recent row, based on the DATETIME column? Since you also woudl not post DDL for anyaone trying to answer your question, I will make a guess about names SELECT * FROM Foobar WHERE foo_date = (SELECT MAX(foodate) FROM Foobar);You need a basic course in relational databases before you worry about a particular product. --CELKO--Joe Celko, SQL Guru |
|
|
|
Next Page
|
Last record