Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CorpDirect
Yak Posting Veteran
92 Posts |
 Posted - 2005-09-08 : 16:01:08
|
Does anyone know a way I can leave off the very last record returned in a select statement that uses ORDER BY?Something like:SELECT TOP 100 PERCENT MINUS ONE<columns>FROM some_tableORDER BY some_colum Obviously that's just made-up syntax but you get the idea.Thank you,Daniel |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-09-08 : 16:18:00
|
| I get the idea, but I don't get why?DECALRE @x int, @sql varchar(8000)SELECT @x = COUNT(*) FROM some_tableSELECT @sql = 'SELECT TOP ' + CONVERT(varchar(15),@x) + ' FROM some_table ORDER BY some_column'EXEC(@sql)Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2005-09-08 : 16:25:09
|
quote:
Originally posted by X002548
I get the idea, but I don't get why?
Well, it's really for this other issue:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54975[/url]The goal there was to insert blank lines between groups of records, but the answer turned out to be inserting blank lines after groups of records. So, to suppress the extra blank record at the end, I needed a way to select all but the last record.Daniel |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-09-08 : 16:35:28
|
| As mentioned in the other thread, you really, really should do this with PHP. (your presentation layer). By trying to keep things "simple" you are making things much, much more complicated and making things more inefficient. I don't know PHP, but I am quite sure that there is an easy way to add a blank row between groups. If you post your PHP code I am quite sure someone here can help you out. It is best to bite the bullet and learn how to deal with this stuff the correct way than to keep hacking T-SQL together for your presentation needs.YOu have already had to alter your SELECT to loop through the data twice as many times as needed to generate the blank rows; now, to avoid that last row, you have again doubled the amount of processing required -- so trying to force this kind of formatting into T-SQL has effectively made your code 4 times slower than it could be if you simply put in a quick snippet of code in PHP as you loop trhough the rows and build your webpage. |
|
|
|
CorpDirect
Yak Posting Veteran
92 Posts |
Posted - 2005-09-08 : 18:12:47
|
| As mentioned in the other thread, I'm not an experienced developer on T-SQL or PHP. With PHP all I have been able to do is execute the stored procedure that generates this result set, then dump the result into an HTML table. Any conditional treatment of data, particularly of this sort, is beyond available skills at the moment.So I have decided on a compromise: use the T-SQL SELECT statement as worked out in the other thread to return the sorted records with blank lines after each grouping, and just ignore the final blank line. This way I can eliminate at least that one extra doubling of processing required. As the HTML table does not have visible borders this will hopefully not cause problems with the report format, and if so that can be a "bug" to work out later.You may be right about the presentation layer being the right place to do things like this, which are in a sense formatting issues. For myself I'm a little bit more comfortable with SQL than with PHP so I find it easier to understand the help I get here. --and I haven't posted any PHP questions here since this is a SQL forum...Thank you very much for your recommendations; I do take them seriously and think you are right, I'm just doing the best I can with limited time and knowledge.Thanks and Regards,Daniel |
|
|
|
|
|
|
|
|
omit last record from ordered result set