Possible to create a mysql query that only displays things that are in descending order

To start things off, I want to make it clear that I'm not trying to order by descending order.

I am looking to order by something else, but then fil开发者_如何学Pythonter further by displaying things in a second column only if the value in that column 1 row below it is less than itself. Once It finds that the next column is lower, it stops.

Example:

Ordered by column-------------------Descending Column
353215                                    20
535325                                    15
523532                                    10
666464                                    30
473460                                    20

If given that data, I would like it to only return 20, 15 and 10. Because now that 30 is higher than 10, we don't care about what's below it.

I've looked everywhere and can't find a solution.

EDIT: removed the big number init, and edd the counter in ifnull test, so it works in pure MySQL: ifnull(@prec,counter) and not ifnull(@prec,999999).

If your starting table is t1 and the base request was:

select id,counter from t1 order by id;

Then with a mysql variable you can do the job:

SET @prec=NULL;
select * from (
    select id,counter,@prec:= if(
       ifnull(@prec,counter)>=counter,
       counter,
       -1) as prec
    from t1 order by id
 ) t2 where prec<>-1;

except here I need the 99999 as a max value for your column and there's maybe a way to put the initialisation of @prec to NULL somewhere in the 1st request.

Here the prec column contains the 1st row value counter, and then the counter value of each row if it less than the one from previous row, and -1 when this becomes false.

Update

The outer select can be removed completely if the variable assignment is done in the WHERE clause:

SELECT @prec := NULL;
SELECT
  id,
  counter
FROM t1
WHERE
  (@prec := IF(
    IFNULL(@prec, counter) >= counter,
    counter,
    -1
  )) IS NOT NULL
  AND @prec <> -1
ORDER BY id;

regilero EDIT: I can remove the 1st initialization query using a temporary table (left join) of 1 row this way: but this may slow down the query, maybe.

(...)
FROM t1 
LEFT JOIN (select @prec:=NULL as nullinit limit 1) as tmp1 ON tmp1.nullinit is null
(..)

As said by @Mike using a simple UNION query or even :

(...)
FROM t1 , (select @prec:=NULL) tmp1 
(...)

is better if you want to avoid the first query.

So at the end the nicest solution is:

SELECT NULL AS id, NULL AS counter FROM dual WHERE (@prec := NULL) 
UNION
SELECT   id,   counter
FROM t1  
WHERE   (
   @prec := IF(
      IFNULL(@prec, counter) >= counter,
      counter,
      -1   )) IS NOT NULL
AND @prec <> -1
ORDER BY id;

+--------+---------+
| id     | counter |
+--------+---------+
| 353215 |      20 |
| 523532 |      10 |
| 535325 |      15 |
+--------+---------+

EXPLAIN SELECT output:

+----+--------------+------------+------+---------------+------+---------+------+------+------------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+--------------+------------+------+---------------+------+---------+------+------+------------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE | 
|  2 | UNION        | t1         | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where      | 
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using filesort   | 
+----+--------------+------------+------+---------------+------+---------+------+------+------------------+

You didn't find a solution because it is impossible.

SQL works only within a row, it can not look at rows above or below it.

You could write a stored procedure to do this, essentially looping one row at a time and calculating the logic.

It would probably be easier to write it in the frontend language, whatever it is you are using.

I'm afraid you can't do it in SQL. Relational databases were designed for different purpose so there is no abstraction like next or previous row. Do it outside the SQL in the 'wrapping' language.

I'm not sure whether these do what you want, and they're probably too slow anyway:

SELECT t1.col1, t1.col2
  FROM tbl t1
  WHERE t1.col2 = (SELECT MIN(t2.col2) FROM tbl t2 WHERE t2.col1 <= t1.col1)

Or

SELECT t1.col1, t1.col2
  FROM tbl t1
  INNER JOIN tbl t2 ON t2.col1 <= t1.col1
  GROUP BY t1.col1, t1.col2
  HAVING t1.col2 = MIN(t2.col2)

I guess you could maybe select them (in order) into a temporary table, that also has an auto-incrementing column, and then select from the temporary table, joining on to itself based on the auto-incrementing column (id), but where t1.id = t2.id + 1, and then use the where criteria (and appropriate order by and limit 1) to find the t1.id of the row where the descending column is greater in t2 than in t1. After which, you can select from the temporary table where the id is less than or equal to the id that you just found. It's not exactly pretty though! :)

It is actually possible, but the performance isn't easy to optimize. If Col1 is ordered and Col2 is the descending column:

First you create a self join of each row with the next row (note that this only works if the column value is unique, if not you need to join on unique values).

(Select Col1, (Select Min(Col2) as A2 from MyTable as B Where B.A2>A.Col1)  As Col1FromNextRow From MyTable As A) As D
INNER JOIN
(Select Col1 As C1,Col2 From MyTable As C On C.C1=D.Col1FromNextRow)

Then you implement the "keep going until the first ascending value" bit:

Select Col2 FROM
(
(Select Col1, (Select Min(Col2) as A2 from MyTable as B Where B.A2>A.Col1)  As Col1FromNextRow From MyTable As A) As D
INNER JOIN
(Select Col1 As C1,Col2 From MyTable As C On C.C1=D.Col1FromNextRow)
) As E
WHERE NOT EXISTS
(SELECT Col1 FROM MyTable As Z Where z.COL1<E.Col1 and Z.Col2 < E.Col2)

I don't have an environment to test this, so it probably has bugs. My apologies, but hopefully the idea is semi clear.

I would still try to do it outside of SQL.