Home » SQL & PL/SQL » SQL & PL/SQL » typical query (sql)
typical query [message #365356] Thu, 11 December 2008 13:04 Go to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
i have 2 columns in a tables as follows
WK ID
1 NULL
3 1
9 NULL
10 NULL
11 10
12 10
13 11
14 11
15 11
16 11
17 12
18 12
19 NULL
20 19
21 19
22 20
23 20
24 20
25 21
26 21
27 21

now the out put i require is based on my id say 11 i should fetch all the corresponding wk ie 13,14,15,16 i shuld do that even for null
so my table shuld look like
wx id
null 1 9 10 19
1 3
10 11 12
11 13 14 15 16
12 17 18
19 20 21
20 22 23 24
21 25 26 27
so how do i do this ??
please help me urgent
and they wanted it in a sql query
Re: typical query [message #365358 is a reply to message #365356] Thu, 11 December 2008 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
read OraFAQ Forum Guide and
1/ Format your post
2/ Don't use "urgent"
3/ Post a test case
4/ Post what you tried

and above all search BEFORE posting, this is asked every week.

Regards
Michel
Re: typical query [message #365367 is a reply to message #365358] Thu, 11 December 2008 14:06 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
its is not because i am lazy to try i post i post because i dont get it. This is my own version of getting it
if possible please give me a ver which doesnt have any functions
THANQ FOR ALL THE SUGGESTION
NEXT TIME I TRY TO FIT INTO THEM
skarri>ed
Wrote file afiedt.buf

1 SELECT sw,
2 LTRIM(MAX( SYS_CONNECT_BY_PATH ( id, ',')),',') EMP_STRING
3 FROM
4 (SELECT id,sw,
5 row_number() OVER ( PARTITION BY sw ORDER BY rownum) rn
6 FROM maxy)
7 CONNECT BY sw = PRIOR sw
8 AND rn = PRIOR rn+1
9 START WITH rn =1
10 GROUP BY sw
11* ORDER BY sw
12 ;

SW EMP_STRING
---------- -------------------------------------------------------------------------------------
1 3
10 11,12
11 13,14,15,16
12 17,18
19 20,21
20 22,23,24
21 25,26,27
1
Re: typical query [message #365370 is a reply to message #365367] Thu, 11 December 2008 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
NEXT TIME I TRY TO FIT INTO THEM

Why didn't you do it in this very post and format it and don't use IM speak and post your Oracle version and post a test case and do not post in UPPER case?
Start doing this and then we will see.

Regards
Michel

[Updated on: Thu, 11 December 2008 14:18]

Report message to a moderator

Re: typical query [message #365371 is a reply to message #365370] Thu, 11 December 2008 14:21 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
see i tired to edit it but even when i give a gap between my two columns they are getting close after submission and about im language i will not use it any more i am trying to maintain a friendly relation with you why are you so serious at me.
and i think the above post is in oracle version

[Updated on: Thu, 11 December 2008 14:30]

Report message to a moderator

Re: typical query [message #365642 is a reply to message #365371] Thu, 11 December 2008 14:39 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Placing items in [CODE] and [/CODE] tags will format your code.

wx id
null 1 9 10 19
1 3
10 11 12
11 13 14 15 16

vs.

wx   id
null 1 9 10 19
1    3
10   11 12
11   13 14 15 16
Re: typical query [message #365643 is a reply to message #365642] Thu, 11 December 2008 14:42 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
thanks for the suggestion and visual i will follow it
Re: typical query [message #366221 is a reply to message #365643] Fri, 12 December 2008 03:16 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Hi,
i discovered a new problem with code please observe the follow code
skarri>SELECT * FROM MAXY
  2  ;

        ID         SW
---------- ----------
         1
         3          1
         9
        10
        11         10
        12         10
        13         11
        14         11
        15         11
        16         11
        17         12

        ID         SW
---------- ----------
        18         12
        19
        20         19
        21         19
        22         20
        23         20
        24         20
        25         21
        26         21
        27         21

21 rows selected.
skarri>ed
Wrote file afiedt.buf

  1  SELECT sw,
  2            LTRIM(MAX( SYS_CONNECT_BY_PATH ( id, ',')),',') NO_STRING
  3     FROM
  4    (SELECT  id,sw,
  5              row_number() OVER ( PARTITION BY sw  ORDER BY rownum) rn
  6      FROM  maxy)
  7     CONNECT  BY  sw = PRIOR sw
  8             AND  rn     = PRIOR rn+1
  9     START WITH rn =1
 10    GROUP BY sw
 11*  ORDER BY sw
 12  ;

        SW    NO_STRING
---------------------------------------------------------------------------------------------------
         1    3
        10    11,12
        11    13,14,15,16
        12    17,18
        19    20,21
        20    22,23,24
        21    25,26,27
              1

8 rows selected.


the problem here is in the output every thing is represented correctly except for null it is represent for null as only one NO_STRING value 1 but actually it has to be 1,9,10,19 now how do i do this
Re: typical query [message #366230 is a reply to message #366221] Fri, 12 December 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad I have no test case otherwise I could test your query and something else.

Regards
Michel
Re: typical query [message #366250 is a reply to message #366221] Fri, 12 December 2008 05:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You just need to add this condition in your connect by clause
connect by ( (sw = prior sw) or (sw is null and prior sw is null))

See if that helps.

Regards

Raj
Re: typical query [message #366254 is a reply to message #366250] Fri, 12 December 2008 05:21 Go to previous message
sekharsomu
Messages: 69
Registered: December 2008
Member
thanks for the help
Previous Topic: case or decode
Next Topic: Get particular data using decode(urgent)
Goto Forum:
  


Current Time: Sun Dec 11 06:01:02 CST 2016

Total time taken to generate the page: 0.11678 seconds