Oracle SQL

Use this logic to select alternative records from a table -

SQL> ed
Wrote file afiedt.buf

1 select * from
2 (select rownum row_num,exec_date from clob_test)
3* where mod(row_num,2)=1
SQL> /

ROW_NUM EXEC_DATE
---------- ---------
1 13-OCT-08
3 13-OCT-08
5 13-OCT-08
7 13-OCT-08
9 13-OCT-08
11 13-OCT-08

6 rows selected....

sk_kireeti

This can be a very handy script if you want to convert a SQL query output into a HTML table.

Execute following anonymous pl/sql block in your SQL*Plus editor -

* Sorry for bad formatting

---

declare
c number;
d number;
col_cnt integer;
f boolean;
rec_tab dbms_sql.desc_tab;
col_num number;
column_value varchar2(32767);
col_index integer;
sql_query varchar2(32767);...

sk_kireeti

A decision has to taken carefully before we implement a solution for the two possibilities -

1. When we have odd total number of rows
2. When we have even total number of rows

My SQL will work differently in each case.

In first case (odd number of total rows), this SQL query will return middle record with out any conflict. Eg., If we have 9 records, this will display 5th record from table.

In second case (even number of total rows), this SQL query will return middle two...

sk_kireeti

select * from
(select rownum sno, empno,ename from emp)
where
sno =1
or sno=(select count(*) from emp);

sk_kireeti