November 28, 2011 at 4:47 pm
I have a need to find a value shared/duplicated across a number of records. There are 3 tables involved:
Table_1 (ID int)
Table_2 (Table_1_ID int, Table_3_ID int, CreatedOn datetime)
Table_3 (ID int)
Pretty straight forward -- Table 2 resolves the many-to-many relationship between tables 1 and 3. So my problem was finding which Table_1_ID occurred in Table_2 on a CreatedOn day.
So what initially popped in my head was something like
SELECT MAX(COUNT(Table_1_ID))
FROM Table_2
WHERE CreatedOn BETWEEN someDay AND someOtherDay
Of course, this doesn't work because the COUNT aggregate function can't appear inside the MAX aggregate function. So I had to first get a table's worth of COUNT data using:
Declare @a table (Table_1_ID int, countTotal int)
INSERT INTO @a
SELECT Table_1_ID, COUNT(Table_1_ID)
FROM Table_2
WHERE CreatedOn BETWEEN someDay AND someOtherDay
And then do this to find the Table_1_ID that is being used the most:
SELECT Table_1_ID
FROM Table_2
WHERE CreatedOn BETWEEN someDay AND someOtherDay
AND COUNT(Table_1_ID) = (SELECT MAX(countTotal) FROM @a)
While this works to eventually find which Table_1_ID is used the most in the date range, it seems like a roundabout way of getting there. Is there a shortcut to this logic that can fit inside a single query without using a temp table?
December 14, 2011 at 1:27 pm
OK, slightly confused. It would help to provide sample data.
Do you want the most common value on a day, or all duplicates? Meaning, if I have this
CREATE TABLE t1 ( id int)
GO
CREATE TABLE t2
( t1_id int
, createdon datetime
)
GO
INSERT t1 SELECT 1
INSERT t1 SELECT 2
INSERT t1 SELECT 3
INSERT t2 SELECT 1, '12/1/2011'
INSERT t2 SELECT 2, '12/1/2011'
INSERT t2 SELECT 2, '12/1/2011'
INSERT t2 SELECT 3, '12/1/2011'
INSERT t2 SELECT 3, '12/1/2011'
INSERT t2 SELECT 3, '12/1/2011'
INSERT t2 SELECT 2, '12/2/2011'
INSERT t2 SELECT 2, '12/2/2011'
INSERT t2 SELECT 3, '12/2/2011'
INSERT t2 SELECT 3, '12/2/2011'
INSERT t2 SELECT 3, '12/2/2011'
GO
SELECT * FROM dbo.t1
SELECT * FROM dbo.t2
go
SELECT t1.ID
, COUNT(t1.id) 'Count of IDs'
FROM dbo.t1
INNER JOIN dbo.t2
ON dbo.t1.id = dbo.t2.t1_id
WHERE t2.createdon = '12/1/2011'
GROUP BY t1.id
go
SELECT t1.ID
, COUNT(t1.id) 'Count of IDs'
FROM dbo.t1
INNER JOIN dbo.t2
ON dbo.t1.id = dbo.t2.t1_id
WHERE t2.createdon = '12/1/2011'
GROUP BY t1.id
HAVING COUNT(t1.id) > 1
go
; WITH MyCTE (MyID, Cnt)
AS
( SELECT t1.ID
, COUNT(t1.id) 'Count of IDs'
FROM dbo.t1
INNER JOIN dbo.t2
ON dbo.t1.id = dbo.t2.t1_id
WHERE t2.createdon = '12/1/2011'
GROUP BY t1.id
)
SELECT TOP 1
MyID, CNT
FROM MyCTE
ORDER BY CNT DESC
go
DROP TABLE dbo.t1
DROP TABLE dbo.t2
GO
My first query returns the count of each ID value for a particular day. If I only want duplicates, I can add a HAVING clause to filter those counts < 1.
If I want the most common, I use a CTE that essentially rolls up my IDs and counts, and then grabs only the top 1.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy