|
Hi
What will be the SQL SELECT statement to select only the last row of a table. I am using Microsoft SQL server. Please Help me.
Tutul
|
|
|
|
|
Sorry for my english...
You should to create a new field (for example "created_on") in your table, which type is TIMESTAMP. When you inserting a new record in the table this field will automatically filled within the time of creation of the new record...
So, the request is:
SELECT * FROM myTable ORDER BY created_on DESC LIMIT 1
|
|
|
|
|
If the table has a unique ID column (primary key) then u can Select the last record from the table as:
Select [Fields Name] from [Table Name] where [Pk Field name] in (Select MAX(Pk Field name) as nID from [Table Name])
e.g.,
Select employeename,Employee_ID from tbl_emp where Employee_ID in (Select MAX(Employee_id) as nid from tbl_emp)
Regards
Anyla
|
|
|
|
|
Hi
If you have a date field in your table or some other field which can sort the recordset.If yes then try this
SELECT top 1 * FROM tblA ORDER BY DATE1 DESC
or
SELECT top 1 * FROM tblA ORDER BY DATE1 ASC
i hope this helps
Mandar Patankar
Microsoft Certified professional
|
|
|
|
|
Hi
What will be the SQL SELECT statement to select only the last row of a table. Please Help me
Tutul
|
|
|
|
|
Try This One It Will Help You
SELECT top 1 * FROM <tablename> ORDER BY CloumnName desc
Annpurna Tiwari
jr.Technology Architect
IIPL Company
Nagpur
(M.S)
India
|
|
|
|
|
Hi
Can anyone tell me is there any way to store different language data(in particular spanish) into sql server. If yes please tell me
Thanks in advance
|
|
|
|
|
SQL server (or any database that supports Unicode text, for that matter) doesn't care what 'language' the text being stored belongs to. As long as your fireld types ar NVarchar or NText, you can store any sequence of Unicode characters you want, and they will be returned accurately.
If you need the sorting algorithms to operate correctly for your language, then you need to change the collation setting for the server to the proper language. This however, just affects what order will result from sorting recordes containing the text. The data is agnostic: what goes in is what comes out.
Note that the results of quees on Unicode data my sometimes look wrong, if the font used to display the data does not contain the unicode charaters in the text, in which case an empty 'box' is usually displayed for the missing character. Arial seems to support the widest range of characters.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Hi
I need to query into MS Access database using c++. The search should uses a general date but I could not work out how-to.
i.e. the field in the db is of Date/Time, which formats as dd/mm/yy hh:mm:ss
How should I fill in this sql statement? (the XXXX bit, and DateAndTime is the name of the field.)
SELECT * FROM MyTable WHERE DateAndTime='XXXX';
Thx to anyone who helps!
|
|
|
|
|
Since the date is stored with high precision (i would guess as a function of the exact time the entry was made, like Now()), a general date query is likely to have many misses (the hh:mm:ss parts may get defaulted to 00 and cause a candidate to fail the equality match). I would use a range query instead, (i.e Between "XXX - 12H" and 'XXX + 12h', or present a selection list of candidate dates queried from the database. The string format of the date is also dependant on the locale setting of the computer running you app, so using the general date format is risky dd/mm/yy works for some locales, others expect mm/dd/yy. I would format the date using th 'odbc canonical' format which is locale immune:
"XXXX" = "{ts 'yyyy-MM-dd HH:mm:ss'}".
Even better, use a parameterized query and pass the date value(s) as a parameter of type OLEDATE.
You should also be aware that Access stores the dates as local time relative to the zero date , so changing the time zone can cause the dates to appear differently in different time zones...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Hey all,
I'm working with a software package that relies on the security of the server to protect the database, meaning the database is entirely in plain english, and there is no double checking or check sum features to verify the data.
The server is accessable by all domain users(go figgure?) and therefore so is the database. The problem I am having is certain users have figgured this out and will go in to the db to change their access level and passwords of others (just for the sake of being malicious)
I have contacted the developers of the package and they basically said, "Sorry, mate, can't help you, we didn't design the system to be used in a hostile environment like yours". So I have no help from them, but still have the problem.
I need a way to track the transactions happening on the access_level column of the users table.
I've tried using the sql profiler, but i only know how to trace current transactions not previous tranactions so if the server gets rebooted, or someone logs into the console they see that its running and shut it off.
Basically, is there anyway to write something that will store the transactions happening on that column? or is there anyway to store the username column that was modified? so if i were user1 and i went in to the db and modified my account access level there would be a log or record of that transaction somewhere else that would store that users name.
I will plan to write any neccessary app in C#, and I would prefer to have the app run on my pc, not the server.
i've thought about just running an app that queries the db every so often (3-5 mins) to see the number of users in each access level, then returns the usernames for each admin access level(the level they are maliciously accessing) but that seems like a awful lot of work on the db, specially since its allready running slow due to the number of hits and request the server allready deals with. (which i can't really change since the server is run and operated by a different team, I just run the software package, there is a server group and a db group)
Is there a better option???
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
What is the underlying database engine? If it is sql server you could add an update trigger on the users table and audit trail the changes to another (new) table. Better yet, the trigger could also reject access level changes by other than specific users, and perhps temporarily disable their access completely...
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
yes it is sql server, and i will have to do some research on these triggers you speak of, any direct links, good books, ideas you can share?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
The most complete documentation would be in SQL Sever Books On Line, which can be downloaded here.[^]
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Cool, I'll check it out, thanks!
|
|
|
|
|
ok, so i've done some reading on triggers, and now i've got some questions!!!
do i want to do this with an instead of, or after statement?
or would it be just best for me to use this: CREATE trigger Nnew_updates on people for insert, update as
if i use this one, will i need to search thru the deleted table to find it, then rollback, or just use the instead of and compare the requested change with the trigger and not let it do it at all? (can i even do that with the instead of)
I found some DB2 info that uses BEFORE, too bad sqlSever does't use that, it'd be nice.
Course, the info that i found showing sql doesn't use it was from 2000, and it said that Microsoft was contemplating the use of BEFORE, do you konw if they have implemented the use of it in sp3 by chance?
My next question then is regardless of what method I go with what should my trigger statement look like? for instance I'm already looking at using what i pasted earlier and i've got the format down, but what should my begin statement look like?
I need to beable to compare the old access_level to the new access_level. so will i need to compare the deleted table with the people table? or is there an easier way to just not allow changes to the access_level column?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
I would use separate triggers for insert and update. The Deleted pseudoTable will not exist in the case of an insert, so the handling will likely need to be different. In both cases the new data can be accessed in the "Inserted" psuedoTable.
The for insert, for update triggers would seem nore sensible here than an after or instead of...
Could be as simple as "if Inserted.Access_level <> Deleted.Access_level then rollback transaction"
For inserts, I suspect you will want to check the user that is inserting the record (not the user being added)
- SELECT user_name() will return this, but this might be probematic if the application is always logged in as sysadmin - dbo - (it shouldn't be, but that is another matter).
Yuo should likely disallow changes to more than on record at a time (rollback if @@rowCount > 1) as this would seem inappropriate in any case, and allows succeeding checks to assume only one row (the current one) in the psuedo tables.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Well, my thinking is that I'm going to be better off by not restricting it to users as there IS one user who has admin access to the software but does not need the access to change others access(i was forced to give him access). so in other words i need to restrict the access to the access_level column entirely, and IF i need to update someones level(only if my boss tells me to) then i disable the trigger to complete what I would need to do. plus the domain levels are not set to change the db level so they can log in with sa level access.
and i think i would need to block any new entries as admin as there is nothing keeping someone from entering a new username and creating it as an admin from the db.(not sure if that was inline or out-of-line with what you were saying about the inserts.)
in your last statement, not sure i fully understand, are you saying i need to block more the possiblity of someone using a query to insert multiple accounts so as to trick the trigger into only stopping the first entry?
and i sould be able to run both the insert and update checks in the same trigger right? by just separating the functions of each rather than running them as a single statement?
Dim Beautiful As String
Beautiful = "ignorant"
Label1.Text = "The world is full of " & Beautiful & " people."
Why is common sense such an un-common comodity?
|
|
|
|
|
wetrivrrat wrote:
and i sould be able to run both the insert and update checks in the same trigger right? by just separating the functions of each rather than running them as a single statement?
You could, but the code might be much simpler if you did them as separate triggers, as they will fire independantly (the 'for update' won't fire the 'for insert' unless the update trigger itself inserts new records).
wetrivrrat wrote:
and i think i would need to block any new entries as admin as there is nothing keeping someone from entering a new username and creating it as an admin from the db.(
That makes the for insert trigger simple: just rollback transaction
wetrivrrat wrote:
in your last statement, not sure i fully understand, are you saying i need to block more the possiblity of someone using a query to insert multiple accounts so as to trick the trigger into only stopping the first entry?
I was more concered about an update that changed the access_level for any username 'like A%" forinstance, or all usernames ....
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
|
|
|
|
|
Which is better?
sqlDataReader[i]
sqlDataReader["columnName"]
sqlDataReader.GetValue(i)
where i is index of column
|
|
|
|
|
i prefer using the column name, for the simple reason that it is more readable than using a column index...
moreover, it would avoid some debug mistakes...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
sqlDataReader["columnName"]
is generally used....
coz giving number as index may create prob if the sequence of columns is changed
Annpurna Tiwari
IIPL Company
Nagpur
India
|
|
|
|
|
Hi, i just wanna download DB2/ORacle editor in my system having the OS windows 2000.How can i download this freely in my machine.Plzz help me.
Thanks
BhuvanaSathish
|
|
|
|
|
oracle databases are free unless you use the DL versions for sell and some reasons explained on the website....
did you have a look at their site ?
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
I have SQL Server 2000 installed on my laptop, as well as Visual Studio .NET 2003. I am going through a tutorial in which it asks me to go to the Northwind database and right-click on Views and select New View. When I right-click on Views, the only context menu items I get are Refresh and Properties. Does anyone know why I do not get the "New View..." menu item in Server Explorer within Visual Studio .NET? Thanks in advance.
Dan
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
