"middle" also only makes sense when you know what the order of the data is.

If I have an employee table with ten rows in it, the fifth row will probably
be a different employee when I sort by salary than if I sort by employee id.

This sort of thing seems to come up the most when a client or middle tier
developer wants paging. I've fiddled with server-side TSQL solutions, and
they've always been lacking. ADO isn't my strong suit, and I've been trying
to learn as much as I can about it as I go along my regular duties.

Things really get fun when a user is looking at the third of five pages of
data, then clicks on a column header to re-sort. Do you just re-sort that
page or do you go back to the server for a new resultset in a different sort
order? do you still show the third of five pages? what if the row she was
looking at has now sorted to the second page? What if someone else has
inserted or deleted rows and not there are six or four pages?

Darin Strait, MS SQL Server Development and Administration
http://home.earthlink.net/~dstrait/professional/resume.htm


----- Original Message -----
From: "Roland Boorman" <r_boorman@y...>
To: "sql language" <sql_language@p...>
Sent: Wednesday, June 13, 2001 3:54 AM
Subject: [sql_language] Re: select records from middle of the table


> Yes of course Darin is right
> What do we mean by the middle of a table?
>
> If you consider a multi user million records etc constantly changing
>
> data records. SQL is designed to eat this kind of thing before breakfast
>
> but of course on Sets of data.
>
> Middle makes sense when you know the number of records?
>
> So before you start the query will be count(all records) from this
>
> you can probably follow some sort of logic such as
>
> select top 1/3rds  of the (select Top 2/3rds)
>
> this sort of nested logic. In the end however the question is why
>
> would you want to know?.
>
>   Darin Strait <dstrait@e...> wrote:
> A lame example:
> create table #t(t_id integer not null identity(1,1), fname varchar(20)
null,
> lname varchar(20) null)
> insert into #t (fname, lname) select top 20 fname, lname from employees
> select top 10 fname, lname from #t order by t_id desc
> drop table #t
>
> SQL as a language isn't particularly good at row "positioning" because it
is
> intended to work on data one set at a time, not one row at a time. You
might
> be able to cobble something better than the above by using a cursor. In
> either case, I'd expect the performance to be less-than-good.
>
> I'd suggest that you look at using the paging features of ADO, if you are
> using Microsoft tools.
>
> Darin Strait, MS SQL Server Development and Administration
> http://home.earthlink.net/~dstrait/professional/resume.htm
>
>
> ----- Original Message -----
> From: "Ken Schaefer"
> To: "sql language"
> Sent: Sunday, June 10, 2001 8:53 AM
> Subject: [sql_language] Re: select records from middle of the table
>
>
> > SELECT TOP 10 FROM table1
> > WHERE ID NOT IN
> > SELECT TOP 10 FROM table1
> >
> > ?
> >
> > Cheers
> > Ken
>
$subst('Email.Unsub')
>