| Query [message #324899] |
Wed, 04 June 2008 08:10  |
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  |
 |
Michel Cadot
Messages: 68716
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
