 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Top
We have top function such as: Select top 100 * From SomeTable Why not there is a bottom function? We are doing a project and after the last record we do some processing at the bottom of the table and I find it I had to write extra code to retrieve those added at the bottom. I wish I could do: Select bottom 100 * From SomeTable Also SQL server string function charindex has limited functionality and clumsy to use. In substring wee give the starting position to splice the string. In chraindex we should be able to tell if we want to find the first occurrence (that is default) or second or third or fourth occurrence. I can still use charindex to find all those occurrences but it causes serious overhead to the sql. And finally, the string functions do not abort gracefully if wrong input number is provided (for example an invalid position into substring function). This is not good because all string functions should terminate gracefully instead of aborting/crashing out of a sql statement while processing records in a table. I usually set some flags/warnings off to handle these type of situations. Hi, Usually when you use TOP you also use ORDER BY like in SELECT TOP 100 * from table ORDER BY field Then you can use DESC to sort the records in descending order to get the 100 records at the bottom like in SELECT TOP 100 * from table ORDER BY field DESC Hope this helps, Ben Nevarez > We have top function such as: > Select top 100 * [quoted text clipped - 18 lines] > processing records in a table. I usually set some flags/warnings off > to handle these type of situations. On Feb 22, 1:54 pm, Ben Nevarez <bneva...@no.spam.please.sunamerica.com> wrote: > Hi, > [quoted text clipped - 35 lines] > > - Show quoted text - Thanks. However this table has no order by column. Size is 10 mil records. <<Thanks. However this table has no order by column. Size is 10 mil records.>> Are you saying that there is no column with which you can determine what you mean by "top" or "bottom". I.e., that you do SELECT TOP ... without ORDER BY? If so, then there would be no difference between TOP and a hypothetical BOTTOM, since TOP without ORDER BY can give you any rows. There is no implied ordering between rows in a table!  SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi On Feb 22, 1:54 pm, Ben Nevarez <bneva...@no.spam.please.sunamerica.com> wrote: > Hi, > [quoted text clipped - 36 lines] > > - Show quoted text - Thanks. However this table has no order by column. Size is 10 mil records. >On Feb 22, 1:54 pm, Ben Nevarez ><bneva...@no.spam.please.sunamerica.com> wrote: [quoted text clipped - 15 lines] >Thanks. However this table has no order by column. Size is 10 mil >records. Tables don't have ORDER BY, queries do. If your query >> SELECT TOP 100 * from table ORDER BY field does not have an ORDER BY clause then there is no certainty WHICH 100 rows are returned by the query. If you care which are the top 100, then you have to use ORDER BY, and once you use ORDER BY, Ben explained how to get the "bottom". "bottom". Roy Harvey Beacon Falls, CT >>On Feb 22, 1:54 pm, Ben Nevarez >><bneva...@no.spam.please.sunamerica.com> wrote: [quoted text clipped - 25 lines] > then you have to use ORDER BY, and once you use ORDER BY, Ben > explained how to get the "bottom". "bottom". I just had a perfect example of this two weeks ago at work. A report one of the VPs was getting was ordered based on status and date, but not orderID. So within the same day on different reports, the OrderID might vary. Very confusing when trying to compare between reports. Not sure if the developer didn't realize this was a requirement or assumed SQL Server would always return the same order for the OrderID, but it certainly didn't do it in practice. > Roy Harvey > Beacon Falls, CT SignatureGreg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html On Feb 24, 12:16 am, "Greg D. Moore \(Strider\)" <mooregr_deletet...@greenms.com> wrote: > >>On Feb 22, 1:54 pm, Ben Nevarez > >><bneva...@no.spam.please.sunamerica.com> wrote: [quoted text clipped - 47 lines] > > - Show quoted text - Top means top of table and bottom is bottom of table. A table is a set of rows and therefore asking for a order in a query is nonsense. If I say top 10 * I want top 10 rows and if I say bottom 10* then bottom 10 rows. Even if it is a set SQl server returns same top and bottom rows 100% of the time. Anyone who said is an idiot. Instead of responding to everyone I have made a response to everyone. Thanks. I'm not sure what points you are trying to make, but I'll try to comment: <<Top means top of table and bottom is bottom of table.>> Imagine a plastic bag with a number of paper slips in the bag. The bag is your table and the paper slips are your rows. This bag is shaken every once in a while (optimizer can decide on difference execution plans) . Now, pick the "first" 10 rows. > A table is a set of rows and therefore asking for a order in a query is nonsense.>> This is an interesting proposal. The result of a query can be ordered in the SQL language, using ORDER BY. However, a result from a query is a set, so from a theoretical standpoint one can argue that ORDER BY violates the relational model. >> If I say top 10 * I want top 10 rows and if I say bottom 10* then bottom 10 rows.>> Again, a table is not ordered. << Even if it is a set SQl server returns same top and bottom rows 100% of the time.>> In your case, perhaps. But without ORDER BY, the optimize is free to return the rows in any order is finds cheapest. Perhaps tomorrow you have more rows than you have now and you get some different execution plan? Or perhaps at some point the optimizer decided to work on the query using several processors. There are no guarantees of ordering if you don't have ORDER BY in your query. << Anyone who said is an idiot. Instead of responding to everyone I have made a response to everyone. Thanks.>> I don't follow the part about idiot above. I assume you went some frustration that the SQL language doesn't work the way you want it to, by calling the people who took their free time and tried to assist you idiots. You are of course welcome to your opinion, but insults will not help you in the future... SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi On Feb 24, 12:16 am, "Greg D. Moore \(Strider\)" <mooregr_deletet...@greenms.com> wrote: > "Roy Harvey (SQL Server MVP)" <roy_har...@snet.net> wrote in > messagenews:j8itr3dsr0s6vcspldie0dejgj0t0lv88j@4ax.com... [quoted text clipped - 53 lines] > > - Show quoted text - Top means top of table and bottom is bottom of table. A table is a set of rows and therefore asking for a order in a query is nonsense. If I say top 10 * I want top 10 rows and if I say bottom 10* then bottom 10 rows. Even if it is a set SQl server returns same top and bottom rows 100% of the time. Anyone who said is an idiot. Instead of responding to everyone I have made a response to everyone. Thanks. On Feb 25, 1:56 pm, "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > I'm not sure what points you are trying to make, but I'll try to comment: > [quoted text clipped - 107 lines] > > - Show quoted text - Do you guys really have to be work hard to be ignorant or does it come natural to you? I think the truth is somewhere in between. I have no interest explaining ABCD to you guys. Thanks. By the way I DISAGREE 100% with whatever you said and you can take your argument to whoever you want. I am certain you have zero (that is 0 or 0.0 take your pick) as a database developer. I am astounded! On Feb 25, 1:56 pm, "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > I'm not sure what points you are trying to make, but I'll try to comment: > [quoted text clipped - 107 lines] > > - Show quoted text - What do you mean set? I do a select * from table and I get the same rows in the same order every time and I am not smoking anything unlike you guys. Did you guys finish high school or cheated? > What do you mean set? I do a select * from table and I get the same > rows in the same order every time and I am not smoking anything unlike > you guys. Did you guys finish high school or cheated? YOU may get the same order every time but that's purely luck of the draw. For those of us with real-world experience, that ain't necessarily the case. I can't speak for others, but I did in fact finish high school and beyond and further more have actually know what a set is. I highly recommend you do some reading up on SQL fundamentals. Or at the very least go to your boss and admit your ignorance. SignatureGreg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html On Feb 25, 5:41 pm, "Greg D. Moore \(Strider\)" <mooregr_deletet...@greenms.com> wrote: > > What do you mean set? I do a select * from table and I get the same > > rows in the same order every time and I am not smoking anything unlike [quoted text clipped - 13 lines] > SQL Server DBA Consulting Remote and Onsite available! > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html All your certificates should be revoked and you should be sent back to school. You need serious re-education. You are dangerous to yourself and don't write any more sql code in case you injure someone. <<What do you mean set? I do a select * from table and I get the same rows in the same order every time and I am not smoking anything unlike you guys. Did you guys finish high school or cheated?>> I will not comment the insulting remarks, as I don't find them relevant nor productive to the discussion at hand. The reason you get the same rows every time is that the optimizer happened to pick the same execution plan for each execution that you do. Imagine that someone creates some index on the table or that for some other reason another plan is picked. That would alter what "top" or "bottom" means to you, and the query would no longer return what you consider "top" or "bottom". Furthermore, there is no way of specifying the "order of a table". Did you see some attribute for a column that say something like "order the table based on this column, ascending or descending"? There are attributes that reflects ordering at the physical level, but those do not guarantee anything at the logical (SQL) level (those at the physical level are only performance constructs). Anyhow, bottom-line is that there is no "bottom" construct in SQL or TSQL. This is probably not what you wanted to hear and I'm sorry that SQL Server do not behave the way you want it to. But your frustration will not change whatever language constructs (like "bottom") are available. I will not post further on this topic since I've exhausted my viewpoints about this and I don't feel like taking more insults. SignatureTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi On Feb 25, 1:56 pm, "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > I'm not sure what points you are trying to make, but I'll try to comment: > [quoted text clipped - 113 lines] > > - Show quoted text - What do you mean set? I do a select * from table and I get the same rows in the same order every time and I am not smoking anything unlike you guys. Did you guys finish high school or cheated? On Feb 25, 1:56 pm, "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > I'm not sure what points you are trying to make, but I'll try to comment: > [quoted text clipped - 107 lines] > > - Show quoted text - When I do: Select * from SomeTable I get the same rows, same order every time. You can talk about set but in classic set theory SQL server table is not set based because mathematically a set does not have any order. Also the top function does not follow standard because every time it should return rows in different order. Therefore it is wrong same as SQL server, the whole database. just do a top using order by in reverse or create a udf SignatureSincerely, John K Knowledgy Consulting http://knowledgy.org/ Atlanta's Business Intelligence and Data Warehouse Experts > We have top function such as: > Select top 100 * [quoted text clipped - 18 lines] > processing records in a table. I usually set some flags/warnings off > to handle these type of situations. On Feb 27, 9:31 am, "Knowledgy" <atlanta business intelligence consultants> wrote: > just do a top using order by in reverse or create a udf > [quoted text clipped - 29 lines] > > - Show quoted text - Trust me it's not fun to order a table of ~40 mil records. I want something that is fast and udf is slow. Unix has head/tail FWIW. Thanks. > Trust me it's not fun to order a table of ~40 mil records. I want > something that is fast and udf is slow. Unix has head/tail FWIW. > Thanks. Nobody says you have to order 40 million rows. However, you DO have to use the ORDER BY clause to specify what you mean by "TOP n" because a table has no implicit order and the result of TOP is not guaranteed without ORDER BY. If you index the columns used in the ORDER BY clause in the same order they are specified then the operation of TOP should be very efficient and the server will not have to order the entire table. Since you have chosen to ignore and abuse people who tried to give you good advice, maybe you will believe what Microsoft say about TOP: "If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary." http://msdn2.microsoft.com/en-us/library/ms189463.aspx -- David Portas On Feb 27, 6:15 pm, David Portas <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: > > Trust me it's not fun to order a table of ~40 mil records. I want > > something that is fast and udf is slow. Unix has head/tail FWIW. [quoted text clipped - 18 lines] > -- > David Portas You and Microsoft can shove order by. On Feb 27, 6:15 pm, David Portas <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: > "If the query includes an ORDER BY clause, the first expression rows, > or expression percent of rows, ordered by the ORDER BY clause are > returned. If the query has no ORDER BY clause, the order of the rows > is arbitrary."http://msdn2.microsoft.com/en-us/library/ms189463.aspx > David Portas Don't be a lemming of Microsoft and there is no reason to follow them blindly what they say. > Trust me it's not fun to order a table of ~40 mil records. I want > something that is fast and udf is slow. Unix has head/tail FWIW. > Thanks. So can Windows. Your point? One is a file access tool, one is a RDBMS. If you're confusing the two of those, then you really do need to submit your resignation letter. SignatureGreg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html On Feb 28, 10:31 am, "Greg D. Moore \(Strider\)" <mooregr_deletet...@greenms.com> wrote: > > Trust me it's not fun to order a table of ~40 mil records. I want > > something that is fast and udf is slow. Unix has head/tail FWIW. [quoted text clipped - 8 lines] > SQL Server DBA Consulting Remote and Onsite available! > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html Yes that was an option I am considering seriously unlike most of you lame sql coders. So you can shove it too. Came across this post, as I wanted to get the second record from a table (with a sequence number as primary id), but about all I came back with is some rantings from an SQL 'terrorist'. But I can use 'Order By' and figure it out. Using TOP (with no Order By) does seem to bring back the same records, but I wonder if that is because there is an index on the sequence number/primary key? I suspect that someone has not been using Order By. That's fine until another developer comes along and adds Order By to the queries and bang! - the code is broken. Better to be safe (unambiguous) and use it, even if you 'think' it is unnecessary. JMTCW KL > On Feb 28, 10:31 am, "Greg D. Moore \(Strider\)" > [quoted text clipped - 20 lines] > > - Show quoted text - > Came across this post, as I wanted to get the second record from a > table (with a sequence number as primary id), > but about all I came back with is some rantings from an SQL > 'terrorist'. > > But I can use 'Order By' and figure it out. Correct. > Using TOP (with no Order By) does seem to bring back the same records, > but I wonder if that is because there is an index on the sequence > number/primary key? Luck of the draw. SQL will tend to read from the disk in that order, but again, there's no requirement that it will do so and it may return the results in any order it wishes. I've had the same query run twice in a row return in different order. Seems to be more common on my machine with more CPUs. > I suspect that someone has not been using Order By. That's fine until > another developer comes along and adds Order By to the queries and > bang! - the code is broken. Better to be safe (unambiguous) and use > it, even if you 'think' it is unnecessary. Exactly. (so now not sure if you were asking a question or confirming :-) > JMTCW > [quoted text clipped - 27 lines] >> >> - Show quoted text - SignatureGreg Moore SQL Server DBA Consulting Remote and Onsite available! Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.