Efficient short SQL Query

Question

I Have This Table called papers

|ID | Paper_Code | Subject_Code |
| 0 | 2018/Eng/01|     ENG      |
| 1 | 2018/Eng/02|     ENG      |
| 2 | 2018/CS/01 |     CS       |
| 3 | 2018/Sci/01|     Sci      |
| 4 | 2018/Eng/03|     ENG      |

I Want The Results to be

|ID | Paper_Code | Subject_Code |
| 4 | 2018/Eng/03|     ENG      |
| 2 | 2018/CS/01 |     CS       |
| 3 | 2018/Sci/01|     Sci      |

I Have tried

$sql = "SELECT DISTINCT (MAX(Paper_Code) 
     FROM papers WHERE Subject_Code = (SELECT DISTINCT Subject_Code))";

I'm new to sql, tried alot of times but kept failing.

You don't need to post a comment when you add an answer, the poster is notified automatically. — Barmar, Jun 18 '18 at 19:25
You should explain the logic that you're trying to implement. — Barmar, Jun 18 '18 at 19:28

Daniel Marcus · Accepted Answer · 2018-06-18T19:26:58.160

1

select a.* from yourtable a join (
select subject_code, max(id)id
from yourtable
group by subject_code) b 
on a.subject_code=b.subject_code and a.id=b.id

edited Jun 18 '18 at 19:26

answered Jun 18 '18 at 19:23

Daniel Marcus

2,686
1
7
13

He wants `MAX(paper_code)`, not `MAX(id)` – Barmar Jun 18 '18 at 19:24
Above code will get him the data set he wants – Daniel Marcus Jun 18 '18 at 19:25
I think it's just a coincidence that it gives the same answer. – Barmar Jun 18 '18 at 19:25
@Barmar I don't think op know what he want. – Juan Carlos Oropeza Jun 18 '18 at 19:26
His attempted query uses `MAX(paper_code)`. I think that makes it obvious what he's trying to maximize for each subject code. – Barmar Jun 18 '18 at 19:28
@ Daniel Marcus Thanks loads You're a Champ i really struggled with this Thanks Mate – Dreu Davids Jun 18 '18 at 19:34
Great! Please upvote/accept answer – Daniel Marcus Jun 18 '18 at 19:35
i wanted to get rows with the max(paper_code) for Distinct Subject_code – Dreu Davids Jun 18 '18 at 19:36
@ Daniel Marcus i tried up voting it, it says that its recorded but since my reputation is below 15 it won't be shown publicly – Dreu Davids Jun 18 '18 at 19:37
OK you can also click the check mark as well – Daniel Marcus Jun 18 '18 at 19:38

Efficient short SQL Query

1 Answers1