Corresponding Sqlite query syntax for Mysql query

Question

Please help me to find the sqlite equivalent of the below query. Tried searching a lot.

    SET @rownum := 0;
SELECT * FROM (
SELECT @rownum := @rownum+1 AS rank, id
FROM tbl_flight
ORDER BY id DESC
) AS tbl_flight WHERE id = 2

Please explain what you're trying to do (what your query should return) in your question, so that people with similar problems can find this question in the future. (Could also help people that are familiar with sqlite but not mysql to give you an answer.) — Mat
– Mat, Commented Jun 23, 2012 at 8:59

biziclop · Accepted Answer · 2012-06-23 11:12:09Z

2

You can't do this in a single query, but you can use temporary tables and the rowid property of every table:

http://sqlfiddle.com/#!5/f0a1b/8 (You can't run the fiddle twice, you have to always rebuild schema first before running it again)

DROP TABLE IF EXISTS tmp;

CREATE TEMPORARY TABLE tmp AS 
  SELECT id
  FROM tbl_flight
  ORDER BY id DESC;

SELECT tmp.rowid AS rank, tmp.id FROM tmp WHERE id=2;

answered Jun 23, 2012 at 11:12

biziclop

14.6k3 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jake Feasel Over a year ago

Interestingly, if you switch to SQLite (WebSQL) and run your code in that environment, you don't get that I/O error: sqlfiddle.com/#!7/f0a1b/2 Must be something strange with SQL.js

Jake Feasel Over a year ago

Also this version doesn't seem to have that problem: sqlfiddle.com/#!5/f0a1b/10

Collectives™ on Stack Overflow

Corresponding Sqlite query syntax for Mysql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related