CODEWITHSUNDEEP
sqlsql-servert-sqlgreatest-n-per-groupwindow-functions
Teknas
Teknas

Reputation: 559

SQL - Select One less record from total

Following is the data and I want to output having one less record of each value.

for e.g There are

Total A are 4,so output shall have 3 A

Total B is 1, so output should not have any B

Total C are 2,so output should have One C

Total D is 1, so output should not have any D

Total F is 3, so output should have 2 F

+------+
| Data |
+------+
| A    |
| A    |
| A    |
| A    |
| B    |
| C    |
| C    |
| D    |
| E    |
| F    |
| F    |
| F    |
+------+

Output

+------+
| Data |
+------+
| A    |
| A    |
| A    |
| C    |
| F    |
| F    |
+------+

Upvotes: 0

Views: 263

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521419

Using analytic functions:

WITH cte AS (
    SELECT Data, COUNT(*) OVER (PARTITION BY Data) cnt,
        ROW_NUMBER() OVER (PARTITION BY Data ORDER BY (SELECT NULL)) rn
    FROM yourTable
)

SELECT Data
FROM cte
WHERE rn < cnt;

In plain English terms, the above approach retains any record whose row number (based on some arbitrary ordering) is less than the total number of records in each Data group. This means that one record per group would be excluded in the result set.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use row_number() :

select t.data
from (select t.*, row_number() over (partition by data order by data) as seq
      from table t
     ) t
where seq > 1;

Upvotes: 1

GMB
GMB

Reputation: 222482

You can use row_number(); it is far better if you have an ordering column (I assumed id):

select data
from (select data, row_number() over(partition by data order by id) rn from mytable) t
where rn > 1

If you don't have an ordering column, then you can just order by data.

Upvotes: 3

Related Questions