I have the following table:
CREATE TABLE [TABLE_1]
(
ID decimal(28,6) NULL,
S_DATE datetime NULL,
NR decimal(28,6) NULL,
);
INSERT INTO TABLE_1 (ID, S_DATE, NR)
VALUES (1, '2020-01-01', 1),
(1, '2020-01-01', 2),
(1, '2020-05-02', 3);
A simple query:
SELECT *
FROM Table_1 t1
Returns:
+----+----------------------+----+
| ID | S_DATE | NR |
+----+----------------------+----+
| 1 | 2020-01-01T00:00:00Z | 1 |
| 1 | 2020-01-01T00:00:00Z | 2 |
| 1 | 2020-05-02T00:00:00Z | 3 |
+----+----------------------+----+
But I want to filter out duplicates that share the same ID and S_DATE, like this:
+----+----------------------+----+
| ID | S_DATE | NR |
+----+----------------------+----+
| 1 | 2020-01-01T00:00:00Z | 1 |
| 1 | 2020-05-02T00:00:00Z | 3 |
+----+----------------------+----+
SQL FIDDLE: LINK
You can use a common table expression which applies a row number to each subsequent row with the same ID/S_DATE values, then filter on that row number.
;WITH src AS
(
SELECT ID, S_DATE, NR, rn = ROW_NUMBER() OVER
(PARTITION BY ID, S_DATE ORDER BY NR)
FROM dbo.TABLE_1
)
SELECT ID, S_DATE, NR
FROM src
WHERE rn = 1;
You could also do this, which is more straightforward, but it is much more complicated to do when you have many columns to group by (or if you want more complicated logic for which NR to include):
SELECT ID, S_DATE, NR = MIN(NR)
FROM dbo.TABLE_1
GROUP BY ID, S_DATE;
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加