November 24, 2008 at 10:35 pm
Can someone please explain to me why this is not working ??
CREATE TABLE test AS
(SELECT A, MAX(B)
FROM tbl
GROUP BY A)
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Cheers 🙂
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
November 24, 2008 at 10:41 pm
Mr J (11/24/2008)
Can someone please explain to me why this is not working ??CREATE TABLE test AS
(SELECT A, MAX(B)
FROM tbl
GROUP BY A)
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Cheers 🙂
Because you can't create a table like that, that's why. You could possibly create a view using that syntax, but my question, first, is what are you trying to accomplish?
November 24, 2008 at 10:45 pm
Because you seem to be confusing the syntax for Creating a Table with the syntax for creating a View.
A table is a data storage object that is defined as a list of columns which consist a name and a data type (and opitonally, some additional column attributes).
A view is a query abstraction object that imitates a table in function (but not in form). A view is defined by a SELECT query statement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 24, 2008 at 10:50 pm
Mr J (11/24/2008)
Can someone please explain to me why this is not working ??CREATE TABLE test AS
(SELECT A, MAX(B)
FROM tbl
GROUP BY A)
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Cheers 🙂
it may be like this
CREATE TABLE test
( A int, B int)
GO
insert into test SELECT A, MAX(B)
FROM tbl
GROUP BY A
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 24, 2008 at 10:51 pm
You mean technothenet lied to me.... the buggers!!
Basically i've been asked to find out the following
SELECT A, B, MAX(C) AS C_max
FROM tbl
GROUP BY A, B
that returns;
A B C
11428
11531
21441
31439
and i wanna remove 1 14 28, because its not the lastest version (31 is). The table uses a composite key based on A,B,C
So i created a temp table that contains A, Max(C) and then do a select not in .....
Hmmm i hope that makes sense ??
Remember
Without Change something sleeps inside of us that seldom awakens, the sleeper must awaken!!
November 24, 2008 at 10:55 pm
Mr J (11/24/2008)
Can someone please explain to me why this is not working ??CREATE TABLE test AS
(SELECT A, MAX(B)
FROM tbl
GROUP BY A)
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Cheers 🙂
First Create table and then insert, otherwise create table with select * into
for more info see SQL BOL for Insert & Select Into statements.
November 24, 2008 at 11:02 pm
I don't know if it is possible for you to Create your table in another way if so this is the other way :
SELECT A, MAX(B) AS B
INTO test
FROM tbl
GROUP BY A
Golbano0o 🙂
November 27, 2008 at 10:21 pm
hi all
this works on mysql. i did that
the sysntax is correct.
create table abc select max(sal) from salary;
here it works man
regards
aswin...
November 28, 2008 at 12:54 am
aswanidutt.dasara (11/27/2008)
this works on mysql. i did thatthe sysntax is correct.
create table abc select max(sal) from salary;
Near as I can figure, this is what you want:
Select Max(sal)
Into abc
From Salary
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply