CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user3754804
user3754804

Reputation: 33

How to delete all rows from table except the min & max value of column by date

I have a table Visitors with the following columns

ID, Visitors, RegDate (date & time)

Rows: There are 4, 6, 8, 20, 11, 31, 43 Visitors with the same date (July 18) but not the same time.

Another Rows: There are 1, 5, 10, 4, 13, 15, 18, 11, 23 Visitors with the same date (July 19) but not the same time.

Something like this

Id  RegDate
4   2015-07-18 11:11:00.000
6   2015-07-18 11:11:01.000
8   2015-07-18 11:11:02.000
20  2015-07-18 11:11:03.000
11  2015-07-18 11:11:04.000
31  2015-07-18 11:11:05.000
43  2015-07-18 11:11:06.000
1   2015-07-19 11:11:00.000
10  2015-07-19 11:11:01.000
4   2015-07-19 11:11:02.000
13  2015-07-19 11:11:03.000
15  2015-07-19 11:11:04.000
18  2015-07-19 11:11:05.000
11  2015-07-19 11:11:06.000
23  2015-07-19 11:11:07.000

Here is my Query that shows the Minimum and Maximum including the date.

SELECT MIN(Visitors), MAX(Visitors), cast(RegDate as date) AS DATE
FROM Visitor GROUP BY cast(RegDate as date)
ORDER BY DATE

I want to delete all rows from the column Visitors except its Minimum and Maximum values by RegDate.

Upvotes: 0

Views: 4077

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use ROW_NUMBER to find the Visitor with the earliest and latest RegDate:

WITH Cte AS(
    SELECT *,
        RN_ASC = ROW_NUMBER() OVER(PARTITION BY CAST(RegDate AS DATE) ORDER BY RegDate ASC),
        RN_DESC = ROW_NUMBER() OVER(PARTITION BY CAST(RegDate AS DATE) ORDER BY RegDate DESC)
    FROM Visitor
)
DELETE FROM Cte WHERE RN_ASC > 1 AND RN_DESC > 1

SQLFiddle

Upvotes: 1

JohnS
JohnS

Reputation: 2052

The following works for your data. It uses your GROUP BY query as a sub-query to a DELETE:

DELETE V FROM  
@Visitor AS v
JOIN 
(
  SELECT
    RegDate,
    MIN(Visitors) MinVisitors,
    MAX(Visitors) MaxVisitors
  FROM
    @Visitor AS V
  GROUP BY
    RegDate
) T  ON 
  v.RegDate = T.RegDate 
  AND V.Visitors <> T.MinVisitors 
  AND V.Visitors <> T.MaxVisitors

Upvotes: 1

Related Questions