Home » SQL & PL/SQL » SQL & PL/SQL » sql query sequnce help (10g)
sql query sequnce help [message #391131] Wed, 11 March 2009 03:33 Go to next message
u263066
Messages: 47
Registered: March 2007
Member
Hi Gurus,

there is any way to assign a group number for all records which belong to one dept like
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17/12/1980,800,,20
7499,ALLEN,SALESMAN,7698,20/02/1981,1600,300,30
7521,WARD,SALESMAN,7698,22/02/1981,1250,500,30
7566,JONES,MANAGER,7839,02/04/1981,2975,,20
7654,MARTIN,SALESMAN,7698,28/09/1981,1250,1400,30


I required to have display like
GroupCode,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
1,7369,SMITH,CLERK,7902,17/12/1980,800,,20
1,7566,JONES,MANAGER,7839,02/04/1981,2975,,20
2,7654,MARTIN,SALESMAN,7698,28/09/1981,1250,1400,30
2,7499,ALLEN,SALESMAN,7698,20/02/1981,1600,300,30
2,7521,WARD,SALESMAN,7698,22/02/1981,1250,500,30


I am just wondering if there is any Oracle function which I can use to display the seq number based out of similar group

I above example based out of division 20 and 30 group code 1 and 2 is required in such order.

any input will be highly appreciated.
Re: sql query sequnce help [message #391138 is a reply to message #391131] Wed, 11 March 2009 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
group code = deptno/10 - 1

You can also have a look at DENSE_RANK function.

Regards
Michel
Re: sql query sequnce help [message #391140 is a reply to message #391138] Wed, 11 March 2009 03:53 Go to previous messageGo to next message
u263066
Messages: 47
Registered: March 2007
Member
thanks Michel,

I tried DENSE_RANK, it is giving the rank

My requirement is to assign a group code with running sequnce

Lets say have dept 10,20,30,40,50,60,70,80,90...likewise

I am trying to assign a group code which should like 1,2,3,4,5...

that mean
all rows with dept 10 will have group code 1
all rows with dept 20 will have group code 2
...
...
all rows with dept 70 will have group code 7
like wise

I have not used such advance feature nor found in documentaion , thus looking some node from Guru's to help me out.


Re: sql query sequnce help [message #391145 is a reply to message #391140] Wed, 11 March 2009 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You badly used the function as it answers your requirements.
Post what you tried.

Regards
Michel

[Updated on: Wed, 11 March 2009 04:04]

Report message to a moderator

Re: sql query sequnce help [message #391147 is a reply to message #391140] Wed, 11 March 2009 04:03 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@u263066,

Please post the SQL PLus session of the query you have tried along with the results you got.

Regards,
Jo
Re: sql query sequnce help [message #391150 is a reply to message #391145] Wed, 11 March 2009 04:11 Go to previous messageGo to next message
u263066
Messages: 47
Registered: March 2007
Member
It was my bad, i was wrongly used RANK rather DENSE_RANK

select
RANK() OVER (PARTITION BY NULL ORDER BY deptno) "groupcode",
deptno,
empno,ename from scott.emp


i tried this and this is what i am looking


select
DENSE_RANK() OVER (PARTITION BY NULL ORDER BY deptno) "groupcode",
deptno,
empno,ename from scott.emp

Thanks to Guru.
Re: sql query sequnce help [message #391164 is a reply to message #391150] Wed, 11 March 2009 05:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"partition" is an optional clause, remove it.

Regards
Michel
Re: sql query sequnce help [message #391367 is a reply to message #391164] Wed, 11 March 2009 23:51 Go to previous messageGo to next message
u263066
Messages: 47
Registered: March 2007
Member
is there any way to pass the starting sequnce number in query rather than hardcoding the value.

My requirement is lets say first time query is run , the number should be 10000 and lets say last number i am getting is 10003.

when I run this query i just want to pass the value last number +1 as parameter.

in this query , what is way to pass 10000 as parameter

select
to_number(10000)+DENSE_RANK() OVER (PARTITION BY NULL ORDER BY deptno) "groupcode",
deptno,
empno,ename from scott.emp

any help will be great


Re: sql query sequnce help [message #391376 is a reply to message #391367] Thu, 12 March 2009 00:22 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
to_number(10000)

Isn't 10000 a number? Why do you convert it to a number? Remove to_number.

Quote:
PARTITION BY NULL

This clause just mean no partition, remove it.

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), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

You use a (context) variable to parameter your query. The ways depend on your client application. Which one is it?

Regards
Michel
Previous Topic: Help with Exists or In Statement (Merged again)
Next Topic: Data as column heading
Goto Forum:
  


Current Time: Fri Dec 02 14:02:34 CST 2016

Total time taken to generate the page: 0.12569 seconds