One SQL statement of MySQL queries the SQL statements of some result instances of all different types of products.

One SQL statement of MySQL queries the SQL statements of some result instances of all different types of products.

For & ever

 

 

Consider the following scenarios:
There is a product table with many product records. The table contains the product type field prodtype, which is used to indicate different product types. There are 10 different product types, for example.

 

Requirements:
Sort by product ID using an SQL statement to query the latest five of the 10 different types of products.

For example, the following table records:
1 prodtypea
2 prodtypeb
3 prodtypea
4 prodtypea
5 prodtypea
6 prodtypeb
7 prodtypea
8 prodtypea
9 prodtypea

 

You need to use an SQL statement to query the following result set:

9 prodtypea
8 prodtypea
7 prodtypea
5 prodtypea
4 prodtypea
6 prodtypeb
2 prodtypeb

 

 

After discussing with xinxinran today, the result is as follows :::

You can use the following SQL query:
Select ID, prodtype from product A where (select count (1) from product B where a. prodtype = B. prodtype and B. ID> = A. ID) <= 5

What if we want to obtain the oldest five records?
Modify B. ID> = A. ID to B. ID <= A. ID, as shown below:
Select ID, prodtype from product A where (select count (1) from product B where a. prodtype = B. prodtype and B. ID <= A. ID) <= 5

 

@ Forandever for & ever