|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why isn't this working?Given a set of rows with RowID from 1-499 I want to return the last ten rows in ascending order, 490, 491, ... , 499 SELECT * FROM ( SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC ) SubQuery Order by RowID ASC The above query does not work, it returns the ten rows as 499, 498, ... , 490. If I change the outer select * to anything else, then it works. SELECT FirstName FROM ( SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC ) SubQuery Order by RowID ASC However, I need all columns. Do I have to specify all columns? Morten Morten wrote on Fri, 11 Nov 2005 13:29:27 +0100:
Show quote > Hi, I just tested a similar query on one of my own databases, and it works > > Given a set of rows with RowID from 1-499 I want to return the last ten > rows in ascending order, 490, 491, ... , 499 > > SELECT * FROM > ( > SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC > ) SubQuery > Order by RowID ASC > > The above query does not work, it returns the ten rows as 499, 498, ... , > 490. If I change the outer select * to anything else, then it works. > > SELECT FirstName FROM > ( > SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC > ) SubQuery > Order by RowID ASC > > However, I need all columns. Do I have to specify all columns? > > Morten fine - it get the last 10 entries by ID sorted in ascending order. select * from (select top 10 * from ClosedOrderHeader order by OrderID desc) subquery order by OrderID asc That's the exact query I used. It might help if you post the output of select @@version as it might be something that is addressed in a SP. My test was on a SQL 7 SP3 server. Dan Could be fixed in an SP as I seem to be using SP2 of SQL 7
On Fri, 11 Nov 2005 14:41:01 +0100, Daniel Crichton <msn***@worldofspack.co.uk> wrote: Show quote > Morten wrote on Fri, 11 Nov 2005 13:29:27 +0100: > >> Hi, >> >> Given a set of rows with RowID from 1-499 I want to return the last ten >> rows in ascending order, 490, 491, ... , 499 >> >> SELECT * FROM >> ( >> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC >> ) SubQuery >> Order by RowID ASC >> >> The above query does not work, it returns the ten rows as 499, 498, ... , >> 490. If I change the outer select * to anything else, then it works. >> >> SELECT FirstName FROM >> ( >> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC >> ) SubQuery >> Order by RowID ASC >> >> However, I need all columns. Do I have to specify all columns? >> >> Morten > > I just tested a similar query on one of my own databases, and it works > fine - it get the last 10 entries by ID sorted in ascending order. > > select * from (select top 10 * from ClosedOrderHeader order by OrderID desc) > subquery order by OrderID asc > > That's the exact query I used. > > It might help if you post the output of > > select @@version > > as it might be something that is addressed in a SP. My test was on a SQL 7 > SP3 server. > > Dan > > > > However, I need all columns. Do I have to specify all columns? You really should be striving to avoid SELECT * in production code.Really?
I wasn't aware of that, why should I avoid SELECT * ? On Fri, 11 Nov 2005 14:49:41 +0100, Aaron Bertrand [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote: Show quote >> However, I need all columns. Do I have to specify all columns? > > You really should be striving to avoid SELECT * in production code. > > > On Mon, 14 Nov 2005 08:55:07 +0100, Morten Wennevik wrote:
>Really? Hi Morten,> >I wasn't aware of that, why should I avoid SELECT * ? > > >On Fri, 11 Nov 2005 14:49:41 +0100, Aaron Bertrand [SQL Server MVP] <ten.xoc@dnartreb.noraa> wrote: > >>> However, I need all columns. Do I have to specify all columns? >> >> You really should be striving to avoid SELECT * in production code. Various reasons. Returning more data than you need stresses the network. It also limits the possiblities for the query optimizer to create an efficient plan. You should therefore always return only the columns you actually need. With SELECT *, changes in the table design will affect the results. There might suddenly be an extra column, or a column less. Or, if the table has been dropped and recreated, columns might have changed places in the results. Any code that tries to proces the results from the query might break becuase of such changes. Even humans that have to peruse the data might be confused. Finally, SELECT * makes impact analysis a lot harder. Global searches to find out if a column is used will miss such queries. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) On Fri, 11 Nov 2005 13:29:27 +0100, "Morten Wennevik"
<Morten.Wenne***@email.adr> wrote: >Given a set of rows with RowID from 1-499 I want to return the last ten rows in ascending order, 490, 491, ... , 499 use northwind> >SELECT * FROM >( > SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC >) SubQuery >Order by RowID ASC > >The above query does not work, it returns the ten rows as 499, 498, ... , 490. > If I change the outer select * to anything else, then it works. go SELECT * FROM ( SELECT TOP 10 * FROM Products WHERE ProductID >= 1 ORDER BY ProductID DESC ) SubQuery Order by ProductID ASC go Seems to work fine. J. As Daniel suggested I think it may be fixed in SP3 as I am using SQL 7 SP2
On Fri, 11 Nov 2005 21:01:23 +0100, jxstern <jxst***@nowhere.xyz> wrote: Show quote > On Fri, 11 Nov 2005 13:29:27 +0100, "Morten Wennevik" > <Morten.Wenne***@email.adr> wrote: >> Given a set of rows with RowID from 1-499 I want to return the last ten rows in ascending order, 490, 491, ... , 499 >> >> SELECT * FROM >> ( >> SELECT TOP 10 * FROM Import WHERE RowID >= 1 ORDER BY RowID DESC >> ) SubQuery >> Order by RowID ASC >> >> The above query does not work, it returns the ten rows as 499, 498, ... , 490. >> If I change the outer select * to anything else, then it works. > > use northwind > go > > SELECT * FROM > ( > SELECT TOP 10 * FROM Products WHERE ProductID >= 1 ORDER BY > ProductID DESC > ) SubQuery > Order by ProductID ASC > go > > Seems to work fine. > > J. > > >  |
|||||||||||||||||||||||
Other interesting topics