June 11, 2014 at 9:06 am
I need to write a query to select some fields from a table, where one of the fields is dublicated. Data is disjointed and one of the entries has some fields which are correct, and the other entry has other fields which are correct. So i need to see all of the fields, to identify which fields i need to transfer from one entry to another (Sounds complicated writing it down)
select a.fullname, a.attrib3, count(*) num_occurances
from userdb a
where a.companyname like 'carpet%'
group by a.fullname, a.attrib3
having count(*) > 1
The duplication is in the fullname field, but instead of just selecting the line once, i need to see both entries, with a count as the above query shows. I think I am going about this the wrong way, but any help would be appreciated
June 11, 2014 at 9:12 am
SQLSteve (6/11/2014)
I need to write a query to select some fields from a table, where one of the fields is dublicated. Data is disjointed and one of the entries has some fields which are correct, and the other entry has other fields which are correct. So i need to see all of the fields, to identify which fields i need to transfer from one entry to another (Sounds complicated writing it down)select a.fullname, a.attrib3, count(*) num_occurances
from userdb a
where a.companyname like 'carpet%'
group by a.fullname, a.attrib3
having count(*) > 1
The duplication is in the fullname field, but instead of just selecting the line once, i need to see both entries, with a count as the above query shows. I think I am going about this the wrong way, but any help would be appreciated
We can help but you need to explain more clearly what the issue is. You have some duplicate fullnames and you want to see those. What about your query does not do that?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2014 at 9:12 am
SELECT fullname, attrib3, num_occurrences
FROM (
SELECT
fullname,
attrib3,
num_occurrences = COUNT(*) OVER(PARTITION BY fullname)
FROM userdb
WHERE companyname LIKE 'carpet%'
) d
WHERE num_occurrences > 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 9:25 am
Chris M that is exactly what I was after! Thank you
June 11, 2014 at 9:27 am
Sean, the query only posted 1 entry back to me from the duplicate. Chris' posted both enrties, so I can now see all of the entries rather than just 1 of the duplicated.
Thanks for reading though. Its hard to explain that kind of thing in a few lines!
June 11, 2014 at 9:32 am
SQLSteve (6/11/2014)
Sean, the query only posted 1 entry back to me from the duplicate. Chris' posted both enrties, so I can now see all of the entries rather than just 1 of the duplicated.Thanks for reading though. Its hard to explain that kind of thing in a few lines!
It is tough. That is why we always suggest posting ddl and sample data. 😉
Glad Chris was able to sort it out for you. He has an uncanny ability to see this stuff with not much detail posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 11, 2014 at 9:48 am
Sean Lange (6/11/2014)
SQLSteve (6/11/2014)
Sean, the query only posted 1 entry back to me from the duplicate. Chris' posted both enrties, so I can now see all of the entries rather than just 1 of the duplicated.Thanks for reading though. Its hard to explain that kind of thing in a few lines!
It is tough. That is why we always suggest posting ddl and sample data. 😉
Glad Chris was able to sort it out for you. He has an uncanny ability to see this stuff with not much detail posted.
Years and years of working with really cr@p specs, Sean 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 11, 2014 at 9:55 am
ChrisM@Work (6/11/2014)
Sean Lange (6/11/2014)
SQLSteve (6/11/2014)
Sean, the query only posted 1 entry back to me from the duplicate. Chris' posted both enrties, so I can now see all of the entries rather than just 1 of the duplicated.Thanks for reading though. Its hard to explain that kind of thing in a few lines!
It is tough. That is why we always suggest posting ddl and sample data. 😉
Glad Chris was able to sort it out for you. He has an uncanny ability to see this stuff with not much detail posted.
Years and years of working with really cr@p specs, Sean 😉
Ahh that's the difference. You get cr@p specs...I get none. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply