0

How to retreive the newest record in a database based on datetimefield

What is the best way to retreive the newest record in a database based on datetimefield select afield from a table where max(datefield) select top 1 afield from a table order by (datefield) desc Are the other options to do this ?

22nd Aug 2017, 7:54 PM
sneeze
sneeze - avatar
15 Antworten
+ 2
@Sneeze anyways, I think you need a subquery, to get your desired field from a row having most recent datetime value. Something like: Select <yourDesiredField> from <tableName> where <recordUpdateField> = (Select max(<recordUpdateField>) from <tableName> )
22nd Aug 2017, 10:23 PM
Ipang
+ 2
@Sneeze then use: Select max(adatefield) from atable; This will return the most recent datetime field value. I hope I got you right, you only want the datetime field, not the row? Hth, cmiiw
22nd Aug 2017, 8:50 PM
Ipang
+ 2
@Sneeze in MS Sql Server "TOP" is used, "LIMIT" is used with MySql. https://www.w3schools.com/sql/sql_top.asp
22nd Aug 2017, 9:00 PM
Ipang
+ 2
LOL @Sneeze good for you, got me confused there a bit how could it be.
22nd Aug 2017, 9:42 PM
Ipang
+ 1
another option: SELECT afield FROM table ORDER BY datefield DESC LIMIT 0,1
22nd Aug 2017, 8:25 PM
Luca
Luca - avatar
+ 1
Select * from atable where atable.adatefield > 'yyyy-mm-dd';
22nd Aug 2017, 8:38 PM
Ipang
+ 1
@sneeze how bad 😞
22nd Aug 2017, 8:42 PM
Luca
Luca - avatar
+ 1
thanks ;-)
22nd Aug 2017, 9:53 PM
sneeze
sneeze - avatar
+ 1
true, thanks a lot
22nd Aug 2017, 10:24 PM
sneeze
sneeze - avatar
0
Looks very neat. Unfortunately my sqlexpress does not recognize LIMIT as keyword. so the query exceutes with ""incorrect syntax near LIMIT"
22nd Aug 2017, 8:36 PM
sneeze
sneeze - avatar
0
@Ipang I do not know the exact datetime. I just want the most recent value of the datetime field.
22nd Aug 2017, 8:43 PM
sneeze
sneeze - avatar
0
@ luca Do you know whether LIMIT is limited to a special version of sql
22nd Aug 2017, 8:55 PM
sneeze
sneeze - avatar
0
@Ipang I do not actually want the datetimefield. I do not want the record. There is afield I want the value of that field from the row that has the most recent datatimefield.
22nd Aug 2017, 8:58 PM
sneeze
sneeze - avatar
0
Is there a difference between sql server and the c# enviroment ? I use this code to query the database. It seems the answer I get in c# are different from the sql server. Where the sql server is correct but I need my results in the c# enviroment. try { cnn.Open(); cmd = new SqlCommand(sql, cnn); reader = cmd.ExecuteReader(); while (reader.Read()) { MessageBox.Show(reader.GetValue(0) ); } reader.Close(); cmd.Dispose(); cnn.Close(); } catch (Exception ex) { MessageBox.Show("Can not open connection ! "); }
22nd Aug 2017, 9:11 PM
sneeze
sneeze - avatar
0
Oeps I found my mistake. It was in the sql-statement "Select afield from atable where secondfield = secondfield" Not using the variable secondfield but just have plain text there. Still I do not understand why it did return a record. But when I use the variable it does not return any result anymore as it should.
22nd Aug 2017, 9:37 PM
sneeze
sneeze - avatar