Question 201276 in Mysql Question posted in
The official MySQL documentation can be found here.

I used MySQL, I need to convert this code to SQLite and check it on the FIDDLE engine

I need to convert this code to SQLite and check it on the FIDDLE engine.
I replaced the window function, I checked it in MySQL – it works, not in SQLite, tell me what else to do?

 WITH p as (  WITH e AS (
 SELECT  s.date AS dateS, e.date AS dateE
 from ev s 
 JOIN ev e 
 ON s.cid = e.cid  
) 
  SELECT t2.`dateSS` as dateStart, t2.`dateEE` as dateEnd, count(*) all_count
  FROM    (
     SELECT 
      (SELECT date FROM ev t1
       WHERE t1.date < ev.date
          OR t1.date = ev.date AND t1.id < ev.id
       ORDER BY date DESC, ID DESC LIMIT 1 ) dateSS,
     date as dateEE 
FROM ev
order by date   
        ) t2
JOIN e
        ON  t2.`dateSS` <   e.`dateE` 
        AND t2.`dateEE` >  e.`dateS`  
        GROUP BY   t2.`dateSS`, t2.`dateEE`
)
(SELECT
          dateStart, 
          dateEnd,
          all_count
FROM p 
WHERE all_count=(SELECT MAX(all_count) FROM p)
ORDER BY dateStart ASC LIMIT 1)
UNION
(SELECT
          dateStart, 
          dateEnd,
          all_count
FROM p 
WHERE all_count=(SELECT MAX(all_count) FROM p)
ORDER BY dateStart DESC LIMIT 1);
Tags:

2

Answers


  1. 0 
    WITH p AS (
WITH e AS (
SELECT s.date AS dateS, e.date AS dateE
FROM ev s
JOIN ev e ON s.cid = e.cid)
SELECT t2.dateSS AS dateStart, t2.dateEE AS dateEnd, COUNT(*) AS all_count
FROM (SELECT (SELECT date 
              FROM ev t1
              WHERE t1.date < ev.date
              OR t1.date = ev.date AND t1.id < ev.id
              ORDER BY date DESC, ID DESC LIMIT 1) AS dateSS, date AS dateEE
FROM ev
ORDER BY date) t2
JOIN e ON t2.dateSS < e.dateE AND t2.dateEE > e.dateS
GROUP BY t2.dateSS, t2.dateEE)
SELECT dateStart, dateEnd, all_count
FROM p
WHERE all_count = (SELECT MAX(all_count) 
                   FROM p)
ORDER BY dateStart ASC LIMIT 1
UNION
SELECT dateStart, dateEnd, all_count
FROM p
WHERE all_count = (SELECT MAX(all_count) 
                   FROM p)
ORDER BY dateStart DESC LIMIT 1;

    In SQLite, the changes from the original MySQL query are as follows:

    1. Removed the backticks (`) around column aliases.
    2. Removed the aliasing of COUNT(*) as all_count in the main SELECT clause.
    3. Removed the JOIN keyword before the WITH clause name.
    4. Replaced the INNER JOIN syntax with JOIN syntax.
    5. Removed the ORDER BY clause in the innermost subquery because it’s unnecessary in SQLite.
    6. Added the ASC keyword after the ORDER BY dateStart in the first subquery of the UNION.
    7. Removed the ORDER BY clause in the second subquery of the UNION because it’s unnecessary in SQLite.
    Login or Signup to reply.
  2. 0

    To convert the given code to SQLite, you need to make a few adjustments since SQLite does not support the same syntax as MySQL for some operations, such as the use of window functions. Here’s the modified code that should work in SQLite:

    WITH e AS (
  SELECT s.date AS dateS, e.date AS dateE
  FROM ev s
  JOIN ev e ON s.cid = e.cid
),
p AS (
  SELECT t2.dateSS AS dateStart, t2.dateEE AS dateEnd, COUNT(*) AS all_count
  FROM (
    SELECT (
      SELECT date
      FROM ev t1
      WHERE t1.date < ev.date OR (t1.date = ev.date AND t1.id < ev.id)
      ORDER BY date DESC, id DESC
      LIMIT 1
    ) AS dateSS, date AS dateEE
    FROM ev
    ORDER BY date
  ) AS t2
  JOIN e ON t2.dateSS < e.dateE AND t2.dateEE > e.dateS
  GROUP BY t2.dateSS, t2.dateEE
)
SELECT dateStart, dateEnd, all_count
FROM p
WHERE all_count = (
  SELECT MAX(all_count)
  FROM p
)
ORDER BY dateStart ASC
LIMIT 1

UNION

SELECT dateStart, dateEnd, all_count
FROM p
WHERE all_count = (
  SELECT MAX(all_count)
  FROM p
)
ORDER BY dateStart DESC
LIMIT 1;

    Please note that the code assumes you have a table named ev with columns cid, date, and id in your SQLite database. Make sure to replace ev with the actual table name in your database.

    You can test this code on the SQLite Fiddle engine or any other SQLite environment to verify if it produces the desired results. Thank you!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search