Selecting Rows Where Remainder (Modulo) Is 1 After Division by 2

Select Rows with id having even number

You are not using Oracle, so you should be using the modulus operator:

SELECT * FROM Orders where OrderID % 2 = 0;

The MOD() function exists in Oracle, which is the source of your confusion.

Have a look at this SO question which discusses your problem.

count of rows which are not divided by a number

You are asking for the remainder when an integer is divided by 48. That has a specific name in mathematics, the modulo operation. This is actually a very interesting (to some people) part of number theory and group theory.

Most databases support the modulo operator or function via %. So the idea is:

select value
from t
where value % 48 = 0;

This might be:

where value mod 48 = 0
where mod(value, 48) = 0

depending on the database.

Another method is:

where floor(val / 48) * 48 = val

sql custom select

SQL Server/MySQL/Postgresql/SQLite version using modulo division:

SELECT *
FROM your_table
WHERE id % 2 = 1;

SELECT *
FROM your_table
WHERE id % 3 = 1;

As jarlh mentioned in comment there is MOD(id, x) operator in standard SQL (not working in SQL Server/SQLite)

Do a SELECT in MYSQL to return the remainder of an ID COUNT/8

Use MOD function

Query for you example

SELECT COUNT(1) MOD 8 AS result
FROM `table`
WHERE id=5

Query for common example

SELECT id, COUNT(1) MOD 8 AS result
FROM `table`
GROUP BY id

Selecting first N rows, where sum of lengths of TEXT field is up to some limit

You can do it with a smart recursive CTE:

WITH RECURSIVE c AS ( -- 1st CTE is not recursive
   SELECT dense_rank()  OVER (ORDER BY     source, target, profile)             AS rnk
        , row_number()  OVER (PARTITION BY source, target, profile ORDER BY id) AS rn
        , lead(encoded) OVER (PARTITION BY source, target, profile ORDER BY id) AS next_enc
        , id, encoded
   FROM   cache
   )

 , rcte AS (  -- "recursion" starts here
   SELECT rnk, rn, ARRAY[id] AS ids, encoded AS url
        , CASE WHEN length(concat_ws('&q=', encoded || next_enc)) > 2000  -- max len
                 OR next_enc IS NULL  -- last in partition
               THEN TRUE END AS print
   FROM   c
   WHERE  rn = 1

   UNION ALL
   SELECT c.rnk, c.rn
        , CASE WHEN r.print THEN ARRAY[id] ELSE r.ids || c.id                      END AS ids
        , CASE WHEN r.print THEN c.encoded ELSE concat_ws('&q=', r.url, c.encoded) END AS url
        , CASE WHEN length(
             CASE WHEN r.print THEN concat_ws('&q=', c.encoded, c.next_enc)
                  ELSE concat_ws('&q=', r.url, c.encoded, c.next_enc) END) > 2000  -- max len
                 OR c.next_enc IS NULL  -- last in partition
               THEN TRUE END AS print
   FROM   rcte r
   JOIN        c USING (rnk)
   WHERE  c.rn = r.rn + 1
   )
SELECT ids, url
FROM   rcte
WHERE  print
ORDER  BY rnk, rn;

About the rCTE including a non-recursive CTE:

• Multiple CTE in single query

But this is probably one of the rare cases where looping in a plpgsql function is actually faster.

See this related answer for more explanation:

• Grouping or Window

How to find nearest divisor to given value with modulo zero

Looking for the largest divisor is not a good approach because of two reasons.

1. The size of array might be prime number.
2. The divisor may be too large or too small resulting in ineffective learning.

The better idea is to pad dataset with samples randomly selected from the whole dataset to make it divisible by optimal batch size. Here is the simple trick to compute the size of padded array divisible by 1440

(-x.shape[0] % 1440) + x.shape[0]

However, when data is ordered (like time series) then padding cannot be used because there no way to construct representative content of padding data.

The alternative solution would be minimization of truncated data. One can search through a range a available padding to find requires minimal truncation.

def find_best_divisor(size, low, high, step=1):
    minimal_truncation, best_divisor = min((size % divisor, divisor)
        for divisor in range(low, high, step))
    return best_divisor

This approach is nice because it allows to utilize data well and use padding suitable for training.

Using Cast as Decimal and Mod in Select

By checking your query there is an unnecessary parenthesis. So I removed it. I've also modified it on how you will get the value of divCalc. I CAST both the numerator and the denominator and I put it in CASE statement to prevent the SQL Error when the value ofwf.reminderFrequency is zero. Also I used the DATEDIFF function to get the modCalc. See below:

SELECT 
      wfi.WebFormsInstanceID, 
      wfi.WebFormsIndexID, 
      wfi.FormStage,
      wfi.FormAction, 
      wfi.FormActionDate, 
      wf.WebFormsIndexID, 
      wf.reminderFrequency,
      CASE WHEN CAST(wf.reminderFrequency AS DECIMAL(18,1))=0.0 THEN 0.0 ELSE CAST(wfi.formActionDate AS DECIMAL(18,1)) / CAST(wf.reminderFrequency AS DECIMAL(18,1)) END as divCalc, 
     DATEDIFF(DAY, '2000-01-01', CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE)%wf.reminderFrequency as modCalc
      FROM webFormsInstances as wfi
LEFT OUTER JOIN WebFormsIndex as wf ON wfi.WebFormsIndexID = wf.WebFormsIndexID
WHERE (wfi.formStage <> 'Complete' AND wfi.FormStage <> 'Terminated') 
AND wfi.formActionDate < CONVERT(int, CONVERT(varchar(8), dateAdd(day,-14, getdate()), 112))
ORDER BY wfi.WebFormsInstanceID DESC;

---

Related Topics