SQLite Forum

A little help with temp tables?
Login

A little help with temp tables?

(1) By David Jackson (davidjackson) on 2021-09-30 04:11:21 [link] [source]

What am I missing here:

CREATE TEMP TABLE hello ( SELECT ProductId, UnitPrice, SUM(Quantity) AS Qty FROM S GROUP BY ProductId ORDER BY Qty LIMIT 5 );

TX David

(2) By Stephan Beal (stephan) on 2021-09-30 04:18:26 in reply to 1 [source]

CREATE TEMP TABLE hello ( SELECT ProductI...

sqlite> create temp table foo as select * from event limit 3;
sqlite> select * from foo;
'ci',2454303.0902662039734,1,NULL,NULL,NULL,NULL,'drh',NULL,'initial empty baseline',NULL,2454303.0902662039734
'ci',2454304.0000694449991,181,NULL,NULL,NULL,NULL,'drh',NULL,'Setup webpag updates.',NULL,2454304.0000694449991
'ci',2454303.3139583338051,182,NULL,NULL,NULL,NULL,'drh',NULL,'Improvements to web-based user management.',NULL,2454303.3139583338051

(3.1) By Ryan Smith (cuz) on 2021-09-30 07:15:04 edited from 3.0 in reply to 1 [link] [source]

You are missing some correct syntax only, the idea is fine.

Try:

CREATE TEMP TABLE hello AS
  SELECT ProductId, UnitPrice, SUM(Quantity) AS Qty
    FROM S
   GROUP BY ProductId
   ORDER BY 3
   LIMIT 5
;

EDIT: Not sure "ORDER BY Qty" would work since it's an alias, it works in some engines. To be safe I changed it to "ORDER BY 3" (to order by the third column) or you could use "ORDER BY SUM(Quantity)" too.

This way of creating tables is quite useful, but note that performance is a slave to the elements.

If you need it to be fast for querying, perhaps using some unique key, then pre-creating the table with relevant keys and filling it is best, and it can be achieved in almost the same way with a bit more verbose SQL.

Example:

CREATE TEMP TABLE hello(
  ProductId TEXT NOT NULL COLLATE NOCASE PRIMARY KEY,
  UnitPrice REAL,
  Qty REAL
);

INSERT INTO hello
SELECT ProductId, UnitPrice, SUM(Quantity)
  FROM S
 GROUP BY ProductId
 LIMIT 5
;

EDIT: Removed the Order-By in the second example as it has no effect since the table will be ordered according to the primary key. Adding a second Index on Qty will make ordering the output by Qty a lot faster - which is a good example reason for doing it this way.