I've a table as below:
ColA ColB ColC
````` `````` ``````
1 Steve Rodgers
2 Tony Stark
3 Steve Jobs
Resultant table should look like this:
ColA ColB ColC
````` `````` ``````
2 Tony Stark
Rows with same value in ColB
should be removed. How can I delete/exclude such rows?
6条答案
按热度按时间nfg76nw01#
You can use a similar logic:
wwodge7n2#
With NOT EXISTS:
emeijp433#
I just want to note that you can do this with aggregation:
If the count is
1
, then themin()
brings back the values in that row.umuewwlo4#
If you have a covering index with leading column
ColB
you could also useLAG
/LEAD
. This can scan the index in order (without needing a sort) and retain rows whereColB
is not the same as either of its neighboursl3zydbqr5#
You can also use
Returns:
Live Demo
mzmfm0qo6#
You could use
COUNT(*) OVER (PARTITION BY …)
to count the similar values in a column and then filter by that count, e.g.: