SQL sort by and include only most recent column [on hold]
up vote
-3
down vote
favorite
What I have so far is this:
Name : Date
Person A : 21/Aug/2017
Person B : 04/DEC/2015
Person C : 30/NOV/2013
Person A : 21/AUG/2012
and so on ....
And what I want it to show me is just the MOST RECENT company for every NAME column, so in the above case it would remove the bottom row, and any older rows for person A, B and C, whilst if an entry is for Person D it would show it.
I've been trying for hours, however have been unable to work out how to achieve this, I suspect it would be something to do with Max(Date) but just can't work it out.
EDIT TO INSERT CODE -----
So, the code that I have so far is this:
SELECT DISTINCT Academic_tbl.name, academic_tbl.id, affiliation_lnk_tbl.aff_date FROM Academic_tbl
FULL OUTER JOIN affiliation_lnk_tbl ON academic_tbl.id = affiliation_lnk_tbl.academic_id
GROUP BY academic_tbl.id, academic_tbl.name, affiliation_lnk_tbl.aff_date
ORDER BY affiliation_lnk_tbl.aff_date DESC;
This outputs me the above table format with some minor variances. I know how to attach it to other tables to connect the rest of the information I need, for this question I am literally just trying to show Person A just the once, with the latest date shown too.
sql
asked yesterday
Andy
11
New contributor
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by πάντα ῥεῖ, Toby Speight, 200_success, Sᴀᴍ Onᴇᴌᴀ, vnp yesterday
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – πάντα ῥεῖ, Toby Speight, 200_success, Sᴀᴍ Onᴇᴌᴀ, vnp
If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
-3
down vote
favorite
What I have so far is this:
Name : Date
Person A : 21/Aug/2017
Person B : 04/DEC/2015
Person C : 30/NOV/2013
Person A : 21/AUG/2012
and so on ....
And what I want it to show me is just the MOST RECENT company for every NAME column, so in the above case it would remove the bottom row, and any older rows for person A, B and C, whilst if an entry is for Person D it would show it.
I've been trying for hours, however have been unable to work out how to achieve this, I suspect it would be something to do with Max(Date) but just can't work it out.
EDIT TO INSERT CODE -----
So, the code that I have so far is this:
SELECT DISTINCT Academic_tbl.name, academic_tbl.id, affiliation_lnk_tbl.aff_date FROM Academic_tbl
FULL OUTER JOIN affiliation_lnk_tbl ON academic_tbl.id = affiliation_lnk_tbl.academic_id
GROUP BY academic_tbl.id, academic_tbl.name, affiliation_lnk_tbl.aff_date
ORDER BY affiliation_lnk_tbl.aff_date DESC;
This outputs me the above table format with some minor variances. I know how to attach it to other tables to connect the rest of the information I need, for this question I am literally just trying to show Person A just the once, with the latest date shown too.
sql
asked yesterday
Andy
11
New contributor
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by πάντα ῥεῖ, Toby Speight, 200_success, Sᴀᴍ Onᴇᴌᴀ, vnp yesterday
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – πάντα ῥεῖ, Toby Speight, 200_success, Sᴀᴍ Onᴇᴌᴀ, vnp
If this question can be reworded to fit the rules in the help center, please edit the question.
4
Welcome on Code Review. Unfortunately, we don't provide code, we review code you've written. Depending on your problem, another site of the StackExchange network can help you. Please see our help center for more information.
– Calak
yesterday
add a comment |
up vote
-3
down vote
favorite
up vote
-3
down vote
favorite
What I have so far is this:
Name : Date
Person A : 21/Aug/2017
Person B : 04/DEC/2015
Person C : 30/NOV/2013
Person A : 21/AUG/2012
and so on ....
And what I want it to show me is just the MOST RECENT company for every NAME column, so in the above case it would remove the bottom row, and any older rows for person A, B and C, whilst if an entry is for Person D it would show it.
I've been trying for hours, however have been unable to work out how to achieve this, I suspect it would be something to do with Max(Date) but just can't work it out.
EDIT TO INSERT CODE -----
So, the code that I have so far is this:
SELECT DISTINCT Academic_tbl.name, academic_tbl.id, affiliation_lnk_tbl.aff_date FROM Academic_tbl
FULL OUTER JOIN affiliation_lnk_tbl ON academic_tbl.id = affiliation_lnk_tbl.academic_id
GROUP BY academic_tbl.id, academic_tbl.name, affiliation_lnk_tbl.aff_date
ORDER BY affiliation_lnk_tbl.aff_date DESC;
This outputs me the above table format with some minor variances. I know how to attach it to other tables to connect the rest of the information I need, for this question I am literally just trying to show Person A just the once, with the latest date shown too.
sql
asked yesterday
Andy
11
New contributor
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
What I have so far is this:
Name : Date
Person A : 21/Aug/2017
Person B : 04/DEC/2015
Person C : 30/NOV/2013
Person A : 21/AUG/2012
and so on ....
And what I want it to show me is just the MOST RECENT company for every NAME column, so in the above case it would remove the bottom row, and any older rows for person A, B and C, whilst if an entry is for Person D it would show it.
I've been trying for hours, however have been unable to work out how to achieve this, I suspect it would be something to do with Max(Date) but just can't work it out.
EDIT TO INSERT CODE -----
So, the code that I have so far is this:
SELECT DISTINCT Academic_tbl.name, academic_tbl.id, affiliation_lnk_tbl.aff_date FROM Academic_tbl
FULL OUTER JOIN affiliation_lnk_tbl ON academic_tbl.id = affiliation_lnk_tbl.academic_id
GROUP BY academic_tbl.id, academic_tbl.name, affiliation_lnk_tbl.aff_date
ORDER BY affiliation_lnk_tbl.aff_date DESC;
This outputs me the above table format with some minor variances. I know how to attach it to other tables to connect the rest of the information I need, for this question I am literally just trying to show Person A just the once, with the latest date shown too.
sql
sql
asked yesterday
Andy
11
New contributor
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked yesterday
Andy
11
New contributor
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited yesterday
asked yesterday
Andy
11
New contributor
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked yesterday
Andy
11
asked yesterday
Andy
11
11
New contributor
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Andy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
put on hold as off-topic by πάντα ῥεῖ, Toby Speight, 200_success, Sᴀᴍ Onᴇᴌᴀ, vnp yesterday
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – πάντα ῥεῖ, Toby Speight, 200_success, Sᴀᴍ Onᴇᴌᴀ, vnp
If this question can be reworded to fit the rules in the help center, please edit the question.
put on hold as off-topic by πάντα ῥεῖ, Toby Speight, 200_success, Sᴀᴍ Onᴇᴌᴀ, vnp yesterday
This question appears to be off-topic. The users who voted to close gave this specific reason:
- "Code not implemented or not working as intended: Code Review is a community where programmers peer-review your working code to address issues such as security, maintainability, performance, and scalability. We require that the code be working correctly, to the best of the author's knowledge, before proceeding with a review." – πάντα ῥεῖ, Toby Speight, 200_success, Sᴀᴍ Onᴇᴌᴀ, vnp
If this question can be reworded to fit the rules in the help center, please edit the question.
4
Welcome on Code Review. Unfortunately, we don't provide code, we review code you've written. Depending on your problem, another site of the StackExchange network can help you. Please see our help center for more information.
– Calak
yesterday
add a comment |
4
Welcome on Code Review. Unfortunately, we don't provide code, we review code you've written. Depending on your problem, another site of the StackExchange network can help you. Please see our help center for more information.
– Calak
yesterday
4
4
Welcome on Code Review. Unfortunately, we don't provide code, we review code you've written. Depending on your problem, another site of the StackExchange network can help you. Please see our help center for more information.
– Calak
yesterday
Welcome on Code Review. Unfortunately, we don't provide code, we review code you've written. Depending on your problem, another site of the StackExchange network can help you. Please see our help center for more information.
– Calak
yesterday
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
Welcome on Code Review. Unfortunately, we don't provide code, we review code you've written. Depending on your problem, another site of the StackExchange network can help you. Please see our help center for more information.
– Calak
yesterday