Monday, February 13, 2012

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 us
ing
SELECT * FROM Tabel WHERE RowID BETWEEN Start AND End.
If rows 1-10 is removed from the table, there are no rows displayed since th
e select statement returns none for this range.
What I am looking for is an elegant way of retrieving at least n rows with r
ow 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?
MortenHo 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
?
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Morten Wennevik" <Morten.Wennevik@.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|||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 limi
ted.
Morten
On Fri, 11 Nov 2005 08:53:19 +0100, Jens <Jens@.sqlserver2005.de> wrote:

> 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.
>|||Exactly what I was looking for. I mistook the TOP keyword for largest inste
ad of first.
Thanks,
Morten
On Fri, 11 Nov 2005 08:56:11 +0100, Roji. P. Thomas <thomasroji@.gmail.com> w
rote:

> SELECT TOP 10 * FROM Tabel WHERE RowID >= 1
> ?|||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
> <thomasroji@.gmail.com> wrote:
TOP requires an ORDER BY clause to guarantee consistent results.
Otherwise, you might get somewhat random data.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||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:

> Morten Wennevik wrote:
> TOP requires an ORDER BY clause to guarantee consistent results.
> Otherwise, you might get somewhat random data.
>

No comments:

Post a Comment