Home » Other » Training & Certification » Query (oracle 8i, 9i)
Query [message #324899] Wed, 04 June 2008 08:10 Go to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
Hi

I want select query for below table.

suppose I have emp table with one field ID and data into ID is like

select id from emp;

ID
1
5
9
12

Now I want query to retrive data like this

ID ID_1
1 1-5
5 6-9
9 10-15
15 15-15

Thanks in advance


[Updated on: Wed, 04 June 2008 08:11]

Report message to a moderator

Re: Query [message #324901 is a reply to message #324899] Wed, 04 June 2008 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Good exercise, post what you already tried and why it does not succeed.
Have a look at LEAD function.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Wed, 04 June 2008 08:17]

Report message to a moderator

Re: Query [message #324924 is a reply to message #324901] Wed, 04 June 2008 08:59 Go to previous message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member

Thanks Michel for giving reply.

same time i also got the answer....

My solution
1)
SELECT aid,aid || '-' || bid
FROM (SELECT a.ID aid, b.ID bid
, DENSE_RANK () OVER (PARTITION BY a.ID ORDER BY a.ID
, b.ID) rank1
FROM emp a, emp b
WHERE a.ID < b.ID)
WHERE rank1 = 1;

Your solution
2)
SELECT id,id||'-'||nvl(LEAD(id, 1) OVER (ORDER BY id),id) AS "ID_1"
FROM emp;

Thanks


Previous Topic: Left Outer Join
Next Topic: What should i learn to b'com a good PL/SQL Programmer?
Goto Forum:
  


Current Time: Wed Dec 11 22:33:14 CST 2024