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
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/

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/

Hope this is clear enough. I need a select statement which achieve this
Thanks in Advance.
Chinmay
  • Attachment: Select.JPG
    (Size: 14.43KB, Downloaded 5564 times)
  • Attachment: output.JPG
    (Size: 24.43KB, Downloaded 5572 times)

[Updated on: Tue, 13 September 2016 12:42]

Report message to a moderator

Re: select statement as per the columns label [message #655823 is a reply to message #655821] Tue, 13 September 2016 17:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You have posted this in the MySQL sub-forum, yet have indicated that you are using Oracle 11g.

Please copy and paste each of the following lines onto your system, run them, then copy and paste the results as a reply here.

select banner from v$version;

select version();

[Updated on: Tue, 13 September 2016 17:17]

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 messageGo to next message
Barbara Boehmer
Messages: 9077
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 messageGo to next message
Barbara Boehmer
Messages: 9077
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 #656937 is a reply to message #655825] Mon, 24 October 2016 07:01 Go to previous messageGo to next message
chinmay89
Messages: 11
Registered: August 2016
Junior Member
Hi Barbara,

Hope you are doing well.

The Query works well for small set of records(5,000 records), but for huge data(say 50000) it is taking more than one hour.
Is there any ways I can improve the performance. ??

Thanks in Advance.

[Updated on: Mon, 24 October 2016 07:27]

Report message to a moderator

Re: select statement as per the columns label [message #656941 is a reply to message #656937] Mon, 24 October 2016 08:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
posy full results from SQL below

SELECT * FROM V$VERSION;
Re: select statement as per the columns label [message #656943 is a reply to message #656941] Mon, 24 October 2016 08:37 Go to previous messageGo to next message
chinmay89
Messages: 11
Registered: August 2016
Junior Member
I did not get you @blackswan Sad
Re: select statement as per the columns label [message #656944 is a reply to message #656943] Mon, 24 October 2016 08:56 Go to previous messageGo to next message
chinmay89
Messages: 11
Registered: August 2016
Junior Member
Or can i split according to rownum count.... ??
run select statement for rows > 1 and rows <= 1000.
then rows > 1000 and rows <= 2000. and so on ???

we can use the column id for this if we cant use rownum .
Re: select statement as per the columns label [message #656961 is a reply to message #656937] Mon, 24 October 2016 12:55 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
We need to be positive of what database and what version you are using, before we can make recommendations. Therefore, as previously requested, you need to type or copy the following commands into whatever you are running your queries from, then copy and paste the results that the following commands produce onto the forum here.

select banner from v$version;

select version();
Re: select statement as per the columns label [message #656963 is a reply to message #656961] Mon, 24 October 2016 12:57 Go to previous messageGo to next message
chinmay89
Messages: 11
Registered: August 2016
Junior Member
'5.6.22-log'

This is the outcome. I am using Mysql
Re: select statement as per the columns label [message #656972 is a reply to message #656963] Mon, 24 October 2016 14:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Try creating an index:

create index bom_master_label_id_idx on bom_master (label, id);

Then re-run the query.
Re: select statement as per the columns label [message #656981 is a reply to message #656972] Tue, 25 October 2016 01:37 Go to previous messageGo to next message
chinmay89
Messages: 11
Registered: August 2016
Junior Member
I tried using the index as per your suggestion , but the problem with the performance still persist. Sad
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 messageGo to next message
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

Re: select statement as per the columns label [message #657046 is a reply to message #656989] Wed, 26 October 2016 13:35 Go to previous messageGo to next message
chinmay89
Messages: 11
Registered: August 2016
Junior Member
Any suggestion how to optimize the performance. Still struck with the issue Sad
Re: select statement as per the columns label [message #657047 is a reply to message #657046] Wed, 26 October 2016 14:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Sorry, but I don't have any other ideas. Although I am experienced with Oracle, I am new to MySQL and a lot of things that apply to one do not apply to the other. Usually an index speeds things up, but you may need to experiment with which columns should be in the index in what order. Usually, indexes need to be created on columns that are used in join or filter conditions. If your result set only contains a few columns and you can create one index on all of the columns used to obtain the information, then the entire result may be obtainable by accessing just the index without accessing the tables. You can use EXPLAIN after creating indexes to analyze the query to see what indexes it might be able to use. As to parallel, in MySQL, I don't know. You could search the documentation or internet for that. You might want to analyze your design and see if things could be designed better. Perhaps others will have some better ideas.
Re: select statement as per the columns label [message #657048 is a reply to message #657047] Wed, 26 October 2016 15:24 Go to previous message
chinmay89
Messages: 11
Registered: August 2016
Junior Member
Thanks a lot for the help @Barbara

One more question . can we split the query into two instead of writing sub queries and insert it into a staging table in between and then write logic to fetch from that table . ??

[Updated on: Thu, 27 October 2016 11:53]

Report message to a moderator

Previous Topic: Difference between DATETIME and TIMESTANP
Next Topic: MySQL text book
Goto Forum:
  


Current Time: Thu Mar 28 07:33:00 CDT 2024