How are these tasks done in SQL?

Question

1. I have a table, and there is no column which stores a field of when the record/row was added. How can I get the latest entry into this table? There would be two cases in this:

   • Loop through entire table and get the largest ID, if a numeric ID is being used as the identifier. But this would be very inefficient for a large table.
   • If a random string is being used as the identifier (which is probably very, very bad practise), then this would require more thinking (I personally have no idea other than my first point above).

2. If I have one field in each row of my table which is numeric, and I want to add it up to get a total (so row 1 has a field which is 3, row 2 has a field which is 7, I want to add all these up and return the total), how would this be done?

Thanks

Answer 1

for the last thing use a SUM()

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

assuming they are all in the same column.

Answer 2

1) If the id is incremental, "select max(id) as latest from mytable". If a random string was used, there should still be an incremental numeric primary key in addition. Add it. There is no reason not to have one, and databases are optimized to use such a primary key for relations.

2) "select sum(mynumfield) as total from mytable"

Answer 3

Your first question is a bit unclear, but if you want to know when a row was inserted (or updated), then the only way is to record the time when the insert/update occurs. Typically, you use a DEFAULT constraint for inserts and a trigger for updates.

If you want to know the maximum value (which may not necessarily be the last inserted row) then use MAX, as others have said:

SELECT MAX(SomeColumn) FROM dbo.SomeTable

If the column is indexed, MSSQL does not need to read the whole table to answer this query.

For the second question, just do this:

SELECT SUM(SomeColumn) FROM dbo.SomeTable

You might want to look into some SQL books and tutorials to pick up the basic syntax.