Need SQL to delete specific rows 1

[merlynsdad]

I'm having a(nother) senior moment. I have 4 records dated 11/2/2011, each with a different value for TotalIdleDay. I need to delete the 3 records where TotalIdleDay is not the greatest. For the life of me I can't come up with the SQL stmt to do this. Help!

If the square peg won't fit in the round hole, sand off the corners.

 

[kjv1611]

hmm.. are you wanting the SQL statement to run in VBA, or is this to be run on SQL Server, or something else? This is the VBA forum.

Here's the MS SQL Forum:
forum183

But I think what you might need to do is have one subquery that looks for the MAX date for each ID, and then have your main/delete query use the subquery as the critiera ... so "not in" or a LEFT JOIN with "is null" perhaps...



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Alrighty... for the SQL... here's a sample that hopefully will get you going - whether you want to run it in SQL or as a SQL string in VBA:

--Drop the Temp table if it exists
IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL BEGIN DROP TABLE #t END;

--create test table
CREATE TABLE #t ([ID] [int] ,[Date] [date])

--Fill the test table with values
INSERT INTO #t
		SELECT	1,'1/1/2011'
UNION	SELECT	1,'2/1/2011'
UNION	SELECT	1,'3/1/2011'
UNION	SELECT	1,'1/15/2011'
UNION	SELECT	2,'1/1/2011'
UNION	SELECT	2,'4/1/2011'
UNION	SELECT	2,'5/5/2011'
UNION	SELECT	2,'8/2/2011'
UNION	SELECT	3,'1/10/2011'
UNION	SELECT	3,'6/1/2011'
UNION	SELECT	3,'7/12/2011'
UNION	SELECT	3,'3/14/2011'
UNION	SELECT	3,'10/1/2011'
UNION	SELECT	4,'1/1/2011'
UNION	SELECT	4,'3/1/2011'
UNION	SELECT	4,'4/15/2011'
UNION	SELECT	4,'11/20/2011'
UNION	SELECT	4,'1/15/2011'
UNION	SELECT	4,'4/1/2011'
UNION	SELECT	5,'8/1/2011'
UNION	SELECT	5,'10/1/2011'
UNION	SELECT	5,'2/3/2011'
UNION	SELECT	5,'5/13/2011'
UNION	SELECT	5,'8/19/2011'

--view the "max" records
SELECT	[ID] ,MAX([DATE]) AS MaxDate
FROM	#t
GROUP BY [ID]

--View the "non-max" records
SELECT	t.[ID] ,t.[DATE]
FROM	#t t
LEFT JOIN (
	SELECT	[ID] ,MAX([DATE]) AS MaxDate
	FROM	#t
	GROUP BY [ID]
	) x
	ON t.ID = x.ID
	AND t.Date = x.MaxDate
WHERE x.ID IS NULL

--Delete the "non-max" records
DELETE #t
FROM	#t t
LEFT JOIN (
	SELECT	[ID] ,MAX([DATE]) AS MaxDate
	FROM	#t
	GROUP BY [ID]
	) x
	ON t.ID = x.ID
	AND t.Date = x.MaxDate
WHERE x.ID IS NULL
--19 records

--Remaining records
SELECT * FROM #t
--ID	Date
--1	2011-03-01
--2	2011-08-02
--3	2011-10-01
--4	2011-11-20
--5	2011-10-01

Copy that, put it in your SQL Window, and give it a go... hopefully that'll help you with the idea. There may be a better way, but it seems to work in my testing.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

[tt]tblMyTable
MyDate TotalIdleDay
11/2/2011 1
11/2/2011 2[blue]
11/2/2011 5[/blue]
11/2/2011 4[/tt]

To get the record you want to keep you would do:
[tt][blue]
SELECT MAX(TotalIdleDay)
FROM tblMyTable
WHERE MyDate = #11/2/2011#[/blue][/tt]

So to delete the rest, you could do:
[tt]
DELETE FROM tblMyTable
WHERE MyDate = #11/2/2011#
AND TotalIdleDay <> ([blue]
SELECT MAX(TotalIdleDay)
FROM tblMyTable
WHERE MyDate = #11/2/2011#[/blue]
)[/tt]

Have fun.

---- Andy

 

[merlynsdad]

Thanks. I'm actually dealing with more than one date, some of which have only one record, some of which have multiple records. On the dates with multiple records, I'm deleting all but the one with the greatest TotalIdleDay value. What is the SQL for "where the date is not unique"?

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

Are you talking about the situation like:
[tt]tblMyTable
MyDate TotalIdleDay
11/2/2011 1
11/3/2011 2
11/4/2011 5
11/9/2011 4
[/tt]
Then how do you know how to pick the MAX of TotalIdleDay?

Do you have any other Field, key, Column to detemine that?


Have fun.

---- Andy

 

[merlynsdad]

MyDate TotalIdleDay
11/1/2011 1
11/2/2011 3
11/2/2011 4
11/2/2011 5
11/3/2011 2
11/4/2011 4

11/1, 3 & 4 will take care of themselves as there is only 1 record. For 11/2 I need the code to pick out the 5 and delete the 3 & 4.

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

That was in my very first post from 9 Nov 11 11:57

Have fun.

---- Andy

 

[kjv1611]

Hmm... maybe you need to do this:
1. Have one query (subquery) that looks at the count of the MyDate
2. Another one that gets the Max TotalIdleDay for each MyDate
3. Main query (delete query) that says, basically:
Delete all records where there is only one record for the MyDate (Count in #2 = 1) and delete all but the Max TotalIdleDay for all other MyDates..

Well, I guess you could do it in a couple of queries as well.. not sure which would be the best route.

If I get the time later... aka tomorrow or Friday... I'll take another look... assuming no one else has solved it by then.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

If you want to go down the table and do it for all dates, you may do something like this:

'Set a recordset with all MyDate
strSQL = "SELECT DISTINCT MyDate FROM MyTable"
rst.Open strSQL

Do While rst.EOF = False
  strSQL = "DELETE FROM tblMyTable " _
      & " WHERE MyDate = #" & rst!MyDate & "# " _
      & " AND TotalIdleDay <> " _
      & " (SELECT MAX(TotalIdleDay) " _
      & " FROM tblMyTable " _
      & " WHERE MyDate = #" & rst!MyDate & "#) "
  Execute strSQL
    rst.MoveNext
Loop

And don't worry about the records with just one entry per MyDate, nothing will happen to them.

Have fun.

---- Andy

 

[merlynsdad]

Before the Do While Loop, do I need to run the SELECT MAX stmt by itself so the compound SQL stmt knows what was selected?

If the square peg won't fit in the round hole, sand off the corners.

 

[Andrzejek]

No, you don't.
Just put some breakes in the code, go thru the code line-by-line, you will see what's going on in there, check your table in the data base to make sure you get what you want after few first Deletes, and you are set.

Have fun.

---- Andy

 

[merlynsdad]

What I ended up doing was making a query from the table.

Set qdf = db.CreateQueryDef("qryTotalDayPerSEID")
        strSQL = "SELECT tblReady_Dur.SEID, tblReady_Dur.date_stamp, Sum(tblReady_Dur.Signon_Dur) AS SumOfSignon_Dur, Sum(tblReady_Dur.Idle_Dur) AS SumOfIdle_Dur, Sum(tblReady_Dur.Ready_Dur) AS SumOfReady_Dur " _
            & "FROM tblReady_Dur " _
            & "GROUP BY tblReady_Dur.SEID, tblReady_Dur.date_stamp " _
            & "ORDER BY tblReady_Dur.SEID DESC , tblReady_Dur.date_stamp;"
         db.Execute strSQL

If the square peg won't fit in the round hole, sand off the corners.