October 10, 2018 at 2:24 am
October 10, 2018 at 2:42 am
Jon
Use COUNT(*) with a PARTITION BY clause to show the number of rows per NUMBER. If you want to return only those rows with NUMBER greater than 1, you'll need to use a CTE or subquery, since you can't have COUNT(*) in your WHERE clause.
John
October 10, 2018 at 2:48 am
OK, thanks John.
I think that's above my SQL knowledge unfortunately. I'll do it the manual way 🙂
Many thanks
Jon
October 10, 2018 at 7:46 am
j.clay 47557 - Wednesday, October 10, 2018 2:48 AMOK, thanks John.I think that's above my SQL knowledge unfortunately. I'll do it the manual way 🙂
Many thanks
Jon
Take the time to learn something new because you're going to need to do it again. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
October 10, 2018 at 7:49 am
Very true and I will definitely learn. Deadlines are looming this week unfortunately 🙁
October 10, 2018 at 11:13 am
SELECT tn.*
FROM (
SELECT NUMBER
FROM dbo.table_name
GROUP BY NUMBER
HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER JOIN dbo.table_name tn ON tn.NUMBER = NUMBER_DUPS.NUMBER
ORDER BY NUMBER, COURSE
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 10, 2018 at 12:02 pm
ScottPletcher - Wednesday, October 10, 2018 11:13 AM
SELECT tn.*
FROM (
SELECT NUMBER
FROM dbo.table_name
GROUP BY NUMBER
HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER JOIN dbo.table_name tn ON tn.NUMBER = NUMBER_DUPS.NUMBER
ORDER BY NUMBER, COURSE
I suspect the following will perform better, but it's hard to test with no sample data.
WITH Counts AS
(
SELECT *, COUNT(*) OVER(PARTITION BY Number) AS cnt
FROM dbo.table_name
)
SELECT *
FROM Counts
WHERE cnt > 1
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 10, 2018 at 12:56 pm
drew.allen - Wednesday, October 10, 2018 12:02 PMScottPletcher - Wednesday, October 10, 2018 11:13 AM
SELECT tn.*
FROM (
SELECT NUMBER
FROM dbo.table_name
GROUP BY NUMBER
HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER JOIN dbo.table_name tn ON tn.NUMBER = NUMBER_DUPS.NUMBER
ORDER BY NUMBER, COURSEI suspect the following will perform better, but it's hard to test with no sample data.
WITH Counts AS
(
SELECT *, COUNT(*) OVER(PARTITION BY Number) AS cnt
FROM dbo.table_name
)
SELECT *
FROM Counts
WHERE cnt > 1Drew
But then you don't list the first row of every NUMBER, right?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 11, 2018 at 8:18 am
ScottPletcher - Wednesday, October 10, 2018 12:56 PMdrew.allen - Wednesday, October 10, 2018 12:02 PMScottPletcher - Wednesday, October 10, 2018 11:13 AM
SELECT tn.*
FROM (
SELECT NUMBER
FROM dbo.table_name
GROUP BY NUMBER
HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER JOIN dbo.table_name tn ON tn.NUMBER = NUMBER_DUPS.NUMBER
ORDER BY NUMBER, COURSEI suspect the following will perform better, but it's hard to test with no sample data.
WITH Counts AS
(
SELECT *, COUNT(*) OVER(PARTITION BY Number) AS cnt
FROM dbo.table_name
)
SELECT *
FROM Counts
WHERE cnt > 1Drew
But then you don't list the first row of every NUMBER, right?
Again, this is why we ask for expected results. You would simply add a ROW_NUMBER to the CTE and select based on the row number = 1. It will still probably perform faster than the self join.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2018 at 8:26 am
ScottPletcher - Wednesday, October 10, 2018 12:56 PMdrew.allen - Wednesday, October 10, 2018 12:02 PMScottPletcher - Wednesday, October 10, 2018 11:13 AM
SELECT tn.*
FROM (
SELECT NUMBER
FROM dbo.table_name
GROUP BY NUMBER
HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER JOIN dbo.table_name tn ON tn.NUMBER = NUMBER_DUPS.NUMBER
ORDER BY NUMBER, COURSEI suspect the following will perform better, but it's hard to test with no sample data.
WITH Counts AS
(
SELECT *, COUNT(*) OVER(PARTITION BY Number) AS cnt
FROM dbo.table_name
)
SELECT *
FROM Counts
WHERE cnt > 1Drew
But then you don't list the first row of every NUMBER, right?
Sorry, I looked back at the OP and realized that you were asking something else.
Yes, it does include the first row. A frame is only applied to a windowed function if there is an ORDER BY clause. Since there was no ORDER BY clause specified in the windowed function, the COUNT is for the entire partition, so the count will be the same for all records in the partition (including the first). This query will only exclude records where there is exactly one record in the partition.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 11, 2018 at 9:07 am
drew.allen - Thursday, October 11, 2018 8:26 AMScottPletcher - Wednesday, October 10, 2018 12:56 PMdrew.allen - Wednesday, October 10, 2018 12:02 PMScottPletcher - Wednesday, October 10, 2018 11:13 AM
SELECT tn.*
FROM (
SELECT NUMBER
FROM dbo.table_name
GROUP BY NUMBER
HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER JOIN dbo.table_name tn ON tn.NUMBER = NUMBER_DUPS.NUMBER
ORDER BY NUMBER, COURSEI suspect the following will perform better, but it's hard to test with no sample data.
WITH Counts AS
(
SELECT *, COUNT(*) OVER(PARTITION BY Number) AS cnt
FROM dbo.table_name
)
SELECT *
FROM Counts
WHERE cnt > 1Drew
But then you don't list the first row of every NUMBER, right?
Sorry, I looked back at the OP and realized that you were asking something else.
Yes, it does include the first row. A frame is only applied to a windowed function if there is an ORDER BY clause. Since there was no ORDER BY clause specified in the windowed function, the COUNT is for the entire partition, so the count will be the same for all records in the partition (including the first). This query will only exclude records where there is exactly one record in the partition.
Drew
Nice way to do it. I've not seen this method before now.
October 11, 2018 at 9:13 am
Jonathan AC Roberts - Thursday, October 11, 2018 9:07 AMdrew.allen - Thursday, October 11, 2018 8:26 AMScottPletcher - Wednesday, October 10, 2018 12:56 PMdrew.allen - Wednesday, October 10, 2018 12:02 PMScottPletcher - Wednesday, October 10, 2018 11:13 AM
SELECT tn.*
FROM (
SELECT NUMBER
FROM dbo.table_name
GROUP BY NUMBER
HAVING COUNT(*) > 1
) AS NUMBER_DUPS
INNER JOIN dbo.table_name tn ON tn.NUMBER = NUMBER_DUPS.NUMBER
ORDER BY NUMBER, COURSEI suspect the following will perform better, but it's hard to test with no sample data.
WITH Counts AS
(
SELECT *, COUNT(*) OVER(PARTITION BY Number) AS cnt
FROM dbo.table_name
)
SELECT *
FROM Counts
WHERE cnt > 1Drew
But then you don't list the first row of every NUMBER, right?
Sorry, I looked back at the OP and realized that you were asking something else.
Yes, it does include the first row. A frame is only applied to a windowed function if there is an ORDER BY clause. Since there was no ORDER BY clause specified in the windowed function, the COUNT is for the entire partition, so the count will be the same for all records in the partition (including the first). This query will only exclude records where there is exactly one record in the partition.
Drew
Nice way to do it. I've not seen this method before now.
Interesting. And, true, it will still list all rows.
But as to efficiency, doesn't SQL still have to do a separate query to get the COUNT? Does it actually save any I/O?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply