Query to retrieve the latest entry in a history ta

Question

Here is the table

table: StudentHistory

id   |  date     | name  | grade  | subject
---- | ------    |------ |--------|------
1    | 5/1/2017  |Mark   |   a    |   science
2    | 7/1/2016  |Earl   |   c    |   english
3    | 2/1/2015  |John   |   a    |   english
4    | 6/1/2016  |Mike   |   c    |   science
5    | 4/1/2016  |Matt   |   e    |   english
6    | 2/1/2017  |Mark   |   d    |   science
7    | 3/1/2016  |Earl   |   a    |   english
8    | 7/1/2015  |John   |   d    |   english
9    | 8/1/2016  |Mike   |   c    |   science

What I want to happen is to populate the latest grades ONLY in English for students who have one. It should show like this

7/1/2016 Earl c
7/1/2015 John d
4/1/2016 Matt e

I got this but it doesn't give the latest based on

$englishgrades = StudentHistory::('date', 'name', 'grade') ->where('subject', 'english') ->groupBy('name') ->get();

Please help

Answer 1

I don't know what you mean by 'populate' in this context, and I don't know laravel; but here's a query that gets the desired result...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id   INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,date  DATE NOT NULL
,name  VARCHAR(12) NOT NULL
,grade  CHAR(1) NOT NULL
,subject VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1,'2017/5/1','Mark','a','science'),
(2,'2016/7/1','Earl','c','english'),
(3,'2015/2/1','John','a','english'),
(4,'2016/6/1','Mike','c','science'),
(5,'2016/4/1','Matt','e','english'),
(6,'2017/2/1','Mark','d','science'),
(7,'2016/3/1','Earl','a','english'),
(8,'2015/7/1','John','d','english'),
(9,'2016/8/1','Mike','c','science');

SELECT a.* 
  FROM my_table a 
  JOIN 
     ( SELECT name
            , MAX(date) date
         FROM my_table 
        WHERE subject = 'English' 
        GROUP 
           BY name
     ) b 
    ON b.name = a.name 
   AND b.date = a.date;
+----+------------+------+-------+---------+
| id | date       | name | grade | subject |
+----+------------+------+-------+---------+
|  2 | 2016-07-01 | Earl | c     | english |
|  5 | 2016-04-01 | Matt | e     | english |
|  8 | 2015-07-01 | John | d     | english |
+----+------------+------+-------+---------+

Answer 2

You can do descending order and receive first row which is going to be your last row in database. But the thing is you store date in a wrong way. as mentioned by @Strawberry its not clear date or month. I would suggest you to do use mysql datetime option then you can simply do the code I post below. In this case you might have to parse each date for a proper way

$englishgrades = StudentHistory::select('date', 'name', 'grade')
->where('subject', 'english')
->groupBy('name')
->orderBy('date','desc')
->get();