Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How to retrieve all records from 30th record to 50th record of a table?

34 views
Skip to first unread message

AliRezaGoogle

unread,
Oct 3, 2007, 7:49:00 AM10/3/07
to
I have a table named myTable. It is sorted by some column and there is
no primery key or unique column.I want to retrieve all records from
30th record to 50th record. How can I do this by a sql statement?

David Portas

unread,
Oct 3, 2007, 8:01:36 AM10/3/07
to

You are mistaken. Tables are never "sorted by some column" because
tables are unordered sets. If you didn't know that already then I
recommend you do some reading or take a course before you go further.

What you presumably mean is that each time you query the table the
rows are usually returned in the same order. However, the actual
ordering you see is undefined and potentially unpredictable unless you
specify ORDER BY. Accordingly, unless you have a key there is no way
to identify the 30th, 50th or Nth row and such terms are meaningless
unless you tell us the ordering that defines them.

If you DO know which columns you need to order by then you can use the
RANK or ROW_NUMBER functions (in SQL Server 2005 only) to filter the
results:

SELECT col1, col2, col3
FROM
(SLECT col1, col2, col3,
DENSE_RANK() OVER (ORDER BY col1, col2, col3) AS rnk
FROM tbl) AS t
WHERE rnk BETWEEN 30 AND 50;

(untested)

Hopefully it goes without saying that every table should have a key.
In this case I have assumed (col1, col2, col3) is unique.

Hope this helps.

--
David Portas

Jim Underwood

unread,
Oct 3, 2007, 9:07:41 AM10/3/07
to
Without SQL 2005, you can do this:

select top 21 Col1, Col2, ColOrder
from
(
select top 50 Col1, Col2, ColOrder
from MyTable
order by ColOrder Asc
) as Top50
order by ColOrder Desc

First, as David points out, you have to have some column to order by. The
rows in a table have no natural order. As long as you have such a column,
the above SQL will select the last 21 records from the first 50 records,
which should give you rows 30 through 50 when ordered by a particular
column. Note that the order will be reversed in your results so 50 will be
first and 30 will be last. To order them 30 through 50 you need one more
level in the query.

select Col1, Col2, ColOrder
from
(
select top 21 Col1, Col2, ColOrder
from
(
select top 50 Col1, Col2, ColOrder
from MyTable
order by ColOrder Asc
) as Top50
order by ColOrder Desc
)
order by ColOrder Asc

I believe that Aaron Bertrand had an in depth article on his site, although
I can't recall the exact page. Here is the main page of his site:
http://www.aspfaq.com

"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message
news:1191412896.4...@g4g2000hsf.googlegroups.com...

--CELKO--

unread,
Oct 3, 2007, 10:34:29 AM10/3/07
to
>> I have a table named myTable. It is sorted by some column and there is no primary key or unique column. <<

BY DEFINITION:
1) Tables have at least one key
2) Tables have no ordering

>> I want to retrieve all records from 30th record to 50th record. <<

1) Rows are not anything like records
2) Tables have no ordering

Please read a book --ANY book -- are RDBMS. You have gotten every
basic concept wrong.

Jim Underwood

unread,
Oct 3, 2007, 2:42:56 PM10/3/07
to
I just stumbled across the article....
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

"Jim Underwood" <james.under...@fallonclinic.org> wrote in message
news:e1$ei5bBI...@TK2MSFTNGP04.phx.gbl...

jamie....@gmail.com

unread,
Oct 4, 2007, 10:05:05 AM10/4/07
to

You have some of the core concepts *very* wrong, but I'll make an
assumption that what you want is

SELECT * FROM [Table]

WHERE [Key] > 0 AND [Key] < 30

Etc. Because your fundamentals are wrong, that query is likely wrong
too ;)

Alex Kuznetsov

unread,
Oct 4, 2007, 10:33:36 AM10/4/07
to
On Oct 3, 9:34 am, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> I have a table named myTable. It is sorted by some column and there is no primary key or unique column. <<
> 1) Rows are not anything like records

Tha is no longer true. The language is changing. First indices became
indexes, then any lingering differences between rows and records
disappeared completely.

--CELKO--

unread,
Oct 4, 2007, 11:34:02 AM10/4/07
to
>> First indices became indexes, .. <<

Check your OED or Dictionary.com; both plural forms go back to Middle
English. One of the problems of English is multiple spellings of the
same word -- and it is not just US versus UK versions of the language.

>> then any lingering differences between rows and records disappeared completely. <<

NO! The differences were huge to start with (constraints vs. no
constraints; inherent meaning vs. interpretation by an application
program; strong data types vs. weak typing; no implied storage model
vs. contiguous physical storage; etc.

And the need to keep the right mindset is becoming more important. Go
read some of Stonebreaker's recent blogs about column versus row
oriented databases. There are no records in them; they assemble rows
from compressed columns of data elements.


Alex Kuznetsov

unread,
Oct 4, 2007, 12:18:03 PM10/4/07
to
On Oct 4, 10:34 am, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> First indices became indexes, .. <<
>
> Check your OED or Dictionary.com; both plural forms go back to Middle
> English. One of the problems of English is multiple spellings of the
> same word -- and it is not just US versus UK versions of the language.
>

Well if I type indices my spellchecker suggests indexes - idices
became obsolete.

> >> then any lingering differences between rows and records disappeared completely. <<
>
> NO! The differences were huge to start with (constraints vs. no
> constraints; inherent meaning vs. interpretation by an application
> program; strong data types vs. weak typing; no implied storage model
> vs. contiguous physical storage; etc.
>
> And the need to keep the right mindset is becoming more important. Go
> read some of Stonebreaker's recent blogs about column versus row
> oriented databases. There are no records in them; they assemble rows
> from compressed columns of data elements.

A well designed client application is completely isolated from these
details. In the real world everybody uses records and rows
interchangeably - why should anyone care what is under the hood as
long as it works.


--CELKO--

unread,
Oct 4, 2007, 3:31:02 PM10/4/07
to
>> A well designed client application is completely isolated from these details. <<

I agree. That is the basic idea of a tiered architecture. When I
pass data from my RDBMS to another procedural language tier, I want
that tier to think that it is reading "a reallllly fast sequential
file on a magnetic tape"

>> In the real world everybody uses records and rows interchangeably - why should anyone care what is under the hood as long as it works. <<

First of all, not everybody is that sloppy.

But more importantly, we do not work in the "real world" -- we work in
the RDBMS world. The application programmer in the other tier might
not care, but we should.

As an analogy, my wife worked on a cancer ward for 13+ years. The end-
user (cancer victim) was happy to say "lung cancer", but the
oncologist had more precise terms and did not lump all lung cancers
into one category or treatment.

When you are not that strict and careful with your thought process,
you get in trouble. Why would you favor sloppy thinking? Once you
get in the habit of mathematical correctness, ANSI/ISO Standards, etc.
it is not that hard to do things properly.


Alex Kuznetsov

unread,
Oct 4, 2007, 5:52:39 PM10/4/07
to
On Oct 4, 2:31 pm, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> In the real world everybody uses records and rows interchangeably - why should anyone care what is under the hood as long as it works. <<
>
> First of all, not everybody is that sloppy.
>
> But more importantly, we do not work in the "real world" -- we work in
> the RDBMS world.

No. Me and many other here actually work in the real world and are
only part time RDBMS users, as long as RDBMS helps us solve business
problems, and not a second longer.

>
> As an analogy, my wife worked on a cancer ward for 13+ years. The end-
> user (cancer victim) was happy to say "lung cancer", but the
> oncologist had more precise terms and did not lump all lung cancers
> into one category or treatment.
>

CAn you give a scenario when a database programmer could treat rows
differently from records?

> When you are not that strict and careful with your thought process,
> you get in trouble. Why would you favor sloppy thinking? Once you
> get in the habit of mathematical correctness, ANSI/ISO Standards, etc.
> it is not that hard to do things properly.

False analogy. If I do not follow mathematical correctness, my
formulas won't work. If I do not follow ANSI/ISO Standards, nothing
bad will happen whatsoever.

David Portas

unread,
Oct 4, 2007, 6:34:45 PM10/4/07
to
On 4 Oct, 20:31, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> A well designed client application is completely isolated from these details. <<
>
> I agree. That is the basic idea of a tiered architecture. When I
> pass data from my RDBMS to another procedural language tier, I want
> that tier to think that it is reading "a reallllly fast sequential
> file on a magnetic tape"
>
> >> In the real world everybody uses records and rows interchangeably - why should anyone care what is under the hood as long as it works. <<
>
> First of all, not everybody is that sloppy.
>
> But more importantly, we do not work in the "real world" -- we work in
> the RDBMS world. The application programmer in the other tier might
> not care, but we should.
>

In the RDBMS world we use the term Tuple, which is a different thing
altogether from a Row in SQL.

A tuple is an unordered and possibly empty set of attribute name/value
pairs.

By contrast a SQL row is a non-empty collection of values and/or
nulls, which may or may not have names, whose names may or may not be
unique and whose values may be individually identified either by
position or by name, depending on the context. That is quite a
difference so please let's not confuse rows with anything truly
relational! :)

--
David Portas


--CELKO--

unread,
Oct 4, 2007, 6:54:44 PM10/4/07
to
>> Me and many other here actually work in the real world and are only part time RDBMS users, as long as RDBMS helps us solve business problems, and not a second longer. <<

YOU might be a part time RDBMS users (cancer surgery users), but I an
the cancer surgeon. My job is to preserve the integrity of the data,
give my users what they requested, etc.

>> Can you give a scenario when a database programmer could treat rows
differently from records? <<

1) Every constraint put on a ROW is not possible on a record. When
the programmer writes code that assumes the that constraint
2) Any code that looks for a NULL (which do exist in files!! DUH!)
3) Any code that assumes DRI among 2 or more tables

>> If I do not follow mathematical correctness, my formulas won't work. <<

agreed.

>>If I do not follow ANSI/ISO Standards, nothing bad will happen whatsoever. <<

So if type in random syntax it will run? Replace SELECT with RETRIEVE
and run your code. Hey, it worked in QUEL!! MS and all the other
vendors on the ANSI/ISO committees were a waste of time and have never
tried to conform to the Standard?

Steve Dassin

unread,
Oct 4, 2007, 7:57:10 PM10/4/07
to
There you go again :-) Have you ever shown an example so people
would have some idea of what in the world your talking about?
Your concepts live on a page. And I've tried to show what it looks
like when the page come alive:P

www.beyondsql.blogspot.com

"David Portas" <REMOVE_BEFORE_R...@acm.org> wrote in message

news:1191537285.6...@d55g2000hsg.googlegroups.com...

Alex Kuznetsov

unread,
Oct 4, 2007, 11:11:07 PM10/4/07
to
On Oct 4, 5:54 pm, --CELKO-- <jcelko...@earthlink.net> wrote:
> >> Me and many other here actually work in the real world and are only part time RDBMS users, as long as RDBMS helps us solve business problems, and not a second longer. <<
>
> YOU might be a part time RDBMS users (cancer surgery users), but I an
> the cancer surgeon. My job is to preserve the integrity of the data,
> give my users what they requested, etc.

Following your analogy, yes you are one of many cancer surgeons, but
you are not _THE_surgeon unless you are hired in this particular
situation.

>
> >> Can you give a scenario when a database programmer could treat rows
>
> differently from records? <<
>
> 1) Every constraint put on a ROW is not possible on a record. When
> the programmer writes code that assumes the that constraint
> 2) Any code that looks for a NULL (which do exist in files!! DUH!)
> 3) Any code that assumes DRI among 2 or more tables

At the time of this writing many native English speaking database
professionals use rows or records interchangeable without any damage
to database integrity whatsoever. Constraints are put on records and
they work just as well. More to the point, Russian speaking database
professionals translate both rows and records into one and the same
word, and you know what? The sky does not fall on Russian databases,
and databases' integrity is not suffering at all.

Based on that, I'd apply Occum's razor once again - I think that
learning the difference between records and rows is redundant and
irrelevant to ability to implement high quality databases. Removing
redundancies from nomenclature is important - it allows to concentrate
on what's really essential.

> >>If I do not follow ANSI/ISO Standards, nothing bad will happen whatsoever. <<
>
> So if type in random syntax it will run? Replace SELECT with RETRIEVE
> and run your code. Hey, it worked in QUEL!! MS and all the other
> vendors on the ANSI/ISO committees were a waste of time and have never
> tried to conform to the Standard?

False analogy again. Instead of ANSI standard, I will adhere to T-SQL
and come up with a much bettter solution. For my business follwing
ANSI standard would be a huge waste of time and money.

Steve Dassin

unread,
Oct 5, 2007, 4:03:32 PM10/5/07
to
"Alex Kuznetsov" <alk...@gmail.com> wrote in message
news:1191553867.3...@g4g2000hsf.googlegroups.com...
> .

> I think that learning the difference between records and rows is redundant
> and
> irrelevant to ability to implement high quality databases. Removing
> redundancies from nomenclature is important - it allows to concentrate
> on what's really essential.
>.

This puts you on 'record'. And you will save time and money in the
effort to educate yourself on the difference. So you prove that
your a good businessman and know the advantage of being expedient.
But your also an expert. But lets be clear exactly about what
your an expert in. Your an expert in the box. I'm a novice out of
it.

best,
steve

www.beyondsql.blogspot.com


Alex Kuznetsov

unread,
Oct 5, 2007, 11:16:45 PM10/5/07
to
On Oct 5, 3:03 pm, "Steve Dassin" <st...@nospamrac4sql.net> wrote:
> "Alex Kuznetsov" <alk...@gmail.com> wrote in message
>
> news:1191553867.3...@g4g2000hsf.googlegroups.com...
>
> > .
> > I think that learning the difference between records and rows is redundant
> > and
> > irrelevant to ability to implement high quality databases. Removing
> > redundancies from nomenclature is important - it allows to concentrate
> > on what's really essential.
> >.
>
> This puts you on 'record'. And you will save time and money in the
> effort to educate yourself on the difference.

I know the "difference". This knowledge is IMO useless to me.

> But your also an expert. But lets be clear exactly about what
> your an expert in. Your an expert in the box.

That's a serious statement. I am not sure how you could substantiate
your blanket statement.

Steve Dassin

unread,
Oct 7, 2007, 5:09:53 PM10/7/07
to
> I know the "difference". This knowledge is IMO useless to me.

Of course it is! You have no where to go to use a 'row'. (Hence
the metaphor - all dressed up and no where to go:) This is why
I would like 'experts' to look at Dataphor. At least there you
will see how the concept lives. At least there you will be able
to compare and contrast with what you do now. It is less the
product than the ideas that it expresses. Surely an expert must
possess intellectual curosity. Therefore you would think I am not
asking you to do something out of your characeter.

>> But your also an expert. But lets be clear exactly about what
>> your an expert in. Your an expert in the box.

> That's a serious statement. I am not sure how you could substantiate
> your blanket statement.

And just how could you substantiate your statement that you know
the difference between a record and a row? But you needn't prove
anything to me, nor I to you. At least not yet:) I don't expect
the vendors that supply the shrinkwrapped boxes to substantiate
anything. But I would expect an expert to explore if they are
supplying the be all and end all of an idea. And that I would
expect you to explore. Do you really think that I am that unreasonable?
And please don't use your implicit obligation to those that
recognize your expertise as an excuse not to look outside their boxes.

best,
steve

"Alex Kuznetsov" <alk...@gmail.com> wrote in message

news:1191640605.1...@57g2000hsv.googlegroups.com...

Anith Sen

unread,
Oct 7, 2007, 9:13:57 PM10/7/07
to
>> Have you ever shown an example so people would have some idea of what in
>> the world your talking about?

Do you really think David is obligated to show an example? Obviously the
ones who cannot directly address the concept of physical data independence
will have to cling on to such frivolous differences. Shouldn't the onus be
on them to show that it matters?

--
Anith


Steve Dassin

unread,
Oct 8, 2007, 2:16:14 AM10/8/07
to
I guess if you want to sell Tupleware :)

"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:uvS3CiUC...@TK2MSFTNGP02.phx.gbl...

0 new messages