Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Issue with getting unwated duplicate records in SQL query

Posted on 2013-12-01
3
Medium Priority
?
484 Views
Last Modified: 2013-12-02
I have a database of baseball stats, one record per player per year, and I want to generate a list of players that meet certain criteria two years in a row.  But I'm running into issues with players getting doubled up when they meet the criteria for more than two years.

The core structure looks something like this:

select PLAYER
from
TABLE a,
TABLE b
where a.YEAR = b.YEAR+1

But when a player meets the criteria for two pairs of years, like this...

Player A | 2011 | 2012
Player A | 2012 | 2013

... you wind up doubling him up.  In this instance, his 2012 season would get counted twice.  I'd want the Player to be on my list as such:

Player A | 2011
Player A | 2012
Player A | 2013

...but right now I'm getting:

Player A | 2011
Player A | 2012
Player A | 2012
Player A | 2013

Can anyone provide a solution for only including that middle year once?
0
Comment
Question by:dresdena1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 43

Accepted Solution

by:
Rob earned 1000 total points
ID: 39688934
you should use either use the GROUP BY or DISTINCT keyword

ie

SELECT DISTINCT PLAYER
TABLE a,
TABLE b
where a.YEAR = b.YEAR+1
0
 

Author Closing Comment

by:dresdena1
ID: 39689293
Not exactly what I was looking for, but led me in the right direction.
0
 
LVL 43

Expert Comment

by:Rob
ID: 39689539
No problem. Can you post how you eventually did it?
As for the grading, this should probably be a B.
I am thinking you may not be clear on EE grading guidelines for Questions.

http://www.experts-exchange.com/help.jsp?hi=400#hs=29&hi=403

Grading Guidelines

Grading at Experts Exchange is not like school. It's more like the  "10-point Must" system in professional boxing; in other words, an answer  is worth an A, unless it doesn't resolve your issue. If it requires you  to do a little more research, or figure out one more piece of code,  then it's worth a B. If you think it's not worth a B, the custom is to  offer the Experts an opportunity to earn a better grade.

If this answer was the answer to your question, the proper grade to give, even if the answer is not liked, is an A.  To change the grade, you can look at the directions here:

http://www.experts-exchange.com/help.jsp?hi=400#hs=29&hi=404

It says:

If you feel you have graded a solution incorrectly, click the Request  Attention button and the Moderators will change it for you. If another  member disputes the grade you have given, you will be asked to explain  your reasoning; please respond to those requests.

I always think it is better to explain to the Author the grading system before I take a Question to the Moderators.  More often than not, Authors don't really understand how EE grades are supposed to work.

Rob
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question