"START AT" 
Author Message
 "START AT"

Hi!

Is there any SQL-command like "START AT".

For example:

SELECT TOP 10 * FROM tblTable START AT 20

I want something like this to return 10 records starting at the 20th record
found.

Is somehing possible?

Regards,

Ronald Kas



Fri, 15 Oct 2004 21:56:10 GMT  
 "START AT"
I don;t think there is anything equivalent to START AT.
However, if you have an unique key, you can simulate the
same with some coding

Thanks
Anith


Quote:
> Hi!

> Is there any SQL-command like "START AT".

> For example:

> SELECT TOP 10 * FROM tblTable START AT 20

> I want something like this to return 10 records starting at the 20th
record
> found.

> Is somehing possible?

> Regards,

> Ronald Kas



Fri, 15 Oct 2004 22:08:14 GMT  
 "START AT"
There is no "start at" command, but what you could try is this
(note: this requires that the result set you want is ordered by one or more
columns, my example is from Northwind..Customers):

select * from (
 select top 10 *
 from (
  select top 30 *
  from customers
  order by customerID asc
 ) as tmp
 order by customerID desc
) as tmp2
order by customerID asc

I am using two subqueries, to get the correct ordering of the final result
set. If the final ordering is not an issue, you can drop the outer query.

--

/Per Holje, MCDBA


Quote:
> Hi!

> Is there any SQL-command like "START AT".

> For example:

> SELECT TOP 10 * FROM tblTable START AT 20

> I want something like this to return 10 records starting at the 20th
record
> found.

> Is somehing possible?

> Regards,

> Ronald Kas



Fri, 15 Oct 2004 22:06:20 GMT  
 "START AT"
I have a unique key, but with sorting on other fields than the unique key,
will result in no possible 'coding'. Or am I missing something?


Quote:
> I don;t think there is anything equivalent to START AT.
> However, if you have an unique key, you can simulate the
> same with some coding

> Thanks
> Anith



> > Hi!

> > Is there any SQL-command like "START AT".

> > For example:

> > SELECT TOP 10 * FROM tblTable START AT 20

> > I want something like this to return 10 records starting at the 20th
> record
> > found.

> > Is somehing possible?

> > Regards,

> > Ronald Kas



Fri, 15 Oct 2004 22:11:46 GMT  
 "START AT"
Here's an example:

SELECT top 10 cld_ID FROM tblChild where cld_ID not in (select top 10 cld_ID
from tblChild)

Of course, the recordsets must be ordered to ensure a constant return.


Quote:
> Hi!

> Is there any SQL-command like "START AT".

> For example:

> SELECT TOP 10 * FROM tblTable START AT 20

> I want something like this to return 10 records starting at the 20th
record
> found.

> Is somehing possible?

> Regards,

> Ronald Kas



Fri, 15 Oct 2004 22:16:34 GMT  
 "START AT"
You could try paging.

Open your recordset as normal, then alter the absolutePosition property to
the 20th row.

Recordsets do not always support AbsolutePositon though.

You can experiment with this
http://www.adoanywhere.com/download/aaBrowse2_7.zip

Mike.

--
Michael Collier Bsc (Hons)
Interactive ADO Object Browser
www.adoanywhere.com

--


Quote:
> Hi!

> Is there any SQL-command like "START AT".

> For example:

> SELECT TOP 10 * FROM tblTable START AT 20

> I want something like this to return 10 records starting at the 20th
record
> found.

> Is somehing possible?

> Regards,

> Ronald Kas



Fri, 15 Oct 2004 22:21:22 GMT  
 "START AT"
Try this

SELECT TOP 10 * FROM tblTable WHERE <SORTCOL>
NOT IN (SELECT TOP 20 <SORTCOL>FROM tblTable ORDER BY <SORTCOL>)
order by <SORTCOL>

-- <SORTCOL> is the column with which you sort the resultset

Thanks
Anith


Quote:
> I have a unique key, but with sorting on other fields than the unique key,
> will result in no possible 'coding'. Or am I missing something?



> > I don;t think there is anything equivalent to START AT.
> > However, if you have an unique key, you can simulate the
> > same with some coding

> > Thanks
> > Anith



> > > Hi!

> > > Is there any SQL-command like "START AT".

> > > For example:

> > > SELECT TOP 10 * FROM tblTable START AT 20

> > > I want something like this to return 10 records starting at the 20th
> > record
> > > found.

> > > Is somehing possible?

> > > Regards,

> > > Ronald Kas



Fri, 15 Oct 2004 22:34:11 GMT  
 "START AT"
I'm aware of this option, but here all records are returned to the client.


Quote:
> You could try paging.

> Open your recordset as normal, then alter the absolutePosition property to
> the 20th row.

> Recordsets do not always support AbsolutePositon though.

> You can experiment with this
> http://www.adoanywhere.com/download/aaBrowse2_7.zip

> Mike.

> --
> Michael Collier Bsc (Hons)
> Interactive ADO Object Browser
> www.adoanywhere.com

> --



> > Hi!

> > Is there any SQL-command like "START AT".

> > For example:

> > SELECT TOP 10 * FROM tblTable START AT 20

> > I want something like this to return 10 records starting at the 20th
> record
> > found.

> > Is somehing possible?

> > Regards,

> > Ronald Kas



Fri, 15 Oct 2004 22:39:29 GMT  
 "START AT"
Dear Anith:

Very nice!  Too bad we can't use this construction when there's more than one
SORTCOL, however.  We'll still need both solutions, methinks.

I'm now wondering if ANSI provided for:

SELECT TOP 10 * FROM tblTable WHERE (SORTCOL1, SORTCOL2)
NOT IN (SELECT TOP 20 SORTCOL1, SORTCOL2 FROM tblTable ORDER BY SORTCOL1,
SORTCOL2)
order by SORTCOL1, SORTCOL2

I'm pretty certain this isn't implemented in Jet or SQL Server, however.  Just
dreaming, I guess!

Tom Ellison
Microsoft Access MVP

Quote:

> Try this

> SELECT TOP 10 * FROM tblTable WHERE <SORTCOL>
> NOT IN (SELECT TOP 20 <SORTCOL>FROM tblTable ORDER BY <SORTCOL>)
> order by <SORTCOL>

> -- <SORTCOL> is the column with which you sort the resultset

> Thanks
> Anith



> > I have a unique key, but with sorting on other fields than the unique key,
> > will result in no possible 'coding'. Or am I missing something?



> > > I don;t think there is anything equivalent to START AT.
> > > However, if you have an unique key, you can simulate the
> > > same with some coding

> > > Thanks
> > > Anith



> > > > Hi!

> > > > Is there any SQL-command like "START AT".

> > > > For example:

> > > > SELECT TOP 10 * FROM tblTable START AT 20

> > > > I want something like this to return 10 records starting at the 20th
> > > record
> > > > found.

> > > > Is somehing possible?

> > > > Regards,

> > > > Ronald Kas



Fri, 15 Oct 2004 23:13:59 GMT  
 "START AT"
Tom-

ANSI does define "row value comparators", but doesn't have a clue about TOP.
Access and SQL Server support TOP, but don't do row value comparisons.

Tant pis.

--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

Dear Anith:

Very nice!  Too bad we can't use this construction when there's more than
one
SORTCOL, however.  We'll still need both solutions, methinks.

I'm now wondering if ANSI provided for:

SELECT TOP 10 * FROM tblTable WHERE (SORTCOL1, SORTCOL2)
NOT IN (SELECT TOP 20 SORTCOL1, SORTCOL2 FROM tblTable ORDER BY SORTCOL1,
SORTCOL2)
order by SORTCOL1, SORTCOL2

I'm pretty certain this isn't implemented in Jet or SQL Server, however.
Just
dreaming, I guess!

Tom Ellison
Microsoft Access MVP

Quote:

> Try this

> SELECT TOP 10 * FROM tblTable WHERE <SORTCOL>
> NOT IN (SELECT TOP 20 <SORTCOL>FROM tblTable ORDER BY <SORTCOL>)
> order by <SORTCOL>

> -- <SORTCOL> is the column with which you sort the resultset

> Thanks
> Anith



> > I have a unique key, but with sorting on other fields than the unique
key,
> > will result in no possible 'coding'. Or am I missing something?



> > > I don;t think there is anything equivalent to START AT.
> > > However, if you have an unique key, you can simulate the
> > > same with some coding

> > > Thanks
> > > Anith



> > > > Hi!

> > > > Is there any SQL-command like "START AT".

> > > > For example:

> > > > SELECT TOP 10 * FROM tblTable START AT 20

> > > > I want something like this to return 10 records starting at the 20th
> > > record
> > > > found.

> > > > Is somehing possible?

> > > > Regards,

> > > > Ronald Kas



Fri, 15 Oct 2004 23:21:13 GMT  
 "START AT"
ANSI provides for the row constructors, but not for TOP.  In any case,
you can still do this if (sortcol1, sortcol2) is a candidate key (no two
distinct rows that match on these two columns)




from (
  select top 20 sortcol1, sortcol2 from T
  order by sortcol1, sortcol2
) X
order by sortcol1, sortcol2 desc

select top 10 * from T
where


order by sortcol1, sortcol2

If you need to do it as a single query:

select top 10 T.sortcol1, T.sortcol2 from T, (
  select top 1 Y.sortcol1, Y.sortcol2
  from (
    select top 20 T1.sortcol1, T1.sortcol2 from T T1
    order by T1.sortcol1, T1.sortcol2
  ) Y
order by Y.sortcol1, Y.sortcol2 desc
) X
where
  (T.sortcol1 > X.sortcol1) or
  (T.sortcol1 = X.sortcol1 and sortcol2 >= X.sortcol2)
order by T.sortcol1, T.sortcol2

(I think - it's untested)

Steve Kass
Drew University

Quote:

> Dear Anith:

> Very nice!  Too bad we can't use this construction when there's more than one
> SORTCOL, however.  We'll still need both solutions, methinks.

> I'm now wondering if ANSI provided for:

> SELECT TOP 10 * FROM tblTable WHERE (SORTCOL1, SORTCOL2)
> NOT IN (SELECT TOP 20 SORTCOL1, SORTCOL2 FROM tblTable ORDER BY SORTCOL1,
> SORTCOL2)
> order by SORTCOL1, SORTCOL2

> I'm pretty certain this isn't implemented in Jet or SQL Server, however.  Just
> dreaming, I guess!

> Tom Ellison
> Microsoft Access MVP


> > Try this

> > SELECT TOP 10 * FROM tblTable WHERE <SORTCOL>
> > NOT IN (SELECT TOP 20 <SORTCOL>FROM tblTable ORDER BY <SORTCOL>)
> > order by <SORTCOL>

> > -- <SORTCOL> is the column with which you sort the resultset

> > Thanks
> > Anith



> > > I have a unique key, but with sorting on other fields than the unique key,
> > > will result in no possible 'coding'. Or am I missing something?



> > > > I don;t think there is anything equivalent to START AT.
> > > > However, if you have an unique key, you can simulate the
> > > > same with some coding

> > > > Thanks
> > > > Anith



> > > > > Hi!

> > > > > Is there any SQL-command like "START AT".

> > > > > For example:

> > > > > SELECT TOP 10 * FROM tblTable START AT 20

> > > > > I want something like this to return 10 records starting at the 20th
> > > > record
> > > > > found.

> > > > > Is somehing possible?

> > > > > Regards,

> > > > > Ronald Kas



Fri, 15 Oct 2004 23:30:40 GMT  
 "START AT"
How about this

SELECT TOP 10 * FROM tblTable WHERE SORTCOL1
NOT IN
(SELECT TOP 20 SORTCOL1 FROM tblTable ORDER BY SORTCOL1, SORTCOL2)
AND SORTCOL2 NOT IN
(SELECT TOP 20 SORTCOL2 FROM tblTable ORDER BY SORTCOL1, SORTCOL2)
order by SORTCOL1, SORTCOL2

Thanks
Anith


Quote:
> Dear Anith:

> Very nice!  Too bad we can't use this construction when there's more than
one
> SORTCOL, however.  We'll still need both solutions, methinks.

> I'm now wondering if ANSI provided for:

> SELECT TOP 10 * FROM tblTable WHERE (SORTCOL1, SORTCOL2)
> NOT IN (SELECT TOP 20 SORTCOL1, SORTCOL2 FROM tblTable ORDER BY SORTCOL1,
> SORTCOL2)
> order by SORTCOL1, SORTCOL2

> I'm pretty certain this isn't implemented in Jet or SQL Server, however.
Just
> dreaming, I guess!

> Tom Ellison
> Microsoft Access MVP


> > Try this

> > SELECT TOP 10 * FROM tblTable WHERE <SORTCOL>
> > NOT IN (SELECT TOP 20 <SORTCOL>FROM tblTable ORDER BY <SORTCOL>)
> > order by <SORTCOL>

> > -- <SORTCOL> is the column with which you sort the resultset

> > Thanks
> > Anith



> > > I have a unique key, but with sorting on other fields than the unique
key,
> > > will result in no possible 'coding'. Or am I missing something?



> > > > I don;t think there is anything equivalent to START AT.
> > > > However, if you have an unique key, you can simulate the
> > > > same with some coding

> > > > Thanks
> > > > Anith



> > > > > Hi!

> > > > > Is there any SQL-command like "START AT".

> > > > > For example:

> > > > > SELECT TOP 10 * FROM tblTable START AT 20

> > > > > I want something like this to return 10 records starting at the
20th
> > > > record
> > > > > found.

> > > > > Is somehing possible?

> > > > > Regards,

> > > > > Ronald Kas



Fri, 15 Oct 2004 23:31:47 GMT  
 "START AT"
Dear John and Steve:

So, are we saying that the ANSI based solution would be to rank the results and
choose "BETWEEN 21 AND 30"?

Somewhere between standards and implementation it looks like we need a really good,
efficient, and FAST way to rank query results.

Or, in the standard, is there some other method that has been suggested?

And, the big question, what engine is it that implements all of ANSI and is faster
than SQL Server or Oracle?  <grin>

Tom Ellison
Microsoft Access MVP

Quote:

> ANSI provides for the row constructors, but not for TOP.  In any case,
> you can still do this if (sortcol1, sortcol2) is a candidate key (no two
> distinct rows that match on these two columns)




> from (
>   select top 20 sortcol1, sortcol2 from T
>   order by sortcol1, sortcol2
> ) X
> order by sortcol1, sortcol2 desc

> select top 10 * from T
> where


> order by sortcol1, sortcol2

> If you need to do it as a single query:

> select top 10 T.sortcol1, T.sortcol2 from T, (
>   select top 1 Y.sortcol1, Y.sortcol2
>   from (
>     select top 20 T1.sortcol1, T1.sortcol2 from T T1
>     order by T1.sortcol1, T1.sortcol2
>   ) Y
> order by Y.sortcol1, Y.sortcol2 desc
> ) X
> where
>   (T.sortcol1 > X.sortcol1) or
>   (T.sortcol1 = X.sortcol1 and sortcol2 >= X.sortcol2)
> order by T.sortcol1, T.sortcol2

> (I think - it's untested)

> Steve Kass
> Drew University


> > Dear Anith:

> > Very nice!  Too bad we can't use this construction when there's more than one
> > SORTCOL, however.  We'll still need both solutions, methinks.

> > I'm now wondering if ANSI provided for:

> > SELECT TOP 10 * FROM tblTable WHERE (SORTCOL1, SORTCOL2)
> > NOT IN (SELECT TOP 20 SORTCOL1, SORTCOL2 FROM tblTable ORDER BY SORTCOL1,
> > SORTCOL2)
> > order by SORTCOL1, SORTCOL2

> > I'm pretty certain this isn't implemented in Jet or SQL Server, however.  Just
> > dreaming, I guess!

> > Tom Ellison
> > Microsoft Access MVP


> > > Try this

> > > SELECT TOP 10 * FROM tblTable WHERE <SORTCOL>
> > > NOT IN (SELECT TOP 20 <SORTCOL>FROM tblTable ORDER BY <SORTCOL>)
> > > order by <SORTCOL>

> > > -- <SORTCOL> is the column with which you sort the resultset

> > > Thanks
> > > Anith



> > > > I have a unique key, but with sorting on other fields than the unique key,
> > > > will result in no possible 'coding'. Or am I missing something?



> > > > > I don;t think there is anything equivalent to START AT.
> > > > > However, if you have an unique key, you can simulate the
> > > > > same with some coding

> > > > > Thanks
> > > > > Anith



> > > > > > Hi!

> > > > > > Is there any SQL-command like "START AT".

> > > > > > For example:

> > > > > > SELECT TOP 10 * FROM tblTable START AT 20

> > > > > > I want something like this to return 10 records starting at the 20th
> > > > > record
> > > > > > found.

> > > > > > Is somehing possible?

> > > > > > Regards,

> > > > > > Ronald Kas



Fri, 15 Oct 2004 23:46:26 GMT  
 "START AT"
Dear Anith:

Sorry, that is in error.  The two sort columns cannot be considered in
isolation.

Consider the following results, with ranking added

20    X    Y
21    X    Z

You want row 21, but it has a value of the SORTCOL1 = 'X', which is a value of
SORTCOL1 that is in the TOP 20 because, in row 20, SORTCOL1 = 'X' as well.

Tom Ellison
Microsoft Access MVP

Quote:

> How about this

> SELECT TOP 10 * FROM tblTable WHERE SORTCOL1
> NOT IN
> (SELECT TOP 20 SORTCOL1 FROM tblTable ORDER BY SORTCOL1, SORTCOL2)
> AND SORTCOL2 NOT IN
> (SELECT TOP 20 SORTCOL2 FROM tblTable ORDER BY SORTCOL1, SORTCOL2)
> order by SORTCOL1, SORTCOL2

> Thanks
> Anith



> > Dear Anith:

> > Very nice!  Too bad we can't use this construction when there's more than
> one
> > SORTCOL, however.  We'll still need both solutions, methinks.

> > I'm now wondering if ANSI provided for:

> > SELECT TOP 10 * FROM tblTable WHERE (SORTCOL1, SORTCOL2)
> > NOT IN (SELECT TOP 20 SORTCOL1, SORTCOL2 FROM tblTable ORDER BY SORTCOL1,
> > SORTCOL2)
> > order by SORTCOL1, SORTCOL2

> > I'm pretty certain this isn't implemented in Jet or SQL Server, however.
> Just
> > dreaming, I guess!

> > Tom Ellison
> > Microsoft Access MVP


> > > Try this

> > > SELECT TOP 10 * FROM tblTable WHERE <SORTCOL>
> > > NOT IN (SELECT TOP 20 <SORTCOL>FROM tblTable ORDER BY <SORTCOL>)
> > > order by <SORTCOL>

> > > -- <SORTCOL> is the column with which you sort the resultset

> > > Thanks
> > > Anith



> > > > I have a unique key, but with sorting on other fields than the unique
> key,
> > > > will result in no possible 'coding'. Or am I missing something?



> > > > > I don;t think there is anything equivalent to START AT.
> > > > > However, if you have an unique key, you can simulate the
> > > > > same with some coding

> > > > > Thanks
> > > > > Anith



> > > > > > Hi!

> > > > > > Is there any SQL-command like "START AT".

> > > > > > For example:

> > > > > > SELECT TOP 10 * FROM tblTable START AT 20

> > > > > > I want something like this to return 10 records starting at the
> 20th
> > > > > record
> > > > > > found.

> > > > > > Is somehing possible?

> > > > > > Regards,

> > > > > > Ronald Kas



Fri, 15 Oct 2004 23:50:08 GMT  
 "START AT"
Hi Tom,

I am sure you woould have found a solution by now; Anyway the subqueries
are not set in isolation, the records in both the result sets in both cases
are the same, since the order by columns are not changed. So the sub query
returns the row 20 and hence the mainquery gets the row
21    X    Z

Hope it is clear. If I am wrong, don't be mad, though :-)
Thanks
Anith


Quote:
> Dear Anith:

> Sorry, that is in error.  The two sort columns cannot be considered in
> isolation.

> Consider the following results, with ranking added

> 20    X    Y
> 21    X    Z

> You want row 21, but it has a value of the SORTCOL1 = 'X', which is a
value of
> SORTCOL1 that is in the TOP 20 because, in row 20, SORTCOL1 = 'X' as well.

> Tom Ellison
> Microsoft Access MVP


> > How about this

> > SELECT TOP 10 * FROM tblTable WHERE SORTCOL1
> > NOT IN
> > (SELECT TOP 20 SORTCOL1 FROM tblTable ORDER BY SORTCOL1, SORTCOL2)
> > AND SORTCOL2 NOT IN
> > (SELECT TOP 20 SORTCOL2 FROM tblTable ORDER BY SORTCOL1, SORTCOL2)
> > order by SORTCOL1, SORTCOL2

> > Thanks
> > Anith



> > > Dear Anith:

> > > Very nice!  Too bad we can't use this construction when there's more
than
> > one
> > > SORTCOL, however.  We'll still need both solutions, methinks.

> > > I'm now wondering if ANSI provided for:

> > > SELECT TOP 10 * FROM tblTable WHERE (SORTCOL1, SORTCOL2)
> > > NOT IN (SELECT TOP 20 SORTCOL1, SORTCOL2 FROM tblTable ORDER BY
SORTCOL1,
> > > SORTCOL2)
> > > order by SORTCOL1, SORTCOL2

> > > I'm pretty certain this isn't implemented in Jet or SQL Server,
however.
> > Just
> > > dreaming, I guess!

> > > Tom Ellison
> > > Microsoft Access MVP


> > > > Try this

> > > > SELECT TOP 10 * FROM tblTable WHERE <SORTCOL>
> > > > NOT IN (SELECT TOP 20 <SORTCOL>FROM tblTable ORDER BY <SORTCOL>)
> > > > order by <SORTCOL>

> > > > -- <SORTCOL> is the column with which you sort the resultset

> > > > Thanks
> > > > Anith



> > > > > I have a unique key, but with sorting on other fields than the
unique
> > key,
> > > > > will result in no possible 'coding'. Or am I missing something?



> > > > > > I don;t think there is anything equivalent to START AT.
> > > > > > However, if you have an unique key, you can simulate the
> > > > > > same with some coding

> > > > > > Thanks
> > > > > > Anith



> > > > > > > Hi!

> > > > > > > Is there any SQL-command like "START AT".

> > > > > > > For example:

> > > > > > > SELECT TOP 10 * FROM tblTable START AT 20

> > > > > > > I want something like this to return 10 records starting at
the
> > 20th
> > > > > > record
> > > > > > > found.

> > > > > > > Is somehing possible?

> > > > > > > Regards,

> > > > > > > Ronald Kas



Sat, 16 Oct 2004 00:07:45 GMT  
 
 [ 30 post ]  Go to page: [1] [2] [3]

 Relevant Pages 

1. "Start Menu" in Windows

2. Pleae HELP with Shell "Start MailTo:"

3. Set the "Start In" Location

4. Icons in "start/programs" and in "desktop"

5. Modifying the "Start In" box of an Applet

6. "Starting Error" - any ideas ???

7. PLEASE HELP!!!! Getting "Start in" Folder info

8. How do I Invoke the "Start Menu"?

9. Need to set "START IN" field of a shortcut

10. HOWTO: Add menu items that resemble "start menu" items (bitmap and string)

11. *"*-.,._,.-*"* I"LL TRADE VISUAL C++ FOR VBASIC *"*-.,_,.-*"*

12. problems by starting a "splash-form"

 

 
Powered by phpBB® Forum Software