How to output data using query builder while eliminating duplicates

Question

I have a table of results where there is adm_no, subject_id, exam_id and score fields. I inserted the data using the updateOrCreate method in laravel 5.4.

When I fetch the data I still get duplicates. e.g. when a user inserts marks for instance 2 students for a given subject and repeats it for the same students and subject I should get the latest row of all results.

Here is a sample of my query:

public function searchStudent()
{
    $exam = Exam::all();
    $term = Term::all();

    $a = Input::get('adm_no');
    $e = Input::get('exam_id');
    $t = Input::get('term_id');
    $y = Input::get('year');

    $results = DB::table('results')
        ->select(DB::raw('DISTINCT(subject_id)'))
        ->where('adm_no', 'LIKE', '%' . $a . '%')
        ->where('exam_id','LIKE', '%' . $e . '%')
        ->where('term_id', 'LIKE', '%' . $t . '%')
        ->where('year', 'LIKE', '%' . $y . '%')
        ->orderBy('created_at', 'desc')
        ->get();

    dd($results); 
}

score 0 · Answer 1 · answered Mar 06 '17 at 13:37

0

Try the following query:

$results = DB::table('results')
        ->groupBy('subject_id')
        ->where('adm_no', 'LIKE', '%' . $a . '%')
        ->where('exam_id','LIKE', '%' . $e . '%')
        ->where('term_id', 'LIKE', '%' . $t . '%')
        ->where('year', 'LIKE', '%' . $y . '%')
        ->orderBy('created_at', 'desc')
        ->get();

answered Mar 06 '17 at 13:37

Desh901

2,633
17
24

Its still not picking the latest value. let's say the first record for adm_no: 1 , subject_id :1 and had a score of 35 and a second score for the same student and subject was 40 how can i pick 40 and ignore 35 in my query. – David Musyoka Mar 06 '17 at 17:03
Oh, this changes the question a bit, try to checkout [this question](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Desh901 Mar 06 '17 at 17:15

score 0 · Answer 2 · answered Mar 07 '17 at 07:47

i did more research on the question and this is what i got working for me.

Question

      public function searchStudent(){
        $a= Input::get( 'adm_no' );
        $e= Input::get( 'exam_id' );
        $t= Input::get( 'term_id' );
        $y= Input::get( 'year' );
         $results=DB::table('results')->distinct('subject_id')
        ->where( 'adm_no', $a)
        ->where( 'exam_id', $e)
        ->where( 'term_id',$t)
        ->where( 'year',$y)
        ->groupBy('subject_id')
        ->latest('subject_id')
        ->get();
        dd($results);
          }

How to output data using query builder while eliminating duplicates

2 Answers2