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

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

Post by AliRezaGoo » Thu, 04 Oct 2007 20:49:00


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?
 
 
 

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

Post by David Port » Thu, 04 Oct 2007 21:01:36


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.

 
 
 

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

Post by Jim Underw » Thu, 04 Oct 2007 22:07:41

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.yqcomputer.com/
 
 
 

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

Post by --CELKO- » Thu, 04 Oct 2007 23:34:29

>> 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


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.
 
 
 

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

Post by Jim Underw » Fri, 05 Oct 2007 03:42:56

I just stumbled across the article....
http://www.yqcomputer.com/
 
 
 

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

Post by jamie.fras » Fri, 05 Oct 2007 23:05:05


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 ;)
 
 
 

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

Post by Alex Kuzne » Fri, 05 Oct 2007 23:33:36


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

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

Post by --CELKO- » Sat, 06 Oct 2007 00:34:02

>> 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.


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.
 
 
 

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

Post by Alex Kuzne » Sat, 06 Oct 2007 01:18:03


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


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.
 
 
 

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

Post by --CELKO- » Sat, 06 Oct 2007 04:31:02

>> 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"


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.
 
 
 

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

Post by Alex Kuzne » Sat, 06 Oct 2007 06:52:39


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.


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


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.
 
 
 

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

Post by David Port » Sat, 06 Oct 2007 07:34:45


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! :)
 
 
 

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

Post by --CELKO- » Sat, 06 Oct 2007 07:54:44

>> 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.

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


agreed.


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?
 
 
 

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

Post by Steve Dass » Sat, 06 Oct 2007 08:57:10

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
 
 
 

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

Post by Alex Kuzne » Sat, 06 Oct 2007 12:11:07


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.


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.


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.