Home » SQL & PL/SQL » SQL & PL/SQL » A query required
A query required [message #205854] Mon, 27 November 2006 23:11 Go to next message
kanis
Messages: 61
Registered: November 2006
Member
I have table in oracle and the data in the table as follows

Reference col1 col2
-------- --- ----
ref1 A B
ref2 A B
ref3 D E
ref4 D E


I want a query which give the folowing o/p

Reference1 col1 col2 reference2
-------- --- ---- ----------
ref1 A B ref2
ref3 D E ref4


can any one suggest a select query ??
Re: A query required [message #205944 is a reply to message #205854] Tue, 28 November 2006 03:44 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Like this:

SQL> CREATE TABLE test (reference CHAR(4), col1 CHAR(1), col2 CHAR(1));

Table created.

SQL> INSERT INTO test VALUES ('ref1', 'A', 'B');

1 row created.

SQL> INSERT INTO test VALUES ('ref2', 'A', 'B');

1 row created.

SQL> INSERT INTO test VALUES ('ref3', 'D', 'E');

1 row created.

SQL> INSERT INTO test VALUES ('ref4', 'D', 'E');

1 row created.

SQL>
SQL> SELECT UNIQUE
  2         FIRST_VALUE(reference) OVER (ORDER BY col1),
  3         col1,
  4         col2,
  5         LAST_VALUE(reference) OVER (ORDER BY col1)
  6    FROM test;

FIRS C C LAST
---- - - ----
ref1 A B ref2
ref1 D E ref4
Previous Topic: Pivot Query in Oracle 8i
Next Topic: selecting data from remote database[sql server]
Goto Forum:
  


Current Time: Sat Dec 10 04:55:08 CST 2016

Total time taken to generate the page: 0.09991 seconds