Search only mysql

Question

Search only mysql

Navigation

#1 by (2 votes)

1

I'm doing a search in db that needs to return the sum of a field and the last corresponding value, and grouping them to an index

Query

  $stmt = getConn()->query("SELECT senha,movi,SUM(entrada) as entrada,custo FROM estoquemovimento ORDER BY id DESC");
  $items = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC | PDO::FETCH_UNIQUE);

Answer

Array
(
    [NbpsCQAMyc] => Array
        (
            [movi] => Adicionado
            [entrada] => 2.000
            [custo] => 3.00
        )

)

This is all correct, in formatting the array, only the cost is searching for the first record, not the last record

I need the search to return the cost of the last record

php mysql

asked by anonymous 04.07.2018 / 16:57

1 answer

How to display the values of the Cursor using Debugger in Android Studio? Sort sequence numbers from a TXT file

score 2 · Accepted Answer

EDIT # 1

As seen by the questioner, MariaDB ignores ORDER BY in subquery: Why is ORDER BY in a FROM Subquery Ignored?

The solution is to use a LIMIT that fetches all records:

SELECT SUM(entrada) as entrada,custo 
FROM (SELECT * FROM estoquemovimento ORDER BY id DESC LIMIT 999999999) alias_tab

Or a JOIN : LINK

The ORDER BY is applied in the return of your query , that is, in the result. Then he is sorting% of his return, which in this case is only 1 line.

An option is by id , sorting before and doing the select in the result:

SELECT SUM(entrada) as entrada,custo
FROM (SELECT * FROM estoquemovimento ORDER BY id DESC) alias_tab

SQLFiddle Example