Newby Query Question

Newby Query Question

Post by Rob Whitzma » Tue, 05 Oct 1999 04:00:00



Table has three fields
CustNo
LastName
FirstName

PK on CustNo
IX on Lastname_FirstName_CustNo

If I want to find "John Smith" and the next 20 ROWS following that based on
the sort order of the IX_LastName_FirstName_CustNo

What SQL expression can I use?

Thanks

--

--------------------------------------------------
Rob Whitzman, H.C.C.E,  BSc.
Systems Consultant
LONTECH Software Systems

http://www.lontech.com

 
 
 

Newby Query Question

Post by BPMargoli » Tue, 05 Oct 1999 04:00:00


Rob,

If you are using SQL Server 7.0:

select top 21 *
from <table>
where LastName = 'Smith'
and     FirstName = 'John'
order by LastName, FirstName, CustNo

For SQL Server 6.x (although it will also work on SS7):

set rowcount 21

select *
from <table>
where LastName = 'Smith'
and     FirstName = 'John'
order by LastName, FirstName, CustNo

set rowcount 0


> Table has three fields
> CustNo
> LastName
> FirstName

> PK on CustNo
> IX on Lastname_FirstName_CustNo

> If I want to find "John Smith" and the next 20 ROWS following that based
on
> the sort order of the IX_LastName_FirstName_CustNo

> What SQL expression can I use?

> Thanks

> --

> --------------------------------------------------
> Rob Whitzman, H.C.C.E,  BSc.
> Systems Consultant
> LONTECH Software Systems

> http://www.lontech.com


 
 
 

Newby Query Question

Post by Ram Saman » Wed, 06 Oct 1999 04:00:00


Hi,
    I think to get rows beyond those matching 'John Smith' you should make it
'>=', other wise you may not necessarily get more than the exact number of
'John Smith' rows.
regards
ram

> Rob,

> If you are using SQL Server 7.0:

> select top 21 *
> from <table>
> where LastName = 'Smith'
> and     FirstName = 'John'
> order by LastName, FirstName, CustNo

> For SQL Server 6.x (although it will also work on SS7):

> set rowcount 21

> select *
> from <table>
> where LastName = 'Smith'
> and     FirstName = 'John'
> order by LastName, FirstName, CustNo

> set rowcount 0



> > Table has three fields
> > CustNo
> > LastName
> > FirstName

> > PK on CustNo
> > IX on Lastname_FirstName_CustNo

> > If I want to find "John Smith" and the next 20 ROWS following that based
> on
> > the sort order of the IX_LastName_FirstName_CustNo

> > What SQL expression can I use?

> > Thanks

> > --

> > --------------------------------------------------
> > Rob Whitzman, H.C.C.E,  BSc.
> > Systems Consultant
> > LONTECH Software Systems

> > http://www.lontech.com

 
 
 

Newby Query Question

Post by Roy Harve » Wed, 06 Oct 1999 04:00:00


Rob,

Quote:>Table has three fields
>CustNo
>LastName
>FirstName

>PK on CustNo
>IX on Lastname_FirstName_CustNo

>If I want to find "John Smith" and the next 20 ROWS following that based on
>the sort order of the IX_LastName_FirstName_CustNo

The basic query, without limiting it to 21 rows:

  SELECT *
    FROM tbl
   WHERE (LastName = 'Smith' and FirstName >= 'John')
      OR (LastName > 'Smith')
   ORDER BY LastName, FirstName, CustNo

As BP showed, to get the specific 21 rows with 7.0 use TOP, and with
anything earlier use rowcount.

Roy

 
 
 

Newby Query Question

Post by BPMargoli » Wed, 06 Oct 1999 04:00:00


Yep, I messed that one up. Only excuse (and there really is no excuse for
posting incorrect code) is that it was getting into the night and it was the
last posting I responded to.


> Hi,
>     I think to get rows beyond those matching 'John Smith' you should make
it
> '>=', other wise you may not necessarily get more than the exact number of
> 'John Smith' rows.
> regards
> ram


> > Rob,

> > If you are using SQL Server 7.0:

> > select top 21 *
> > from <table>
> > where LastName = 'Smith'
> > and     FirstName = 'John'
> > order by LastName, FirstName, CustNo

> > For SQL Server 6.x (although it will also work on SS7):

> > set rowcount 21

> > select *
> > from <table>
> > where LastName = 'Smith'
> > and     FirstName = 'John'
> > order by LastName, FirstName, CustNo

> > set rowcount 0



> > > Table has three fields
> > > CustNo
> > > LastName
> > > FirstName

> > > PK on CustNo
> > > IX on Lastname_FirstName_CustNo

> > > If I want to find "John Smith" and the next 20 ROWS following that
based
> > on
> > > the sort order of the IX_LastName_FirstName_CustNo

> > > What SQL expression can I use?

> > > Thanks

> > > --

> > > --------------------------------------------------
> > > Rob Whitzman, H.C.C.E,  BSc.
> > > Systems Consultant
> > > LONTECH Software Systems

> > > http://www.lontech.com

 
 
 

Newby Query Question

Post by BPMargoli » Wed, 06 Oct 1999 04:00:00


Roy,

Thanks for correcting the code I messed up.

Only excuse (and there really is no excuse for posting incorrect code) is
that it was getting into the night and it was the last posting I responded
to.


Quote:> Rob,

> >Table has three fields
> >CustNo
> >LastName
> >FirstName

> >PK on CustNo
> >IX on Lastname_FirstName_CustNo

> >If I want to find "John Smith" and the next 20 ROWS following that based
on
> >the sort order of the IX_LastName_FirstName_CustNo

> The basic query, without limiting it to 21 rows:

>   SELECT *
>     FROM tbl
>    WHERE (LastName = 'Smith' and FirstName >= 'John')
>       OR (LastName > 'Smith')
>    ORDER BY LastName, FirstName, CustNo

> As BP showed, to get the specific 21 rows with 7.0 use TOP, and with
> anything earlier use rowcount.

> Roy

 
 
 

1. Newby Query Question

Rob,

If you are using SQL Server 7.0:

select top 21 *
from <table>
where LastName = 'Smith'
and     FirstName = 'John'
order by LastName, FirstName, CustNo

For SQL Server 6.x (although it will also work on SS7):

set rowcount 21

select *
from <table>
where LastName = 'Smith'
and     FirstName = 'John'
order by LastName, FirstName, CustNo

set rowcount 0


2. Irratic PL/SQL behaviour

3. Newby Tough Query

4. Matching Problem!!!!

5. Newby: SQL Query Help Please

6. ESQL/C and C++??

7. Questions from a newby

8. Update trigger, for each row, constraint problem

9. RecordSet Newby Question

10. Newby Question - Cost of Low end DB/2 Licenses

11. newby trigger question

12. Newby Question ... Removing a Column

13. Newby question: import Excel worksheet