Newby Query Question

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 Rob Whitzma » Wed, 06 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 Deon » Wed, 06 Oct 1999 04:00:00


Rob, that won't work mate. That will only give you 20 rows if there are 20
John Smith's in the Db, most probably you would only get 1 or 2 rows. A
cursor or temp table would have to be used where you check for LastName >=
'Smith', find 'John' for FirstName and then the next 20 rows.

Deon.


>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 Deon » Wed, 06 Oct 1999 04:00:00


Rob, that won't work mate. That will only give you 20 rows if there are 20
John Smith's in the Db, most probably you would only get 1 or 2 rows. A
cursor or temp table would have to be used where you check for LastName >=
'Smith', find 'John' for FirstName and then the next 20 rows.

Deon.


>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 M Store » Wed, 06 Oct 1999 04:00:00


How about

set rowcount 21

select *
from <table>
where (ltrim(rtrim(LastName))   + ' ' + ltrim(rtrim(FirstName)) & + ' '  +
ltrim(rtrim(CustNo))) >= "JohnSmith"
order by (ltrim(rtrim(LastName))   + ' ' + ltrim(rtrim(FirstName)) & + ' '
+ltrim(rtrim(CustNo)))

set rowcount 0

Don't know if the trims are strictly necessary but I would put them in for
safety sake.

????


> Rob, that won't work mate. That will only give you 20 rows if there are 20
> John Smith's in the Db, most probably you would only get 1 or 2 rows. A
> cursor or temp table would have to be used where you check for LastName >=
> 'Smith', find 'John' for FirstName and then the next 20 rows.

> Deon.


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

 
 
 

1. Newby Query Question

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

2. Indexes and locks

3. Newby: SQL Query Help Please

4. Big or Small Database Devices?

5. Newby Tough Query

6. FMP-CurrentTime function question

7. ?Miscellaneous Newby Stupid Questions

8. dbstart and dbshut for Oracle 9i - Solaris8

9. Newby SP result set question

10. newby trigger question

11. Newby SQL Question

12. newby question about copying database

13. newby stored procedure question