SQL Select: Getting the previous records from a known record
Home > IT Answers > SQL Server > SQL Select: Getting the previous records from...
Bogeybetsy
325 pts.
0
Q:
SQL Select: Getting the previous records from a known record
SQL Select statement problem, SQL Select, SELECT statement
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?
ASKED: Aug 4 2009  4:37 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Meandyou
1840 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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 if it applies the order first, and then fetches the x rows, then this should work:

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


----------------------

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 Answered: Aug 4 2009  6:01 PM GMT by Meandyou   1840 pts.
Latest Contributors: Carlosdl   29845 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Bogeybetsy   325 pts.  |   Aug 5 2009  6:55AM GMT

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

 

Carlosdl   29845 pts.  |   Aug 5 2009  8:20AM GMT

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,

 

Bogeybetsy   325 pts.  |   Aug 5 2009  10:45AM GMT

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

 
0