SQL Select: Getting the previous records from a known record

560 pts.
Tags:
SELECT statement
SQL Select
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • bogeybetsy
    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
    560 pointsBadges:
    report
  • carlosdl
    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,
    69,835 pointsBadges:
    report
  • bogeybetsy
    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
    560 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following