Solved

find next higher number based on certain where-conditions (SQL)

  • 16 September 2020
  • 1 reply
  • 96 views

Userlevel 3
Badge +7

Hi all,

sorry, this is more of a general SQL question.

 

I have to find the next higher sequence-number depending on certain where-conditions:

Table-example:

╔═══════╦══════════════════╦═══════════════════════════╗
║  Seq    ║       Start_Time       ║             Queue           ║
╠═══════╬══════════════════╬═══════════════════════════╣
║ 34962 ║ 28.07.2020 17:06 ║ PQ_NEW PRICE REQUEST GMDM ║
║ 35393 ║ 29.07.2020 11:03 ║                           ║
║ 35394 ║ 29.07.2020 11:03 ║                           ║
║ 42886 ║ 04.09.2020 14:16 ║ PQ_NEW PRICE REQUEST GMDM ║
║ 42887 ║ 04.09.2020 14:16 ║ PQ_NEW PRICE REQUEST GMDM ║
║ 42888 ║ 04.09.2020 14:16 ║                           ║
║ 42889 ║ 04.09.2020 14:16 ║                           ║
║ 42890 ║ 04.09.2020 14:17 ║ PQ_COST SWEDEN            ║
║ 42891 ║ 04.09.2020 14:17 ║ PQ_COST SWEDEN            ║
║ 42892 ║ 04.09.2020 14:17 ║                           ║
║ 42893 ║ 04.09.2020 14:17 ║                           ║
║ 42894 ║ 04.09.2020 14:17 ║ PQ_NEW PRICE REQUEST GMDM ║
║ 42895 ║ 04.09.2020 14:17 ║ PQ_NEW PRICE REQUEST GMDM ║
╚═══════╩══════════════════╩═══════════════════════════╝

Example select:

SELECT

start_time

FROM table

WHERE

queue <> 'PQ_NEW PRICE REQUEST GMDM' 

AND seq IS NEXT HIGHER SEQ-VALUE COMPARED TO (SELECT seq 

            FROM table 

            WHERE 

            queue = 'PQ_NEW PRICE REQUEST GMDM'

            AND seq = MIN(seq))

 

Expected result from table for NEXT HIGHER SEQ-VALUE COMPARED TO:

42890

This would be the next higher number where the condition is met, based on the minimum-sequence number and the condition in the sub-select (34962).

 

How can I find exactly the next higher sequence-number under certain where-conditions?

Is there even an oracle-sql-comand? By the way: order by is not an option for the scenario I need it.

 

Thanks a lot!

icon

Best answer by durette 17 September 2020, 00:07

View original

1 reply

Userlevel 7
Badge +18
SELECT MIN(seq)
FROM table
WHERE seq > 34962
AND queue != 'PQ_NEW PRICE REQUEST GMDM'

 

Reply