如何按列和下一行排序并按另一列排序?

I have a table with four columns: ID, isError, SolidLine and HighestError.

我有一个包含四列的表:ID、isError、SolidLine和HighestError。

Each row is related to another row by SolidLine column. So we have two related rows in the table.

每一行都通过SolidLine列与另一行相关联。表中有两个相关的行。

For example, rows with ID 1 and 2 have relation by SolidLine(5).

例如，ID为1和ID为2的行通过SolidLine(5)具有关系。

----------------------------------------------------------------------
|      ID      |    isError     |      SolidLine   |    HighestError
----------------------------------------------------------------------
|       1      |     0          |        5         |     1
|       2      |     0          |        5         |     1
|       3      |     0          |        8         |     1
|       4      |     0          |        8         |     1
|       5      |     1          |        10        |     50 
|       6      |     0          |        10        |     1
|       7      |     1          |        4         |     80
|       8      |     0          |        4         |     1
|       9      |     1          |        7         |     80
|      10      |     0          |        7         |     1
|      11      |     0          |        3         |     1 
|      12      |     0          |        3         |     1
----------------------------------------------------------------------

I would like to sort a table by the following condition:

我想按以下条件对表进行排序:

If isError is 1, take the next row by SolidLine, then order by HighestError

如果isError为1，则使用SolidLine获取下一行，然后按HighestError排序

So the wish result should look like this:

因此，希望的结果应该是这样的:

----------------------------------------------------------------------
|      ID      |    isError     |      SolidLine   |    HighestError
----------------------------------------------------------------------
|       7      |     1          |        4         |   80
|       8      |     0          |        4         |   1
|       9      |     1          |        7         |   80
|       10     |     0          |        7         |   1
|       5      |     1          |        10        |   50
|       6      |     0          |        10        |   1
|       1      |     0          |        5         |   1
|       2      |     0          |        5         |   1
|       3      |     0          |        8         |   1
|       4      |     0          |        8         |   1
|       11     |     0          |        3         |   1
|       12     |     0          |        3         |   1 
----------------------------------------------------------------------

The first row becomes the first row as HighestError has maximum value in the table isError equals 1. Then the next row goes with ID = 8 as it SolidLine has the same value SolidLine of row with ID = 7.

第一行成为第一行，因为HighestError在表isError = 1中有最大值。然后下一行与ID = 8匹配，因为SolidLine与ID = 7的行具有相同的值SolidLine。

SolidLine are pairs always together and does not depend upon isError column.

SolidLine始终是成对的，不依赖于isError列。

So the pair of rows tied by SolidLine should always be together.

所以用SolidLine绑定的这对行应该总是在一起的。

I tried the following queries, but it gives wrong result:

我尝试了以下的查询，但是结果是错误的:

--it breaks SolidLine ordering. 
SELECT ID, isError, SolidLine, HighestError
FROM SolidThreads
ORDER BY SolidLine, isError, HighestError desc, id

and:

和:

SELECT 
ROW_NUMBER() OVER (PARTITION BY SolidLine ORDER BY isError DESC) [RowNumber],
ID, isError, SolidLine, HighestError
FROM SolidThreads
ORDER BY HighestError desc, id

What am I doing wrong? Or how can I do it?

我做错了什么?我该怎么做呢?

3 个解决方案

#1

As you describe it, you should be able to do this by...

正如你所描述的，你应该能够通过……

adding a column for "This Solid Line Includes an Error Row"
为“这个实线包含一个错误行”添加一列
adding a column for "The max error for this Solid Line"
为“实线的最大误差”添加一列
using CASE expressions to change the sorting based on error state
使用CASE表达式根据错误状态更改排序

http://sqlfiddle.com/#!18/84e7a/1

http://sqlfiddle.com/ ! 18/84e7a / 1

WITH
  SolidThreadsSummary AS
(
  SELECT
    *,
    MAX(isError     ) OVER (PARTITION BY SolidLine)   AS SolidLineHasError,
    MAX(highestError) OVER (PARTITION BY SolidLine)   AS SolidLineMaxError
  FROM
    SolidThreads
)
SELECT
  *
FROM
  SolidThreadsSummary
ORDER BY
  SolidLineHasError DESC,  -- Not really necessary for your data
  SolidLineMaxError DESC,
  CASE WHEN SolidLineHasError > 0 THEN SolidLine ELSE 1 END,
  isError DESC,
  id

This may be a little more robust if pairs are not always consecutive by id (for the pairs containing no error)...

如果对不总是由id连续的(对于不包含错误的对)，这可能会更加健壮一些……

http://sqlfiddle.com/#!18/84e7a/2

http://sqlfiddle.com/ ! 18/84e7a / 2

WITH
  SolidThreadsSummary AS
(
  SELECT
    *,
    MAX(isError     ) OVER (PARTITION BY SolidLine)   AS SolidLineHasError,
    MAX(highestError) OVER (PARTITION BY SolidLine)   AS SolidLineMaxError,
    MIN(id          ) OVER (PARTITION BY SolidLine)   AS SolidLineMinID
  FROM
    SolidThreads
)
SELECT
  *
FROM
  SolidThreadsSummary
ORDER BY
  SolidLineHasError DESC,
  SolidLineMaxError DESC,
  CASE WHEN SolidLineHasError > 0 THEN SolidLine ELSE 1 END,
  isError DESC,
  SolidLineMinID,
  id
;

#2

It seems like you want to sort keeping the SolidLines together, and ordering those groups first by HighestError then by the lowest ID in the group, then within the group show errors first. Assuming that's what you want, I would do this with a derived table:

看起来你想要将SolidLines放在一起，然后由HighestError先排序，然后由组中最低的ID排序，然后在组中首先显示错误。假设这是你想要的，我用一个衍生的表格

ID, isError, SolidLine, HighestError
FROM SolidThreads INNER JOIN
(SELECT SolidLine, MAX(Highesterror) as sorting_HighestError, MIN(ID) as Sorting_Id
    FROM SolidThreads GROUP BY SolidLine) as Sorting_DT
ON Sorting_DT.SolidLine = SolidThreads.SolidLine
ORDER BY sorting_HighestError DESC, Sorting_Id, isError Desc, Id

#3

If the ID is always sequential for each SolidLine pair, you can simply do this:

如果每个SolidLine对的ID都是连续的，您可以简单地这样做:

SELECT T.*
FROM yourTable T
JOIN (SELECT SolidLine, MAX(HighestError) MaxError
      FROM yourTable
      GROUP BY SolidLine) T2 ON T.SolidLine = T2.SolidLine
ORDER BY MaxError DESC, ID

#1