|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Always selecting at least 10 rows?Hi,
I use an Identity column to create row numbers displayed in a DataGrid. The datagrid typically displays row 1-10, 11-20 etc. The datagrid is filled using SELECT * FROM Tabel WHERE RowID BETWEEN Start AND End. If rows 1-10 is removed from the table, there are no rows displayed since the select statement returns none for this range. What I am looking for is an elegant way of retrieving at least n rows with row numbers from x or above. Something along the line of SELECT 10 FROM Tabel WHERE RowID >= 1 Can this be done, or do I need to use cursors? SqlDataReader and loop? Morten Ho Morten,
If you already have an identity column use this to order againt it. SELECT TOP 10 * FROM Table Order by YouridentColumn You should also consider to sue the paging machanisam in the datagrid (if you are using .NET). HTH, Jens Suessmeyer. Ah, I thought that Top 10 would retrieve the top 10 RowIds, meaning the ten largest ids. Works like a charm :)
Oh, and the paging mechanism is my own since the datagrid paging is too limited. Morten On Fri, 11 Nov 2005 08:53:19 +0100, Jens <J***@sqlserver2005.de> wrote: Show quote > Ho Morten, > > If you already have an identity column use this to order againt it. > > SELECT TOP 10 * FROM Table Order by YouridentColumn > > You should also consider to sue the paging machanisam in the datagrid > (if you are using .NET). > > > HTH, Jens Suessmeyer. > > SELECT TOP 10 * FROM Tabel WHERE RowID >= 1
? Show quote "Morten Wennevik" <Morten.Wenne***@email.adr> wrote in message news:op.sz2fvlfdg1d8xu@tr023.bouvet.no... > > Hi, > > I use an Identity column to create row numbers displayed in a DataGrid. > The datagrid typically displays row 1-10, 11-20 etc. The datagrid is > filled using > > SELECT * FROM Tabel WHERE RowID BETWEEN Start AND End. > > If rows 1-10 is removed from the table, there are no rows displayed since > the select statement returns none for this range. > > What I am looking for is an elegant way of retrieving at least n rows with > row numbers from x or above. Something along the line of > > SELECT 10 FROM Tabel WHERE RowID >= 1 > > Can this be done, or do I need to use cursors? SqlDataReader and loop? > > Morten Exactly what I was looking for. I mistook the TOP keyword for largest instead of first.
Thanks, Morten On Fri, 11 Nov 2005 08:56:11 +0100, Roji. P. Thomas <thomasr***@gmail.com> wrote: Show quote > SELECT TOP 10 * FROM Tabel WHERE RowID >= 1 > > ? Morten Wennevik wrote:
> Exactly what I was looking for. I mistook the TOP keyword for TOP requires an ORDER BY clause to guarantee consistent results. > largest instead of first. > Thanks, > > Morten > > On Fri, 11 Nov 2005 08:56:11 +0100, Roji. P. Thomas > <thomasr***@gmail.com> wrote: >> SELECT TOP 10 * FROM Tabel WHERE RowID >= 1 >> >> ? Otherwise, you might get somewhat random data. Yes, forgot to add ORDER BY RowID, works fine now :)
On Fri, 11 Nov 2005 15:38:32 +0100, David Gugick <david.gugick-nospam@quest.com> wrote: Show quote > Morten Wennevik wrote: >> Exactly what I was looking for. I mistook the TOP keyword for >> largest instead of first. >> Thanks, >> >> Morten >> >> On Fri, 11 Nov 2005 08:56:11 +0100, Roji. P. Thomas >> <thomasr***@gmail.com> wrote: >>> SELECT TOP 10 * FROM Tabel WHERE RowID >= 1 >>> >>> ? > > TOP requires an ORDER BY clause to guarantee consistent results. > Otherwise, you might get somewhat random data. >  |
|||||||||||||||||||||||
Other interesting topics