Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: Eliminate duplicates?

    How do I get it so that every student ID is listed just 1x?
    SQL> select DISTINCT(student_class.student_id) as student_Num,student_class.class_id,
    2 event.event_id, event.event_name
    3 from student_class, event
    4 where student_class.class_id = '10'
    5 and event.class_id = '10';

    STUDENT_NUM CLASS_ID EVENT_ID EVENT_NAME
    ----------- ---------- ---------- --------------------------------------------------
    12 10 2 Flag FOOtball Game
    12 10 5 PICKUP SOCCER GAME
    9 10 5 PICKUP SOCCER GAME
    16 10 5 PICKUP SOCCER GAME
    6 10 2 Flag FOOtball Game
    18 10 5 PICKUP SOCCER GAME
    4 10 5 PICKUP SOCCER GAME
    4 10 2 Flag FOOtball Game
    16 10 2 Flag FOOtball Game
    20 10 2 Flag FOOtball Game
    3 10 5 PICKUP SOCCER GAME
    2 10 5 PICKUP SOCCER GAME
    3 10 2 Flag FOOtball Game
    8 10 2 Flag FOOtball Game
    9 10 2 Flag FOOtball Game
    2 10 2 Flag FOOtball Game
    6 10 5 PICKUP SOCCER GAME
    20 10 5 PICKUP SOCCER GAME
    18 10 2 Flag FOOtball Game
    8 10 5 PICKUP SOCCER GAME

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    for example STUDENT_NUM=12 has 2 records.
    you say STUDENT_NUM=12 should only be returned ONCE.
    so which record get returned & why for STUDENT_NUM=12

    the same question needs to be answer when ever multiple records exist for same STUDENT_NUM
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Posts
    57
    Got it, I talked to instructor, she suggested inserting another field to make the distinction.
    I added GPA to the query..........
    Code:
    SQL> select distinct students.student_id, students.student_name, students.school_id, students.gpa
      2  from students join student_class on students.student_id = student_class.student_id
      3  join event on student_class.class_id = event.class_id
      4  where upper(event_name) like '%GAME%'
      5  order by student_id;
    
    STUDENT_ID STUDENT_NAME                                        SCHOOL_ID        GPA
    ---------- -------------------------------------------------- ---------- ----------
             2 FREDDY FROST                                            11546       1.22
             3 Frank Smith                                             11111       3.21
             4 Rob S. Frost                                            11111          4
             6 Sally SAvage                                            11546       3.54
             8 Franklin Q. Kafka                                       11111        3.2
             9 Jacky Frost                                             11111       1.22
            12 Samuel Smith                                            11111        3.2
            16 Robby Lee                                              123134       1.22
            18 Robert Frost                                            11546          4
            20 Fred SAvage                                             11546       3.54

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What the heck is GPA?!?

    Besides, column list you returned in the first message is different from the latest one. If this is allowed, why didn't you simple
    Code:
    select student_id, student_name
    from students
    That should return distinct students (if it doesn't, that would be very bad).

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Littlefoot View Post
    What the heck is GPA?!?
    I was thinking the same thing! In what universe does a student have more than 1 GPA from the same school? Even if you are talking about grad school, you only have 1 gpa for undergrad, one for masters and I guess phD is pass/fail!

    Tables are suppose to model real world entities!
    Last edited by LinksUp; 10-06-13 at 14:24.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •