Group rows that have values with the condition : value1=value2 1-CodePudding

I have these two columns in an oracle database:

SHIFTIME_NO| UserId|
------- ----------
240016     | 24    |
240017     | 24    |
240016     | 24    |
240017     | 24    |
240016     | 24    |
240016     | 24    |
240021     | 24    |
240022     | 24    |
240022     | 24    |

My goal is to group each two values that have the condition(value1=value2 1) into one value(min(value1,value2)). For this example, I would like to have something like this:

 SHIFTIME_NO| UserId|
    ------- ----------
    240016     | 24    |
    240021     | 24    |

Your help with be much appreciated.

CodePudding user response：

Assuming, same userID for same shiftime_id, following query can be used -

select shiftime_no, userid from 
(
select shiftime_no, UserID, row_number() over 
(order by shiftime_no) rn from tab1 group by shiftime_no, UserID
) where mod(rn,2)<>0

fiddle here.

CodePudding user response：

To simply get the exact outcome you have described, you can just use DISTINCT and JOIN the current entry with the entry having shiftime_no 1:

SELECT DISTINCT y.shiftime_no, y.userid
FROM yourtable y JOIN yourtable y2
ON y2.shiftime_no = y.shiftime_no  1;

Since you asked the question in that way, I assume you are sure you really want to get exactly this result. If not, you will to have further conditions or to add some selects with union to add as example the lowest and the highest number of your table. Or to group by userid etc. Please ask the question more precise if you require further assistance.