Postgresql : Check if the last number is the highest

Question

I have large database and one field should be an incremental number, but it sometimes resets and I must detect them (the bold rows)

Table 1:
Shop    #Sell       DATE
EC1         56          1/10/2015
EC1         57          2/10/2015
**EC1           11          3/10/2015
EC1         12          4/10/2015**
AS2         20          1/10/2015
AS2         21          2/10/2015
AS2         22          3/10/2015
AS2         23          4/10/2015

To solve this problem I thought to find the highest number of each SHOP and check if it is the number with the highest DATE. Do you know another easier way to do it?

My concern is that it can be a problem to do the way I am thinking since I have a large database.

Do you know how I can do the query I am thinking of or do you have any others ideas?

Answer 1

I think the following is the type of query you want:

select s.*
from (select shop, max(sell) as maxsell, 
             first_value(sell) over (partition by shop order by date desc) as lastsell
      from shops s
      group by shop
     ) s
where maxsell <> lastsell;

Answer 2

The query you have in mind will give you all Shop values having a discontinuity in Sell number.

If you want to get the offending record you can use the following query:

SELECT Shop, Sell, DATE
FROM (
 SELECT Shop, Sell, DATE,
        LAG(Sell) OVER (PARTITION BY Shop ORDER BY DATE) AS prevSell
 FROM Shops ) t
 WHERE Sell < prevSell
ORDER BY DATE 
LIMIT 1

The above query will return the first discontinuity found within each Shop partition.

Output:

Shop    Sell    DATE
---------------------
EC1     11      2015-03-10

Demo here

EDIT:

In case you cannot use windowed function and you only want the id of the shop having the discontinuity, then you can use the following query:

SELECT s.Shop
FROM Shops AS s
INNER JOIN (
   SELECT Shop, MAX(Sell) AS Sell, MAX(DATE) AS DATE
   FROM Shops
   GROUP BY Shop ) t 
ON s.Shop = t.Shop AND s.DATE = t.DATE
WHERE t.Sell <> s.Sell

The above will work provided that you have unique DATE values per Shop.