Trent Trent - 2 months ago 7
SQL Question

SQL Select Count using ordering

I had a few lines of Informix-4GL code which do the following

##
# prepare sql
##

let lv_sql = " select table.idno from table ",
" where table.status != 'X' ",
" and table.idno <= 10 ",
" order by table.idno "
prepare table_sel from lv_sql
declare table_cur cursor for table_sel

##
# loop through results and count them
##

let count = 0

foreach table_cur into ti_num
let count = count + 1
end foreach

display count


So I am getting the total of the rows in the specific table that are less than 10 in the correct order sequence but I need a foreach loop to tally the total

I have a second way of doing it which I prefer

##
# prepare sql
##

let lv_sql = " select count(table.idno) from table ",
" where table.idno in ( ",
" select table.idno from table "
" where table.status != 'X' ",
" and table.idno <= 10 ",
" ) "
prepare table_sel from lv_sql

##
# just get result
##

execute table_sel into count

display count


The problem is that the second solution crashes if I include the order by clause in the where in filter and I need it because it is not always in the correct order. Is there a way to include an order by in situation like this?

Answer

I keep re-reading the question and think the answer is ...

let lv_sql = " select table.idno, count(*) ",
             " from table ",
             " where table.status != 'X' ",
             " and table.idno <= 10 ",
             " order by table.idno "
prepare table_sel from lv_sql
declare table_cur cursor for table_sel

foreach table_cur into l_idno, l_count
    ...
end foreach

... or else as other commenters have said, why does the order matter?