.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

SQL Help

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
 
Hi I am stuck and need help with this SQL. I have a table with rows. The table has no keys or unique columns. I need to write a query to: Find all rows where Name and Job on the rows are the same but the Rate value is different. Example: John D, painting, 25 John D, painting, 25 John D, painting, 25 John D, painting, 25 John D, painting, 30 John D, painting, 30 John D, painting, 40 Fred H, lumber, 15 Fred H, lumber, 20 Fred H, lumber, 20 Fred H, lumber, 20 Swarup P, Welding, 15 Swarup P, Welding, 15 Pamela A, Star,1000 The result would be: John D, painting 25 John D, painting 30 John D, painting 40 Fred H, lumber, 15 Fred H, lumber, 20 Attempt 1: Select Name, Job, Rate From T WHERE NAME + JOB IN ( Select NAME + JOB From T Group by NAME + JOB Having COUNT(*) > 1 ) Group by Name, job, rate ORDER BY Name, job Result Fred H lumber 15.00 Fred H lumber 20.00 John D painting 25.00 John D painting 30.00 John D painting 40.00 Swarup P Welding 15.00 The last row (swarup P) should not appear because even though it is duplicate, it has the same rate in both rows. Attempt 2 I then tried this: Select distinct t1.name, t1.job, t1.rate from t t1, t t2 where t1.name = t2.name and t1.job=t1.job and t1.rate!=t2.rate The result is: Fred H lumber 15.00 Fred H lumber 20.00 John D painting 25.00 John D painting 30.00 John D painting 40.00 Now, this worked on the limite


View Complete Post


More Related Resource Links

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend