Sort by larger ID and group the similar

Asked

Viewed 308 times

1

How can I list the records by ordering by the latter, but when you have the field related filled, bring the other records with that same value?

The general idea is to make a order by id desc and group the related.

My table is like this:

ID  TITLE       RELATED
1   tenis       null
2   camiseta    123456
3   calca       null
4   moletom     123456
5   casaco      null

The correct would be to list as follows: listing by the largest ID, but when you have the field related, priority for other records that have the same value as it.

ID  TITLE       RELATED
5   casaco      null
4   moletom     123456
2   camiseta    123456
3   calca       null
1   tenis       null
mysql sql
  • 1

    I don’t think you can do that with a query "". There must be some intermediate logic there.

2 answers

2


Here are two alternatives:

The first consists of using a junction (with the table itself). This is a portable solution, since it only uses standard SQL language elements or functionality, and which are available in (almost) all database management systems.

SELECT P1.*
  FROM produtos P1
  LEFT JOIN 
  ( 
     SELECT RELATED, MAX(ID) MAX_ID 
       FROM produtos 
      WHERE RELATED IS NOT NULL 
      GROUP BY RELATED
  ) AS P2
    ON P2.related = P1.RELATED
 ORDER BY CASE WHEN P2.RELATED IS NULL THEN P1.ID ELSE P2.MAX_ID END DESC, P1.ID DESC

The idea is to identify the products for which information exists in the RELATED column, and for each of the RELATED codes to identify the corresponding maximum ID. This information will be used as the sort criterion as follows:

ORDER BY CASE WHEN P2.RELATED IS NULL THEN P1.ID ELSE P2.MAX_ID END DESC

The result will be the one you indicated in your question:

| ID  | TITLE    | RELATED |
| --- | -------- | ------- |
| 5   | casaco   |         | 
| 4   | moletom  | 123456  |
| 2   | camiseta | 123456  |
| 3   | calca    |         |
| 1   | tenis    |         |

The second alternative makes use of the window functions available in the latest versions of Mysql.

SELECT ID,
       TITLE,
       RELATED,
       MAX(ID) OVER (PARTITION BY CASE WHEN RELATED IS NOT NULL THEN RELATED ELSE ID END) AS SortingOrder
  FROM produtos
ORDER BY 4 DESC, ID DESC
;

The result will be the same as the previous version.

| ID  | TITLE    | RELATED | SortingOrder |
| --- | -------- | ------- | ------------ |
| 5   | casaco   |         | 5            |
| 4   | moletom  | 123456  | 4            |
| 2   | camiseta | 123456  | 4            |
| 3   | calca    |         | 3            |
| 1   | tenis    |         | 1            |

  • Perfect! Just one observation, in the first alternative we only had to remove the part "AS SORTING_ORDER". It may be pq I am using version 5.7.

  • You’re right, it was a copy-Paste error. I’ve already changed the answer.

2

SELECT id, 
       title, 
       related 
  FROM produtos
 ORDER BY 
  CASE WHEN related IS NULL 
        THEN 1
        ELSE 0
   END, related;

Upshot:

id title Related

2 shirt 123456

4 sweatshirt 123456

1 tenis NULL

3 calca NULL

5 NULL COAT

(5 line(s) affected)

It is not possible to classify this way you want, because the classification follows a hierarchy SQL classifies COD and within Cod classifies Related but if you the main objective is the grouping of the Related and descending orderm maybe it’ll help you.

or it helps

SELECT title,
       related,
       cod 
  FROM produtos
 ORDER BY 
  CASE WHEN related IS NULL 
        THEN 1
        ELSE 0
   END, related, cod DESC;

title Related Cod

sweatshirt 123456 4

T-shirt 123456 2

null 5 coat

calca NULL 3

tenis NULL 1

(5 line(s) affected)

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.