SQL Select last row from row that have same column-CodePudding

I am new to sql and went through alot of discussion and youtube but i get more confused.

I was using SELECT * FROM tp_posts to list number of posts into my website so basically below is how my table looks like.


          table name tp_posts
==========================================
|  ID       |       post   | tp_product
=================================================
|   1       |  toyota 1    | toyota
|   2       |  mercedez 1  | mercedez
|   3       |  bmw 1       | bmw
|   4       |  toyota 2    | toyota
|   5       |  toyota 3    | toyota
|   6       |  toyota 4    | toyota
==========================================

Above table is example on how it looks as you can see it will show all the i items above if i use the normal query i mentioned above so i started using this

SELECT * FROM tp_posts GROUP BY tp_product

this works but it only shows the first toyota post and hide the latest ones and order by is not working also as group by hide the other posts

I want to display the latest post while filtering related posts that share the same tp_product column, it has to show the last toyota post from above table

          table name tp_posts
========================================================
|  ID       |       post   | tp_product      post_numb
========================================================
|   1       |  toyota 1    | toyota        |   1
|   2       |  mercedez 1  | mercedez      |   1
|   3       |  bmw 1       | bmw           |   1
|   4       |  toyota 2    | toyota        |   2
|   5       |  toyota 3    | toyota        |   3
|   6       |  toyota 4    | toyota        |   4
==========================================

Update This is the complete table now as you can see above post section and post_num are related on post it shows toyota 1 tp_product which is product type = toyota and post_numb = 1 which represents toyota 1 in posts section.

Some people suggested i use order by but it doesn't work like that as i get all posts in table either way i want to get the result that look like this table below


table name tp_posts
========================================================
|  ID       |       post   | tp_product      post_numb
========================================================
|   6       |  toyota 4    | toyota        |   4
|   3       |  bmw 1       | bmw           |   1
|   2       |  mercedez 1  | mercedez      |   1

======================================================

I want to get The above results in my query, sorry if i was not clear enough Thanks

CodePudding user response：

if you always want to select last row then you can use below query

SELECT * FROM tp_posts ORDER BY id DESC LIMIT 1;

Else share the expected result from above data. Then only solution can be suggested.

CodePudding user response：

I am new to SQL too, but The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

CodePudding user response：

You can use this, by using MAX to select the max post_numb for each tp_product inside a subqery, then sorting them in a descending order to achieve your desired result.

The subquery will select the MAX post_numb and the main query will get the row where the post_numb matches what was selected by the subquery according to tp_product.

SELECT * FROM tp_posts a
WHERE post_numb = (SELECT MAX(post_numb) FROM tp_posts b WHERE a.`tp_product`=b.`tp_product`)
GROUP BY tp_product ORDER BY id DESC

RESULT

    ID  post        tp_product  post_numb  
------  ----------  ----------  -----------
     6  toyota 4    toyota      4          
     3  bmw 1       bmw         1          
     2  mercedez 1  mercedez    1

CodePudding user response：

You can use ROW_NUMBER() function to generate row number partitioned by tp_product then order by post_numb descending; with consideration that the largest post_numb will be the "latest". Make that as subquery and do a WHERE Rnum=1 to only return the ones being deemed as "latest":

SELECT ID, post, tp_product, post_numb
   FROM
(SELECT ID, post, tp_product, post_numb,
       ROW_NUMBER() OVER (PARTITION BY tp_product ORDER BY post_numb DESC) AS Rnum
    FROM tp_posts) v
    WHERE Rnum=1;

However, this only work with MySQL v8 and MariaDB v10.2 .

Demo