1

I want my code to gather grades-table with students-table with a STUDENT_ID as common factor. With the DISTINCT clause, it removes row duplicates, but I want it to not show the same PERSON_ID more than once, so it will only show each STUDENT_ID once.

It's a huge datafile.

SELECT DISTINCT 
    grades.PERSON_ID, grades.GRADE, 
    students.PERSON_ID, students.ENROLL_PERIOD 
FROM  
    students 
INNER JOIN 
    grades ON students.PERSON_ID = grades.PERSON_ID 
WHERE 
    ENROLL_PERIOD < 132 
ORDER BY 
    students.ENROLL_PERIOD

SAMPLE From before -> STUDENTS:

PERSON_ID   PROGRAM_ID  ENROLL_PERIOD
12401       82          109
12401       4549        112
720921      5921        112
832521      9981        114

SAMPLE From before -> GRADES:

PERSON_ID   GRADE   GRADE_DATE  GRADE_TYPE
2308133     7       16-06-26    p                
2308133     10      18-01-24    p                
2308133     4       16-02-01    p        

SAMPLE from when the 2 tables are combined, it looks like this:

PERSON_ID   GRADE   PERSON_ID   ENROLL_PERIOD
12401       00      12401       109
12401       02      12401       112
720921      00      720921      112
832521      00      832521      114

Here we see, that the "12401" row is there twice, since it's only if the whole row is different It should skip the 2nd "12401" and jump to the next one ( For instance 12491)

PERSON_ID   GRADE   PERSON_ID   ENROLL_PERIOD
12401       00      12401       109
720921      00      720921      112
832521      00      832521      114    
Adem
  • 45
  • 4
  • 2
    Sample data and expected results will really help here. Formatting your code so it's readable would also be desirable. Random line breaks in the middle of an expression don't make it easy to understand, no matter how simple it is. :) – Thom A May 26 '18 at 20:23
  • Hi there! The code pasted in is my full code - i just linebroke it so its not written in 1 line. I just wrote in the headlines in the 2 tables if it helps? :) What more can i do, so you can help me? :-)) – Adem May 26 '18 at 20:27
  • A really nice guy just helped me with it - Is someone (Maybe you Larnu) able to help me now? Should be kinda simple for you guys :)) – Adem May 26 '18 at 20:31
  • 1
    I suggest having a look examples of how to format your SQL, writing it all on one line (and then inserting random line breaks) is not a good way to write SQL. Good use of white space makes it far easier for you, and others, to read (and thus troubleshoot). Sample data is still going to help, you've only posted the column names of your tables, there's no data there. – Thom A May 26 '18 at 20:42
  • 1
    You also need to include expected results (for your sample data when you post it). – Thom A May 26 '18 at 20:44
  • I tried to show what the combined table looks like and what it SHOULD look like. Does it make sense now? :) – Adem May 26 '18 at 20:48
  • Now its not a one-liner, and written very well in my oppinion! :) – Adem May 26 '18 at 20:50
  • 1
    What we need to see is your data before it's combined; not after your attempt. The expected result is good, but we need to see what it looks like before. – Thom A May 26 '18 at 20:51
  • 1
    Also, `12491` isn't in your original sample data, where did it come from? We can't "magic" data. – Thom A May 26 '18 at 20:53
  • Okay, my bad - Sorry for the misunderstandings. Now i've provided you with some samplelines of both tables before i combined them!! :) Once again sorry. – Adem May 26 '18 at 20:58
  • 1
    Ok, so, like I asked above, where does `12491` come from? It's not in your sample data. – Thom A May 26 '18 at 21:01
  • Oh lol - It was just an example of what couldve been (12491 is also on the list somewhere). I can remove it if you want? It was just to show an example of how the list then could look like.. – Adem May 26 '18 at 21:10
  • My point is anyway, that it should check if the same PERSON_ID is there several times. If it is, it shouldnt take in the dublicated rows.. :) Tell me if there's more info i can provide you with mate. – Adem May 26 '18 at 21:13
  • My point is that your expected results need to be **representative** of the sample data you have; if it's not we can't (easily) help you as we need to guess (and if we guess we might be wrong, and then we're wasting our time). – Thom A May 26 '18 at 21:18
  • It is now, when i deleted that line - My bad, it was just to imagine what i could look like :-) – Adem May 26 '18 at 21:20
  • I still can't see a way to create those expected results. None of those people appear in your grades table. I've asked enough questions now without getting something workable, so I'm going to let someone else deal with this now... – Thom A May 26 '18 at 21:45
  • @Larnu Has quite patiently been helping you to improve the question, but have you searched for a solution before posting? This sort of question has been asked a lot before (wouldn't be surprised if it's the most asked sql related question). When you were writing this question you probably already had suggested related questions presented. Did you try anything? – HoneyBadger May 26 '18 at 22:59
  • Possible duplicate of [How to delete duplicate rows in sql server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) – HoneyBadger May 26 '18 at 23:08

0 Answers0