Home » Open Source » MySQL » select statement as per the columns label (oracle 11 g, windows 7)
select statement as per the columns label [message #655821] |
Tue, 13 September 2016 12:39 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f46e4c8e7acca28da6041a92fbba1f16?s=64&d=mm&r=g) |
chinmay89
Messages: 11 Registered: August 2016
|
Junior Member |
|
|
Hi All,
I have one requirement which is as follows.
create table bom_master (ID int NOT NULL AUTO_INCREMENT,label int, product varchar(20),PRIMARY KEY (ID));
Insert statements
insert into bom_master (label,product)values(1,'Budwieser');
insert into bom_master (label,product)values(2,'Heineken');
insert into bom_master (label,product)values(2,'Miller');
insert into bom_master (label,product)values(3,'Castle lite');
insert into bom_master (label,product)values(4,'Castle lager');
insert into bom_master (label,product)values(4,'Haywards');
insert into bom_master (label,product)values(3,'Kingfisher');
insert into bom_master (label,product)values(4,'Feni');
And this is the select statement output.
![/forum/fa/13261/0/](/forum/fa/13261/0/)
As you can see the lable column which depicts the label of each product. The immediate next label is the child of the previous label.
just for here 2 is the child of 1, next 2 is the child of same 1, then 3 is the child of 2 (the one that is immediate predecessor of 3). then 4 is the child of 3, next 4 is the child of same 3, then 3 is the child of the 2 with Miller as the product. And this follows.
The output should look like this
![/forum/fa/13262/0/](/forum/fa/13262/0/)
Hope this is clear enough. I need a select statement which achieve this
Thanks in Advance.
Chinmay
-
Attachment: Select.JPG
(Size: 14.43KB, Downloaded 6508 times)
-
Attachment: output.JPG
(Size: 24.43KB, Downloaded 6501 times)
[Updated on: Tue, 13 September 2016 12:42] Report message to a moderator
|
|
|
|
Re: select statement as per the columns label [message #655824 is a reply to message #655821] |
Tue, 13 September 2016 19:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you are using an Oracle database, then the following is one method:
SCOTT@orcl_12.1.0.2.0> create table bom_master
2 (ID int GENERATED ALWAYS AS IDENTITY NOT NULL,
3 label int,
4 product varchar(20),
5 PRIMARY KEY (ID));
Table created.
SCOTT@orcl_12.1.0.2.0> insert into bom_master (label,product)values(1,'Budwieser');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into bom_master (label,product)values(2,'Heineken');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into bom_master (label,product)values(2,'Miller');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into bom_master (label,product)values(3,'Castle lite');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into bom_master (label,product)values(4,'Castle lager');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into bom_master (label,product)values(4,'Haywards');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into bom_master (label,product)values(3,'Kingfisher');
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into bom_master (label,product)values(4,'Feni');
1 row created.
SCOTT@orcl_12.1.0.2.0> select * from bom_master order by id
2 /
ID LABEL PRODUCT
---------- ---------- --------------------
1 1 Budwieser
2 2 Heineken
3 2 Miller
4 3 Castle lite
5 4 Castle lager
6 4 Haywards
7 3 Kingfisher
8 4 Feni
8 rows selected.
SCOTT@orcl_12.1.0.2.0> select label_parent, label_child, product_parent, product_child
2 from (select t1.label label_parent, t2.label label_child,
3 t1.product product_parent, t2.product product_child,
4 t2.id id_child,
5 row_number () over (partition by t2.id order by t1.id desc) as rn
6 from bom_master t1 join bom_master t2
7 on (t1.label = t2.label - 1 and t1.id < t2.id)) t4
8 where rn = 1
9 order by id_child
10 /
LABEL_PARENT LABEL_CHILD PRODUCT_PARENT PRODUCT_CHILD
------------ ----------- -------------------- --------------------
1 2 Budwieser Heineken
1 2 Budwieser Miller
2 3 Miller Castle lite
3 4 Castle lite Castle lager
3 4 Castle lite Haywards
2 3 Miller Kingfisher
3 4 Kingfisher Feni
7 rows selected.
|
|
|
Re: select statement as per the columns label [message #655825 is a reply to message #655821] |
Tue, 13 September 2016 19:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you are using a MySQL database, then the following is one method:
mysql> use mysql;
Database changed
mysql> create table bom_master (ID int NOT NULL AUTO_INCREMENT,label int, product varchar(20),PRIMARY KEY (ID));
Query OK, 0 rows affected (0.24 sec)
mysql> insert into bom_master (label,product)values(1,'Budwieser');
Query OK, 1 row affected (0.04 sec)
mysql> insert into bom_master (label,product)values(2,'Heineken');
Query OK, 1 row affected (0.03 sec)
mysql> insert into bom_master (label,product)values(2,'Miller');
Query OK, 1 row affected (0.05 sec)
mysql> insert into bom_master (label,product)values(3,'Castle lite');
Query OK, 1 row affected (0.06 sec)
mysql> insert into bom_master (label,product)values(4,'Castle lager');
Query OK, 1 row affected (0.06 sec)
mysql> insert into bom_master (label,product)values(4,'Haywards');
Query OK, 1 row affected (0.03 sec)
mysql> insert into bom_master (label,product)values(3,'Kingfisher');
Query OK, 1 row affected (0.03 sec)
mysql> insert into bom_master (label,product)values(4,'Feni');
Query OK, 1 row affected (0.03 sec)
mysql> select * from bom_master;
+----+-------+--------------+
| ID | label | product |
+----+-------+--------------+
| 1 | 1 | Budwieser |
| 2 | 2 | Heineken |
| 3 | 2 | Miller |
| 4 | 3 | Castle lite |
| 5 | 4 | Castle lager |
| 6 | 4 | Haywards |
| 7 | 3 | Kingfisher |
| 8 | 4 | Feni |
+----+-------+--------------+
8 rows in set (0.00 sec)
mysql> select label_parent, label_child, product_parent, product_child
-> from (select t3.*,
-> @row_num := IF(@prev_value=t3.id_child,@row_num+1,1) as rn,
-> @prev_value := t3.id_child
-> from (select t1.label label_parent, t2.label label_child,
-> t1.product product_parent, t2.product product_child,
-> t1.id id_parent, t2.id id_child
-> from bom_master t1 join bom_master t2
-> on (t1.label = t2.label - 1 and t1.id < t2.id)) t3,
-> (select @row_num := 1) x,
-> (select @prev_value := '') y
-> order by t3.id_child, t3.id_parent desc) t4
-> where rn = 1
-> order by id_child;
+--------------+-------------+----------------+---------------+
| label_parent | label_child | product_parent | product_child |
+--------------+-------------+----------------+---------------+
| 1 | 2 | Budwieser | Heineken |
| 1 | 2 | Budwieser | Miller |
| 2 | 3 | Miller | Castle lite |
| 3 | 4 | Castle lite | Castle lager |
| 3 | 4 | Castle lite | Haywards |
| 2 | 3 | Miller | Kingfisher |
| 3 | 4 | Kingfisher | Feni |
+--------------+-------------+----------------+---------------+
7 rows in set (0.01 sec)
|
|
|
|
|
|
|
|
|
|
|
Re: select statement as per the columns label [message #656989 is a reply to message #656981] |
Tue, 25 October 2016 03:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/f46e4c8e7acca28da6041a92fbba1f16?s=64&d=mm&r=g) |
chinmay89
Messages: 11 Registered: August 2016
|
Junior Member |
|
|
Hello,
Can we run parallel select statement(s) with row-num specified 0-5000, 5001-10000 and so on.
because for 5000 records it is taking 5 minutes to fetch all records.
And another question there is order by id_child is it required ??
The fetch is same without the order by.
The second way can be optimization of the select statement we are using.
[Updated on: Tue, 25 October 2016 10:03] Report message to a moderator
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 04:45:42 CDT 2024
|