Re: [sqlite] LAST() function not supported
On Sat, Jan 29, 2011 at 01:06:07PM -0800, Marian Cascaval wrote: > On Sat, January 29, 2011, Nicolas Williams wrote: > > If there's enough indices to satisfy all the ORDER BY expressions then > > how could a "last()" function do any better? For that matter, if there > > aren't enough indices to satisfy all the ORDER BY expressions then how > > could a "last()" function do any better? What optimization could such a > > function implement that the query optimizer couldn't? Syntactically > > speaking, there's no additional information in "last()" -- it's just > > syntactic sugar. > > As I see it,from the point of view of just retrieving the last row from a > table, > no ORDER BY is necessary thus saving processor time. You seem to think that SQLite3 has to get all the rows and sort them, then pick the last. But remember, if there's enough indices then it's not picking the last, but the first row, and there's no sorting to do. Seriously, have SQLite3 explain the query plan to you and you'll see. If there's no suitable indices, then SQLite3 will have to do a full table scan, select specific rows, then sort them to pick the one you want -- a "last()" function wouldn't change that. If there is a suitable index then SQLite3 will not do any scanning, nor sorting, because of that LIMIT 1. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
On Sat, Jan 29, 2011 at 01:03:05PM -0800, Marian Cascaval wrote: > Here's the info source on LAST() function: > > http://www.w3schools.com/sql/sql_func_last.asp > > > > I needed to retrieve the last row from a table. If you need the "last row from a table" that's trivial to do efficiently in SQLite3: SELECT ... FROM ... ORDER BY rowid DESC LIMIT 1; (You should, but don't have to, replace rowid with whatever the INTEGER PRIMARY KEY column is, if there is one.) Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
Thanks for the professional explanation. I think I begin to see the importance of specificity of database query. Nevertheless, I found another info source on LAST() function: http://www.codesnout.com/SQLSample/SQL-LAST.php They also say "The LAST() function is not supported by certain databases.". It seems to me this function is a kind of wrapper which is valid for tables with a primary key. But I assume a pro would not use it anyway since LAST() it's not a general SQL function. As far as I could figure, this function is not a defined function in the SQL92 standard. Marian Cascaval From: Puneet Kishor <punk.k...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Sat, January 29, 2011 11:12:52 PM Subject: Re: [sqlite] LAST() function not supported On Saturday, January 29, 2011 at 3:03 PM, Marian Cascaval wrote: > Here's the info source on LAST() function: > > http://www.w3schools.com/sql/sql_func_last.asp > > > > I needed to retrieve the last row from a table. > And the example (the workaround tip) in the above link solved my issue. > > > > While the "workaround" solved your problem, the workaround *is* the right way to solve the problem. Their original solution is absolutely goofy. From their docs, "The LAST() function returns the last value of the selected column." That sentence makes absolutely no sense. What does "last value of the selected column" mean? We happen to now know that they mean the last row in the result set. But, why not just say that? Their proposed solution is absolutely out of whack -- SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders Note that a SQL result set doesn't have a concept of first or last unless you impose an order on it using the ORDER BY clause. Interestingly, they don't say which databases support this LAST() function. I have not come across, yet, any database that supports a LAST() function the way they say it should. In other words, rest assured, LIMIT 1 is indeed the correct way to restrict the result set to one row. However, if you want the "last" row, you have to tell your database program what you mean by last. You do so by specifying ORDER BY . Then, you can take the last or the first by using LIMIT properly. For example, if the orders were to be ordered by, say, the OrderDate, and you wanted the earliest date, you could do SELECT OrderPrice FROM Orders ORBER BY OrderDate LIMIT 1; If you wanted the latest order, you could do SELECT OrderPrice FROM Orders ORDER BY DESC OrderDate LIMIT 1; Hope this helps. > > > From: Puneet Kishor <punk.k...@gmail.com> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Sat, January 29, 2011 10:47:44 PM > Subject: Re: [sqlite] LAST() function not supported > > > > > On Saturday, January 29, 2011 at 2:38 PM, Marian Cascaval wrote: > > > > Hi! > > > > Is LAST() function going to be supported? > > > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround > > always > > > be > > > > enough? > > > > > > > Where did you get information on this "LAST()" function? > > > > > My concern is if there might be any speed improvement if LAST() function > > were > > > to > > > > be implemented, comparing to the workaround. > > > > > You are possibly confusing how a function works vs. the SQL syntax. A > function > acts on a column or an expression for every row in the result set. It doesn't > modify the number of rows in a result set. On the other hand, the LIMIT > clause > does nothing to the entries that have been retrieved. Instead, it throttles > the > > size of the result set, that is, it controls the number of rows in the result > set. > > > > > -- > Puneet Kishor > Sent with Sparrow > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
On Saturday, January 29, 2011 at 3:06 PM, Marian Cascaval wrote: > As I see it,from the point of view of just retrieving the last row from a > table, > no ORDER BY is necessary thus saving processor time. No, no, no, no. The db doesn't know what you mean by "last". If it happens to give you back what you wanted, good. But, don't get into the habit of depending on that. Get into a good habit -- tell the database exactly what you want. Specify the ORDER BY clause, and be assured of what you want. And, with regards to saving processor time, don't even waste any brain cycles on that. Unless you have a database table with upward of several hundreds of thousands of rows, your processor is not going to sweat or complain. Make sure you index your table on the columns you want to use for retrieval, and let the db do its job. Make sure to always tell the db what you want. Don't be lulled into thinking you are always gonna get the right results just because you happen to get the right results in a few cases. -- Puneet Kishor Sent with Sparrow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
On Saturday, January 29, 2011 at 3:03 PM, Marian Cascaval wrote: > Here's the info source on LAST() function: > > http://www.w3schools.com/sql/sql_func_last.asp > > > > I needed to retrieve the last row from a table. > And the example (the workaround tip) in the above link solved my issue. > > > > While the "workaround" solved your problem, the workaround *is* the right way to solve the problem. Their original solution is absolutely goofy. From their docs, "The LAST() function returns the last value of the selected column." That sentence makes absolutely no sense. What does "last value of the selected column" mean? We happen to now know that they mean the last row in the result set. But, why not just say that? Their proposed solution is absolutely out of whack -- SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders Note that a SQL result set doesn't have a concept of first or last unless you impose an order on it using the ORDER BY clause. Interestingly, they don't say which databases support this LAST() function. I have not come across, yet, any database that supports a LAST() function the way they say it should. In other words, rest assured, LIMIT 1 is indeed the correct way to restrict the result set to one row. However, if you want the "last" row, you have to tell your database program what you mean by last. You do so by specifying ORDER BY . Then, you can take the last or the first by using LIMIT properly. For example, if the orders were to be ordered by, say, the OrderDate, and you wanted the earliest date, you could do SELECT OrderPrice FROM Orders ORBER BY OrderDate LIMIT 1; If you wanted the latest order, you could do SELECT OrderPrice FROM Orders ORDER BY DESC OrderDate LIMIT 1; Hope this helps. > > > From: Puneet Kishor <punk.k...@gmail.com> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Sat, January 29, 2011 10:47:44 PM > Subject: Re: [sqlite] LAST() function not supported > > > > > On Saturday, January 29, 2011 at 2:38 PM, Marian Cascaval wrote: > > > > Hi! > > > > Is LAST() function going to be supported? > > > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround > > always > > be > > > > enough? > > > > > > > Where did you get information on this "LAST()" function? > > > > > My concern is if there might be any speed improvement if LAST() function > > were > > to > > > > be implemented, comparing to the workaround. > > > > > You are possibly confusing how a function works vs. the SQL syntax. A > function > acts on a column or an expression for every row in the result set. It doesn't > modify the number of rows in a result set. On the other hand, the LIMIT > clause > does nothing to the entries that have been retrieved. Instead, it throttles > the > size of the result set, that is, it controls the number of rows in the result > set. > > > > > -- > Puneet Kishor > Sent with Sparrow > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
As I see it,from the point of view of just retrieving the last row from a table, no ORDER BY is necessary thus saving processor time. From: Nicolas Williams <nicolas.willi...@oracle.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Sat, January 29, 2011 10:51:30 PM Subject: Re: [sqlite] LAST() function not supported On Sat, Jan 29, 2011 at 12:38:37PM -0800, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always >be > > enough? > > My concern is if there might be any speed improvement if LAST() function were >to > > be implemented, comparing to the workaround. Use EXPLAIN QUERY PLAN... If there's enough indices to satisfy all the ORDER BY expressions then how could a "last()" function do any better? For that matter, if there aren't enough indices to satisfy all the ORDER BY expressions then how could a "last()" function do any better? What optimization could such a function implement that the query optimizer couldn't? Syntactically speaking, there's no additional information in "last()" -- it's just syntactic sugar. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
Here's the info source on LAST() function: http://www.w3schools.com/sql/sql_func_last.asp I needed to retrieve the last row from a table. And the example (the workaround tip) in the above link solved my issue. From: Puneet Kishor <punk.k...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Sat, January 29, 2011 10:47:44 PM Subject: Re: [sqlite] LAST() function not supported On Saturday, January 29, 2011 at 2:38 PM, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always >be > > enough? > > > > Where did you get information on this "LAST()" function? > My concern is if there might be any speed improvement if LAST() function were >to > > be implemented, comparing to the workaround. > > > > > You are possibly confusing how a function works vs. the SQL syntax. A function acts on a column or an expression for every row in the result set. It doesn't modify the number of rows in a result set. On the other hand, the LIMIT clause does nothing to the entries that have been retrieved. Instead, it throttles the size of the result set, that is, it controls the number of rows in the result set. -- Puneet Kishor Sent with Sparrow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
On Sat, Jan 29, 2011 at 12:38:37PM -0800, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always > be > enough? > > My concern is if there might be any speed improvement if LAST() function were > to > be implemented, comparing to the workaround. Use EXPLAIN QUERY PLAN... If there's enough indices to satisfy all the ORDER BY expressions then how could a "last()" function do any better? For that matter, if there aren't enough indices to satisfy all the ORDER BY expressions then how could a "last()" function do any better? What optimization could such a function implement that the query optimizer couldn't? Syntactically speaking, there's no additional information in "last()" -- it's just syntactic sugar. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LAST() function not supported
On Saturday, January 29, 2011 at 2:38 PM, Marian Cascaval wrote: > Hi! > > Is LAST() function going to be supported? > > Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always > be > enough? > > > > Where did you get information on this "LAST()" function? > My concern is if there might be any speed improvement if LAST() function were > to > be implemented, comparing to the workaround. > > > > > You are possibly confusing how a function works vs. the SQL syntax. A function acts on a column or an expression for every row in the result set. It doesn't modify the number of rows in a result set. On the other hand, the LIMIT clause does nothing to the entries that have been retrieved. Instead, it throttles the size of the result set, that is, it controls the number of rows in the result set. -- Puneet Kishor Sent with Sparrow ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LAST() function not supported
Hi! Is LAST() function going to be supported? Or will the "SELECT ... FROM ... ORDER BY ... DESC LIMIT 1" workaround always be enough? My concern is if there might be any speed improvement if LAST() function were to be implemented, comparing to the workaround. Marian Cascaval ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
