I have a table with some records, ex:

tblONE(numop, ordop, numsav, dateop)

some records where loaded sequencially, but someothers don't, i wish to get those data report

select * from talONE where numop or ordop or numsav or date is no ordered?

there is a way to get it?

EDIT:

example tblONE

numop ordop  numsav    dateop
3       1      5    1/1/22 00:00:01
3       2      6    1/1/22 00:00:02
3       3      7    1/1/22 00:00:04
12      5      3    5/1/22 00:00:15
12      2      9    5/1/22 00:00:17
12      18     1    5/1/22 00:00:23
12      9      15   5/1/22 00:00:32
12      1      6    5/1/22 00:00:05
23      5      13   10/1/22 00:00:02
23      6      26   10/1/22 00:00:03
23      9      34   10/1/22 00:00:04
23      17     41   10/1/22 00:00:05
435     45     9    13/1/22 00:00:21
435     12     3    13/1/22 00:00:22
435     62     62   13/1/22 00:00:25

numop 3 and 23 are ordered, i don't wanna see them, I want to see just the others (12, 435)

CodePudding user response：

If you do not have an ORDER BY clause then none of the rows are ordered (if they appear to be ordered then it is only coincidence).

From the SELECT documentation:

order_by_clause

Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

CodePudding user response：

You could apply an analytic ranking function to both the ordop and dateop columns:

dense_rank() over (partition by numop order by ordop)
dense_rank() over (partition by numop order by dateop)

and compare the results to look for a discrepancy; you can't use those in a where clause but you could use a subquery:

select numop, ordop, numsav, dateop
from tableone
where numop in (
  select numop
  from (
    select numop,
      dense_rank() over (partition by numop order by ordop) as ord_rnk,
      dense_rank() over (partition by numop order by dateop) as date_rnk
    from tableone
  )
  where ord_rnk != date_rnk
)

or CTEs:

with cte1 (numop, ordop, numsav, dateop, match) as (
  select numop, ordop, numsav, dateop,
  case
    when dense_rank() over (partition by numop order by ordop)
        = dense_rank() over (partition by numop order by dateop)
    then 'Match' else 'Mismatch'
  end
  from tableone
),
cte2 (numop, ordop, numsav, dateop, max_match) as (
  select numop, ordop, numsav, dateop, max(match) over (partition by numop)
  from cte1
)
select numop, ordop, numsav, dateop
from cte2
where max_match = 'Mismatch'

which both return:

db<>fiddle