Home » SQL & PL/SQL » SQL & PL/SQL » generating a key for grouping (Oracle 11g)
generating a key for grouping [message #580698] Wed, 27 March 2013 01:22 Go to next message
Oracle1411
Messages: 2
Registered: March 2013
Location: Singapore
Junior Member
Hi,

In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.

Really appreciate help.
Conotainer #	City	Seq	I want this
-------------------------------------------
Container1	S8W	525	1
Container1	S8W	526	1
Container1	AUH	536	2
Container1	AUH	537	2
Container1	JEB	538	2
Container1	JEB	539	2
Container1	CIW	540	2
Container1	CIW	541	2
Container1	LCB	547	3
Container1	LCB	548	3
Container1	SIN	554	4
Container1	SIN	555	4
Container1	SIN	556	4
Container1	SIN	557	4
Container1	IKT	558	4
Container1	IKT	559	4


[EDITED by LF: applied [code] tags to preserve formatting]

[Updated on: Wed, 27 March 2013 02:06] by Moderator

Report message to a moderator

Re: generating a key for grouping [message #580700 is a reply to message #580698] Wed, 27 March 2013 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: generating a key for grouping [message #580720 is a reply to message #580700] Wed, 27 March 2013 05:34 Go to previous messageGo to next message
Littlefoot
Messages: 19605
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option, which uses LAG and SUM analytic functions.
SQL> with red as
  2    (select
  3       container#,
  4       city,
  5       seq,
  6       case when seq - nvl(lag(seq) over (order by seq), seq) > 1 then 1
  7            else 0
  8       end switch
  9     from test
 10    )
 11  select
 12    container#,
 13    city,
 14    seq,
 15    1 + sum(switch) over (order by seq) i_want_this
 16  from red
 17  order by seq;

CONTAINER# CIT        SEQ I_WANT_THIS
---------- --- ---------- -----------
container1 S8W        525           1
container1 S8W        526           1
container1 AUH        536           2
container1 AUH        537           2
container1 JEB        538           2
container1 JEB        539           2
container1 CIW        540           2
container1 CIW        541           2
container1 LCB        547           3
container1 LCB        548           3
container1 SIN        554           4
container1 SIN        555           4
container1 SIN        556           4
container1 SIN        557           4
container1 IKT        558           4
container1 IKT        559           4

16 rows selected.

SQL>
Re: generating a key for grouping [message #580772 is a reply to message #580720] Wed, 27 March 2013 21:56 Go to previous messageGo to next message
Oracle1411
Messages: 2
Registered: March 2013
Location: Singapore
Junior Member
Hi Littlefoot, it was a great great help. Thanks a lot. Really appreciate it.
Re: generating a key for grouping [message #580775 is a reply to message #580772] Thu, 28 March 2013 01:24 Go to previous message
Michel Cadot
Messages: 59109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are other solutions... maybe better... if you post a test case.

Regards
Michel
Previous Topic: Variable value is not being recongnize
Next Topic: Need a output from multilple rows return only one row in oracle?
Goto Forum:
  


Current Time: Wed Sep 17 09:34:26 CDT 2014

Total time taken to generate the page: 0.05494 seconds