Remove multiple records from a table

Question

I am working with canteen type of database system and trying to develop stored procedure.

empcode  time            date                  item
------------------------------------------------------------- 
005153  08:45:03    05/10/2012 12:00:00 AM    BREAKFAST  ****
005153  08:45:04    05/10/2012 12:00:00 AM    BREAKFAST  ****
005153  13:48:47    05/10/2012 12:00:00 AM    LUNCH
005153  17:40:33    05/10/2012 12:00:00 AM    BREAKFAST

The marked records are the area of concern. I want to select only one record out of it. This should be done with respect to the time slots. There are several employees and each having different time slot for breakfast. The selected employee have two slots for breakfast. Therefore it should pick one from one time slot one from another.

The output should be something like this.

empcode  time            date                  item
------------------------------------------------------------- 
005153  08:45:03    05/10/2012 12:00:00 AM    BREAKFAST  
005153  13:48:47    05/10/2012 12:00:00 AM    LUNCH
005153  17:40:33    05/10/2012 12:00:00 AM    BREAKFAST

Do you want to remove duplicate data? Or just want to selected non-duplicated data? And what have you tried? — Himanshu, Mar 29 '13 at 06:26
The title seems to conflict with the question: when you say "remove", do you mean "delete the rows from the table" or "eliminate them from the output"? — Bohemian, Mar 29 '13 at 06:29

praveen · Answer 1 · 2013-03-29T06:39:09.817

1

Try this :-

With cte as 
(
Select *,
row_number() over (partition by datepart(minute,[time]),[date] order by empcode ) rn
from Employee
)
Select * from cte where rn=1 order by [time]

Demo in SQL FIDDLE

edited Mar 29 '13 at 06:39

answered Mar 29 '13 at 06:27

praveen

12,083
1
41
49

score 0 · Answer 2 · answered Mar 29 '13 at 07:01

SQL FIDDLE DEMO

Basically you need to find possible duplicated record and select one of them. You can use rank over and in your case, it should partition by [date] and [item] order by time, if you would like to select the earlier record of dup as output, then use default ascending, or else, use DESC.


WITH cte 
AS
(
   SELECT *,
          rank() over (partition BY Date,Item ORDER BY time ) rn
   FROM Employee
)
SELECT [empcode],[time],[date],[item]
FROM cte WHERE rn=1
ORDER BY [date],[time]

Remove multiple records from a table

2 Answers2