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
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();
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 |
+----+------------+------+-------+---------+
