-2

I want to sort a table by name. These should not be alphabetic but the largest id. I have this table.

id  name     
---|-----|
1  | abc | 
2  | abc |
3  | def |
4  | def |
5  | def |
6  | abc |
7  | abc |
8  | def |

That's what i need

id  name     
---|-----|
8  | def |
5  | def |
4  | def |
3  | def |
7  | abc |
6  | abc |
2  | abc |
1  | abc |

Does anyone have an idea?

Shadow
  • 33,525
  • 10
  • 51
  • 64
MacGyver
  • 1
  • 1

2 Answers2

0

Join the table with a subquery that gets the largest ID for each name and order by that.

SELECT t1.*
FROM YourTable AS t1
JOIN (
    SELECT name, MAX(id) AS maxid
    FROM YourTable
    GROUP BY name
) AS t2 ON t1.name = t2.name
ORDER BY maxid DESC, id DESC

If you have two tables, you can still join them with the subquery.

select p1.*, i.*
FROM posts AS p1
JOIN influencers AS i ON p1.i_name = i.i_name
JOIN (
    SELECT i_name, MAX(p_id) AS maxid
    FROM posts
    GROUP BY i_name
) AS p2 ON p1.i_name = p2.i_name
ORDER BY p2.maxid DESC, p1.p_id DESC
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thanks, with just one spreadsheet that works. I thought I could extend that to two spreadsheets alone, but I can not do it. I have two tables. This was my old select statement: ``` SELECT * from posts, influencer where posts.i_name = influencer.i_name order by influencer.i_name, posts.p_id ``` How can I bring that together? – MacGyver Sep 05 '19 at 22:59
  • Can't you simply join that with the subquery in the same way? – Barmar Sep 05 '19 at 23:40
  • I've shown how to do add that table. – Barmar Sep 05 '19 at 23:42
  • You should learn how to use ANSI JOIN instead of old-style cross products. It doesn't change how the query is processed, but it makes the relationships easier to see. – Barmar Sep 05 '19 at 23:43
  • Absolutely perfect!!! It works. Many thanks. I must learn Joins. – MacGyver Sep 06 '19 at 00:16
0

This is really shouting for window-functions:

SELECT *
FROM your_table t
ORDER BY MAX(id) OVER (PARTITION BY name) DESC, id DESC

With your select from the comment under Barmar's answer:

SELECT *
FROM posts p, influencer i
WHERE p.i_name = i.i_name
ORDER BY MAX(p.p_id) OVER (PARTITION BY i.i_name) DESC, p.p_id DESC

Perhaps also have a look here: MySQL Manual

Islingre
  • 2,030
  • 6
  • 18
  • hmm. is not working. this is the error # 1064 - Error in the SQL syntax. Please refer to the correct syntax in the manual at 'OVER (PARTITION BY – MacGyver Sep 05 '19 at 23:59
  • Can you provide your mysql-version, please? I took another look at the linked manual and could not see a mistake. Perhaps your version has a different spec? – Islingre Sep 06 '19 at 00:09
  • [This site](http://www.mysqltutorial.org/mysql-window-functions/) states, that window functions are only supported since version 8.0. Querying Google for "mysql version history", this version was published April 19, 2018. This is not that new, but some systems may be quite old, so I guess it is a matter of version here. – Islingre Sep 06 '19 at 00:21