0

It was very difficult to find the words for the title, but hopefully the problem is easily described. I have a table:

+------------+----------------+---------------------+
| identifier | version_number | name                |
+------------+----------------+---------------------+
|          1 |              1 | propositional-logic |
|          2 |              2 | propositional-logic |
|          3 |              3 | propositional-logic |
|          4 |              1 | natural-numbers     |
|          5 |              4 | propositional-logic |
|          6 |              2 | natural-numbers     |
|          7 |              3 | natural-numbers     |
|          8 |              4 | natural-numbers     |
|          9 |              5 | propositional-logic |
+------------+----------------+---------------------+

I want to get the 'latest version' for each name, returning the name and the identifier. I have gotten this far with a query:

select max(version_number),name from `release` group by name;

This comes close, returning the right version numbers and names...

+---------------------+---------------------+ | max(version_number) | name | +---------------------+---------------------+ | 4 | natural-numbers | | 5 | propositional-logic | +---------------------+---------------------+ ...but I need the identifiers, too. If I try the query...

select max(version_number),name,identifier from `release` group by name;

...I get an error. I think I understand where I am going wrong, but cannot find away around it.

Any help would be greatly appreciated. Also, if anyone more experienced than me can think of a more descriptive title, please go right ahead and change it!

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
James Smith
  • 324
  • 3
  • 15
  • Can you point me to a correct answer, thank you. – James Smith Mar 15 '18 at 21:53
  • 3
    I've also flagged your comment as rude, which you know but other users won't. And I will ask the moderator to remove your downvote. We are not all experts and the MySQL manual is hard-going at the best of times. If you cannot manage to be civil, the best thing you can do is to simply ignore the question. – James Smith Mar 15 '18 at 21:57
  • @BillKarwin, it would be better if you could provide a link to the duplicate question. Otherwise your intervention is also not very helpful, although you have at least managed not to be rude. – James Smith Mar 15 '18 at 22:01
  • 1
    Yes, I'm trying to be helpful. Marking a question as a duplicate does insert a link at the top of the question. Stack Overflow intentionally allows questions to be closed when they are duplicates of past questions. https://stackoverflow.com/help/duplicates – Bill Karwin Mar 15 '18 at 22:23
  • 1
    And I agree that Strawberry was being unhelpful. The comment above is no better than an abrupt "RTFM!" which should warrant moderator attention. – Bill Karwin Mar 15 '18 at 22:25
  • 1
    My apologies and thanks. My pathetic excuse is that I was unnerved by the first comment. I didn't see the link at the top of the question. – James Smith Mar 15 '18 at 22:25
  • 1
    I also added the tag [tag:greatest-n-per-group] which should help you find other similar answers. – Bill Karwin Mar 15 '18 at 22:26
  • 1
    You're too kind. My thanks again. – James Smith Mar 15 '18 at 22:27
  • And there's always the section in the online MySQL tutorial for "Examples of Common Queries" https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html – Bill Karwin Mar 15 '18 at 22:27
  • I think I get the idea, Bill. Sometimes, when it's late, and it's been a long day, I kid myself it's easier just to ask on SE. – James Smith Mar 15 '18 at 22:29
  • @billkarwin agree with who/what? – Strawberry Mar 15 '18 at 22:46

1 Answers1

1

You could use a join on the ma version 

select r.name,r.identifier,r.version_number 
from `release` r
inner join  ( 
select max(version_number) as maximum_version_number, name 
from `release`
group by name  
) t on  t.maximum_version_number = r.version_number  and t.name = r.name
James Smith
  • 324
  • 3
  • 15
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Thanks, I'll give it a go. I'd hoped to avoid joins but I'm beginning to understand that that is not possible. – James Smith Mar 15 '18 at 21:58
  • Nearly there, but MySQL complains about ambiguous column names in the (I guess) outer `SELECT` statement, so I've altered your answer a little. – James Smith Mar 15 '18 at 22:17