-1

I am creating a new table that sums the values from the stock table for each item_id and then puts them into the new table. This works fine using this code:

create table total_stock as (
select item_id,
sum (stock) total_stock
from stock_tbl
group by item_id
);

which works fine, adding all the values from the stock table and putting them into the new table, but when I try to order it by item_id it doesn't work. Any help would be appreciated. The error given is a syntax error.

create table total_stock as (
select item_id,
sum (stock) total_stock
from stock_tbl
group by item_id
order by item_id
);
William Robertson
  • 15,273
  • 4
  • 38
  • 44
oliver thomas
  • 63
  • 9
  • 1
    Please do not use tags that do not apply to your question. I removed the database tags as it is unclear which one you are actually using. Please add the tag of *only* the database you are actually using – John Conde Apr 27 '18 at 18:46
  • I think the major question here is, why does the order of the insert matter here? Aside from @Andomar answer (which is correct). – Jacob H Apr 27 '18 at 18:50
  • For my assignment the question wanted them to be returned in order by item_id – oliver thomas Apr 27 '18 at 18:53
  • Tag your question with the database you are using. – Gordon Linoff Apr 27 '18 at 19:30
  • I don't see what `group by` has to do with the question, as (in Oracle at least) the `order by` clause is invalid here regardless. The actual question seems to be *ORDER BY not allowed in CREATE TABLE AS SELECT*. – William Robertson Apr 28 '18 at 12:28

3 Answers3

3

Rows in a database table are stored without any particular order. That is why specifying order by when filling a table doesn't work.

You have to order by when selecting rows from a table, not when entering rows into a table.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • thanks for answering, sorry im quite new. Quick follow up question, its worked however its ordered 1, 10, 11, 12... instead of 1, 2, 3... any chance you could help. sorry if its a stupid question. – oliver thomas Apr 27 '18 at 18:52
  • You can sort strings as integers like `order by cast(item_id as integer)` (see https://stackoverflow.com/questions/11808573/sql-order-string-as-number) But remember MySQL can return table rows in any order it choses if you don't specify an `order by`. It may seem consistent at first but there's no guarantee. – Andomar Apr 27 '18 at 18:54
0

Technically, you can order the rows in a table in most databases. You do this by creating a clustered index on the keys used for the ordering.

Guess what?

select t.*
from t

still doesn't return the rows in order, even in tables that have a clustered index. *At least, there is no guarantee. *SQL result sets are unordered unless an ORDER BY is explicitly included for the outermost SELECT. The only guarantee on the ordering of a result set is to use order by. In your case, that would be:

select item_id, sum(stock) as total_stock
from stock_tbl
group by item_id
order by item_id;

or:

select ts.*
from total_stock ts
order by item_id;

By the way, using a temporary table for this is not recommended unless you have a specific need to materialize the result set.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

Updated You can use Common Table Expression and then use this data to insert into your table, as

with CTE as (
select item_id, sum(stock) total stock
from stock_table
) Insert into total_stock
select * from cte group by item_id order by item_id

This should solve your problem

PrateekJain
  • 1
  • 1
  • 5