Home » SQL & PL/SQL » SQL & PL/SQL » query
query [message #281951] Tue, 20 November 2007 02:44 Go to next message
msafana
Messages: 31
Registered: July 2007
Member
hi all,
we can write queries to display odd and even no of rows of a table,
i want the query for displaying even rows at one side and odd rows on other side using single query.
can any one help me.

Thanks .
Re: query [message #281962 is a reply to message #281951] Tue, 20 November 2007 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you already tried as long as with your Oracle version (at least 3 decimals).

clues:
row_number -> can give a row number
mod -> can give if even or odd
trunc -> can give a group

Regards
Michel
Re: query [message #281975 is a reply to message #281951] Tue, 20 November 2007 03:53 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One more clue ...

You can use Max()..... group by also getting your exact result.
Smile

Thumbs Up
Rajuvan
Re: query [message #281978 is a reply to message #281975] Tue, 20 November 2007 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And decode... Wink

Regards
Michel
Re: query [message #282030 is a reply to message #281951] Tue, 20 November 2007 08:11 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
You mean even and odd rows of your query with an order by clause or number column, not even and odd rows of your table, since there is no such thing as even and odd rows of a table.
Re: query [message #282111 is a reply to message #281951] Tue, 20 November 2007 22:40 Go to previous messageGo to next message
msafana
Messages: 31
Registered: July 2007
Member
hi all,
thanks for the response,
we write the query as,
SELECT * FROM EMP WHERE ROWID IN
(SELECT DECODE(MOD(ROWNUM,2),0,ROWID) FROM EMP);


SELECT * FROM EMP WHERE ROWID IN
(SELECT DECODE(MOD(ROWNUM,2),1,ROWID) FROM EMP);

to display alternate rows from the table,that is ,the odd and even rows .

i want to write the query that display odd rows at one side and even rows at another side.

thanks .

Re: query [message #282133 is a reply to message #282111] Tue, 20 November 2007 23:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If you are saying that you want two columns, then you can use a self-join, but you need to order by something before odd-numbered or even-numbered rows have any meaning.
Re: query [message #282149 is a reply to message #281951] Wed, 21 November 2007 00:35 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ msafana :

Why couldn't you try to get your output from the clues given by Michel and Me ? Try the given query though i am unhappy with such kind of spoon-feeding.


WITH DATASET AS 
 (SELECT ENAME , MOD(ROWNUM,2) OFFSET , ROUND(ROWNUM/2) TRSET FROM EMP) 
 SELECT  MAX(DECODE (OFFSET,1,ENAME )) ODD_COL, 
 		 MAX(DECODE (OFFSET,0,ENAME )) EVN_COL
 FROM DATASET 
 GROUP BY TRSET


Even I am stick to joy_division and barbara on saying that there is no such thing as even and odd rows of a table.

regards,
Rajuvan.

Re: query [message #282160 is a reply to message #282149] Wed, 21 November 2007 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Generally speaking, use TRUNC and not ROUND to make the group.

Regards
Michel
Re: query [message #282174 is a reply to message #281951] Wed, 21 November 2007 01:23 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Generally Trunc may be fine .

For this particular case , Round might be better choice

SQL> SELECT ENAME ,rownum, MOD(ROWNUM,2) OFFSET ,
  2             TRUNC(ROWNUM/2) TRSET ,FLOOR(ROWNUM/2) FLR,
  3             ROUND(ROWNUM/2) RNSET , CEIL(ROWNUM/2) CEL
  4   FROM EMP;

ENAME          ROWNUM     OFFSET      TRSET        FLR      RNSET        CEL
---------- ---------- ---------- ---------- ---------- ---------- ----------
SMITH               1          1          0          0          1          1
ALLEN               2          0          1          1          1          1
WARD                3          1          1          1          2          2
JONES               4          0          2          2          2          2
MARTIN              5          1          2          2          3          3
MORGAN              6          0          3          3          3          3
CLARK               7          1          3          3          4          4
SCOTT               8          0          4          4          4          4
KING                9          1          4          4          5          5
TURNER             10          0          5          5          5          5
ADAMS              11          1          5          5          6          6
JAMES              12          0          6          6          6          6
FORD               13          1          6          6          7          7
MILLER             14          0          7          7          7          7

14 rows selected.

SQL>


Note the first Zero value for the Trunc and Floor functions

Thumbs Up
Rajuvan.
Re: query [message #282181 is a reply to message #282174] Wed, 21 November 2007 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh yes, of course you start at 1!
My numbers start at 0, sorry mathematical deviance. ./fa/1587/0/

Regards
Michel
Re: query [message #282199 is a reply to message #281951] Wed, 21 November 2007 02:54 Go to previous message
msafana
Messages: 31
Registered: July 2007
Member
thanks for response to all
Previous Topic: My Procedure: lock held by in-doubt distributed transaction 11.11.2720358
Next Topic: Time taken for a sub program execution
Goto Forum:
  


Current Time: Wed Dec 07 18:30:12 CST 2016

Total time taken to generate the page: 0.09465 seconds