Home > IT Answers > SQL Server > SQL Select: Getting the previous records from a known record
bogeybetsy
560 pts.
 SQL Select: Getting the previous records from a known record
SELECT statement, SQL Select, SQL Select statement problem
I issued a select statement like this: Select phone# from pltlcd where phone# >= 985143525 fetch first 20 records only PLTLCD is a file sorted by phone#. From the first record retrieved by the aboe select statement, how do I get the 20 records in the file that exist before the returned set of the select statement above?

Software/Hardware used:
ASKED: August 4, 2009  4:37 PM
UPDATED: February 29, 2012  10:39 PM
RELATED QUESTIONS
Answer Wiki:
I guess this is on DB2, and I'm not sure how the 'FETCH FIRST x ROWS' works in conjunction with an ORDER BY clause, but <b>if it applies the order first, and then fetches the x rows</b>, then this should work: <pre>Select phone# from pltlcd where phone# < 985143525 order by phone# desc fetch first 20 records only</pre> ---------------------- Something along the lines of ... SELECT ... FROM ... WHERE phone# IN ( SELECT phone# FROM ... T1 WHERE 20 > (SELECT COUNT(*) FROM ... T2 WHERE T1.phone < T2.phone) ) ORDER BY phone You will, of course, have to play with this code. I did not test it, I just typed it.
Last Wiki Answer Submitted:  August 4, 2009  6:01 pm  by  carlosdl   63,535 pts.
All Answer Wiki Contributors:  carlosdl   63,535 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Thanks Carlos, it did. The records were in reverse order though. Anyway, I can reverse that in my program.

I already had that in mind but I didn’t test it until you confirmed what was on my mind. The phone number is composed of two columns, the exchange and the line #. And the address is also included in the key, which is broken down into state, city, subdivision, st. name, st. direction, st. subtitile, st. number, st. subdescription, and area sequence number. Whew! I guess I would have to apply the Desc keyword to all these fields.

To make it faster, maybe I can use a view with the ORDER BY clause already in place. After all, views are persistent. And the company doesn’t care about memory as long as the query runs in a blink of an eye. What can you suggest?

Thanks to everyone who answered!

Allan

bogeybetsy
 560 pts.
 

You should be able to get the records in the correct order this way:

select * from 
(Select phone# from pltlcd
 where phone# < 985143525
 order by phone# desc
 fetch first 20 records only)
order by phone#

As for the use of a view, I don’t really know how that RDBMS manages views, so I’m afraid I can’t advise on that.

Cheers,

carlosdl
 63,535 pts.
 

Hey Carlos,

Thanks again for the tip!

As for the view, it didn’t work out. Turns out you cannot use the ORDER BY clause with a VIEW.

Cheers!

Allan

bogeybetsy
 560 pts.