Home » SQL & PL/SQL » SQL & PL/SQL » sql help
sql help [message #213171] Tue, 09 January 2007 13:09 Go to next message
annsm
Messages: 2
Registered: January 2007
Junior Member
Hi, I need some help with sql. I have a column that stores certain codes as a year + either 01, 02 or 03. So the data looks
as follows:

200501
200502
200503
200601
200602
200603
200701
200702
200703
200801
200802
200803
etc

I am given a certain code. Say 200701. I need to select 3 codes before a given code and 3 codes after including the one that
is given. So, if I am given 200701, then I need to get the following data:

200601
200602
200603
200701
200702
200703
200801

The column that defines this code is VARCHAR2(6)

Thank you!

[EDIT] Removed blank lines.

[Updated on: Tue, 09 January 2007 13:54] by Moderator

Report message to a moderator

Re: sql help [message #213178 is a reply to message #213171] Tue, 09 January 2007 13:54 Go to previous messageGo to next message
Littlefoot
Messages: 21016
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This could be one way to do that:
SELECT x.col
  FROM (SELECT col, RANK () OVER (ORDER BY col DESC) rnk
          FROM TEST
         WHERE col <= '200701') x
 WHERE x.rnk <= 4
UNION
SELECT x.col
  FROM (SELECT col, RANK () OVER (ORDER BY col ASC) rnk
          FROM TEST
         WHERE col > '200701') x
 WHERE x.rnk <= 3;
Re: sql help [message #213182 is a reply to message #213171] Tue, 09 January 2007 14:05 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Fun with Analytics, nice job littlefoot
Re: sql help [message #213183 is a reply to message #213178] Tue, 09 January 2007 14:06 Go to previous messageGo to next message
annsm
Messages: 2
Registered: January 2007
Junior Member
nice! thank you!
Re: sql help [message #216146 is a reply to message #213183] Thu, 25 January 2007 05:02 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
hi guy its also
select * from (select sal from emp where sal>&n) where rownum<=3
union
select * from ( select sal from emp where sal<&n) where rownum<=3

thanks,
pavuluri
Re: sql help [message #216149 is a reply to message #216146] Thu, 25 January 2007 05:13 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry
its work see

select * from (select sal from emp where sal>2450) where rownum<=3
union
select * from ( select sal from emp where sal<=2450 order by sal desc) where rownum<=4
Re: sql help [message #216151 is a reply to message #216146] Thu, 25 January 2007 05:15 Go to previous messageGo to next message
Littlefoot
Messages: 21016
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1600/0/

What are you talking about?!? Did you miss the topic?
Re: sql help [message #216152 is a reply to message #216151] Thu, 25 January 2007 05:23 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry iam improveing my knowledge.

we have any issue plz let me know.
thanks,
srinivas pavuluri
Re: sql help [message #216155 is a reply to message #216152] Thu, 25 January 2007 05:32 Go to previous messageGo to next message
Littlefoot
Messages: 21016
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
No issue on my side; I just wondered what salaries (in your query) have to do with years (in OP's question).

But never mind me; you are searching for salary "environment". Sorry for disturbing you.
Re: sql help [message #216236 is a reply to message #216155] Thu, 25 January 2007 13:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8679
Registered: November 2002
Location: California, USA
Senior Member
I believe pavuluri was exploring other methods of getting the 3 preceeding and 3 following numerical values of a column from a table, and used the salary column of the emp demo table for testing, rather than creating a separate table with years and codes, since the original poster did not provide scripts to do so conveniently.
Re: sql help [message #216258 is a reply to message #216236] Thu, 25 January 2007 15:24 Go to previous message
Littlefoot
Messages: 21016
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, that's what I figured out too; I called it "salary 'environment'" (3 preceding and 3 following salaries). But, slow as I am, it took some time before I realized that.
Previous Topic: calling pipeline table function in Pro*C
Next Topic: Getting ora-02085 fort Database Link
Goto Forum:
  


Current Time: Thu Feb 23 15:08:03 CST 2017

Total time taken to generate the page: 0.08766 seconds