DB gurus: SQL question

[R00k]

My problem is that it won't let me select multiple columns when I use the HAVING clause. This query:

Code: Select all

select ComputerName, UID
	from tbl_PurgeTestData
	group by ComputerName
	having count(ComputerName) > 1

returns this error:

Column 'tbl_PurgeTestData.UID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I have two queries that I believe can give me what I need, I just can't figure out how to make the join work.

This one:

Code: Select all

SELECT *
FROM tbl_PurgeTestData
WHERE DATEDIFF(day, LoginTime, GETDATE()) > 3
ORDER BY LoginTime DESC

Gives me all the records that are older than 3 days.

And this one:

Code: Select all

SELECT ComputerName
FROM tbl_PurgeTestData
GROUP BY ComputerName
HAVING COUNT(ComputerName) > 1
ORDER BY ComputerName

Gives me all the duplicate ComputerName records. I just can't figure out how to join on those and delete.

[R00k]

Okay, I found a way to do it, but it's sloppy and inefficient as hell.

I created a View in the database, called qry_LoginHistoryDuplicates, which returns a list of the ComputerNames which have duplicates, like so:

Code: Select all

SELECT ComputerName
FROM tbl_PurgeTestData
GROUP BY ComputerName
HAVING COUNT(ComputerName) > 1

I then wrote the Delete query with a join to that View to run the actual purge/delete, like so:

Code: Select all

DELETE FROM tbl_PurgeTestData
WHERE UID IN
(
SELECT UID FROM tbl_PurgeTestData as al
INNER JOIN qry_LoginHistoryDuplicates as dup
 on al.ComputerName = dup.ComputerName
WHERE DATEDIFF(day, LoginTime, GETDATE()) > 3
)

I know there's a better way to do it than this, but none of the syntax that I have looked at seems to work. If anyone has an idea of how to make this more efficient -- looking at the queries I've got that actually work -- I'd love some input.

Thanks for the help everyone.

[^misantropia^]

My problem is that it won't let me select multiple columns when I use the HAVING clause.

Why would you want to select multiple columns when you only need the primary key for a DELETE?

Column 'tbl_PurgeTestData.UID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Code: Select all

GROUP BY ComputerName, UID

Solved.