Merge two rows based on common field value

[charan986]

I've a table like below and I want to merge two rows based on the COMMONID

1.    JBID    JOBTYPE                     START_TIME             END_TIME          COMMONID
2.                                2020-03-10T06:30:00   2020-03-10T08:30:00   abc
3.   6398   Medium                                                                abc
4.   5649   Medium                                                                def
5.                                2020-03-10T08:30:00   2020-03-10T10:30:00   def
6.   5649   Medium                                                                ghi
7.                                2020-03-20T08:30:00   2020-03-20T10:30:00   ghi
8.                                2020-03-11T08:30:00   2020-03-11T10:30:00   jkl
9.   6383   Medium                                                               jkl
10.  7070   Medium                                                                mno
11.                                  2020-03-10T08:30:00    2020-03-10T10:30:00   mno
12.  11690  Medium                                                               pqr
13.                                  2020-03-12T06:30:00    2020-03-12T08:30:00   pqr
14.                                  2020-03-19T06:30:00    2020-03-19T08:30:00   stu
15.  6398   Medium                                                                stu
16.  6398   Medium                                                                vwx
17.                                  2020-03-10T06:30:00    2020-03-10T08:30:00   vwx

The resulting table should look like below

1.  JBID    JOBTYPE                                START_TIME             END_TIME          COMMONID
2.  6398    Medium                            2020-03-10T06:30:00   2020-03-10T08:30:00   abc
3.  5649    Medium                          2020-03-10T08:30:00 2020-03-10T10:30:00   def
4.  5649    Medium                          2020-03-20T08:30:00 2020-03-20T10:30:00   ghi
5.  6383    Medium                          2020-03-11T08:30:00 2020-03-11T10:30:00   jkl
6.  7070    Medium                          2020-03-10T08:30:00 2020-03-10T10:30:00   mno
7. 11690    Medium                          2020-03-12T06:30:00 2020-03-12T08:30:00   pqr
8.  6398    Medium                          2020-03-19T06:30:00 2020-03-19T08:30:00   stu
9.  6398    Medium                          2020-03-10T06:30:00 2020-03-10T08:30:00   vwx

How do I achieve this?

[woodcock]

I typically do this:

... | stats list(*) AS * BY COMMONID

Just in case there are some duplicated lines.

[to4kawa]

....
| selfjoin COMMONID

[manjunathmeti · Accepted solution]

hi @charan986

You can do this using stats command:

<base search>| stats first(*) as * by COMMONID

[charan986]

Thanks, it worked 🙂