1

I am trying to retrieve the max(CRN) with a group by function to retrieve duplicate records aside from CRN but it is not working: below is my select statement:

    select max(CRN), TERM, SUBJECT, COURSE, SCH, R_KEY, R_PERC 
    from database 
     group by        TERM, SUBJECT, COURSE, SCH, R_KEY, R_PERC

Below is my input file:

CRN  TERM   SUBJECT COURSE  SCH   R_KEY R_PERC
10607      10   DGRG    230  K  36714   37
10607      10   DGRG    230  K  36715   38
11658      10   MATH    101  E  213162  100
11658      10   MATH    101  L  182471  100
12811      10   MHRM    310  L  32951   50
12811      10   MHRM    310  L  130923  50
12879      10   MHRM    310  L  32738   50
12879      10   MHRM    310  L  32951   50
12912      10   MHRM    310  L  120641  50
12912      10   MHRM    310  L  121008  50

I am expecting the same below output as (above input) because I have different "R_PERC" or "R_KEY":

CRN  TERM   SUBJECT COURSE  SCH   R_KEY R_PERC
10607      10   DGRG    230  K  36714   37
10607      10   DGRG    230  K  36715   38
11658      10   MATH    101  E  213162  100
11658      10   MATH    101  L  182471  100
12811      10   MHRM    310  L  32951   50
12811      10   MHRM    310  L  130923  50
12879      10   MHRM    310  L  32738   50
12879      10   MHRM    310  L  32951   50
12912      10   MHRM    310  L  120641  50
12912      10   MHRM    310  L  121008  50

But my actual output is only one record for CRN 12811 as below, noting that the output would be similar for all CRNs (meaning one record only) if we include above input file in a much bigger file record:

CRN  TERM   SUBJECT COURSE  SCH   R_KEY R_PERC
10607      10   DGRG    230  K  36714   37
10607      10   DGRG    230  K  36715   38
11658      10   MATH    101  E  213162  100
11658      10   MATH    101  L  182471  100
12811      10   MHRM    310  L  32951   50

12879      10   MHRM    310  L  32738   50
12879      10   MHRM    310  L  32951   50
12912      10   MHRM    310  L  120641  50
12912      10   MHRM    310  L  121008  50
jean
  • 89
  • 1
  • 7

1 Answers1

1

Include the count:

+----------+----------+------+---------+--------+------+--------+--------+
| max(CRN) | count(*) | TERM | SUBJECT | COURSE | SCH  | R_KEY  | R_PERC |
+----------+----------+------+---------+--------+------+--------+--------+
|    10607 |        1 |   10 | DGRG    |    230 | K    |  36714 |     37 |
|    10607 |        1 |   10 | DGRG    |    230 | K    |  36715 |     38 |
|    11658 |        1 |   10 | MATH    |    101 | E    | 213162 |    100 |
|    11658 |        1 |   10 | MATH    |    101 | L    | 182471 |    100 |
|    12879 |        1 |   10 | MHRM    |    310 | L    |  32738 |     50 |
|    12879 |        2 |   10 | MHRM    |    310 | L    |  32951 |     50 | <-- your 12811 record is included in here, because both records have the same R_KEY
|    12912 |        1 |   10 | MHRM    |    310 | L    | 120641 |     50 |
|    12912 |        1 |   10 | MHRM    |    310 | L    | 121008 |     50 |
|    12811 |        1 |   10 | MHRM    |    310 | L    | 130923 |     50 |
+----------+----------+------+---------+--------+------+--------+--------+

These two records get grouped:

+-------+------+---------+--------+------+-------+--------+
| CRN   | TERM | SUBJECT | COURSE | SCH  | R_KEY | R_PERC |
+-------+------+---------+--------+------+-------+--------+
| 12811 |   10 | MHRM    |    310 | L    | 32951 |     50 |
| 12879 |   10 | MHRM    |    310 | L    | 32951 |     50 |
+-------+------+---------+--------+------+-------+--------+
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Thanks but why Oracle database or MySQL database are reacting that way; meaning deleting some records that are not duplicated ?? – jean Dec 06 '17 at 08:40
  • They are not deleted. Look at the second table I included. They are grouped and displayed as one row, because you haven't included CRN in the group by. 12879 is greater than 12811, so this is displayed since you're using max() function. The behaviour is totally fine and correct and expected. – fancyPants Dec 06 '17 at 08:41
  • but why the following record disappeared: – jean Dec 06 '17 at 09:37
  • 12811 10 MHRM 310 L 130923 50 instead of 12811 10 MHRM 310 L 32951 50 – jean Dec 06 '17 at 09:38
  • What do you mean with "disappeared"? The record `12811 10 MHRM 310 L 130923 50` is right there, the record `12811 10 MHRM 310 L 32951 50` is like I said in the group with `12879 10 MHRM 310 L 32951 50`, as you can tell by the count column. – fancyPants Dec 06 '17 at 10:15
  • yes but record "12811 10 MHRM 310 L 130923 50" is disappearing; please check my third output list in the question above at the first beginning – jean Dec 06 '17 at 10:23
  • Can you recheck? I can not reproduce that. For me it's there. See here for example: http://sqlfiddle.com/#!4/60ada/2/0 I can't reproduce it in MySQL either. Record is there. – fancyPants Dec 06 '17 at 10:34