Having problem with sql create

[Jim R]

The following is a create that I was trying to run in SQL/2000 SQL
Analyser:

CREATE TABLE tblxyz as (Select * from tblPlayers);

tblPlayers has 2 rows in it.

I continue to get the error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

I have tried it with and without the () and it works on Oracle but not
SQL/2K. Don't know why.

This has all developed because of another problem.

First I have select with a complex join all records with a certain
employee ID.

Then I have to select the top 20 (by date) records from the first
select.

From the second select results I have to select the top 10 by a
numeric field.

The only way I thought of doing it was to create temporary tables (see
original question).

[Tyler Hudson]

SELECT * INTO tblxyz FROM tblPlayers

if you want an empty table with only the schema of tblPlayers use a WHERE
1=0

"Jim R" <Ji*****@comcas t.net> wrote in message
news:7e******** *************** ***@posting.goo gle.com...

The following is a create that I was trying to run in SQL/2000 SQL
Analyser:

CREATE TABLE tblxyz as (Select * from tblPlayers);

tblPlayers has 2 rows in it.

I continue to get the error

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'AS'.

I have tried it with and without the () and it works on Oracle but not
SQL/2K. Don't know why.

This has all developed because of another problem.

First I have select with a complex join all records with a certain
employee ID.

Then I have to select the top 20 (by date) records from the first
select.

From the second select results I have to select the top 10 by a
numeric field.

The only way I thought of doing it was to create temporary tables (see
original question).

[David Portas]

> CREATE TABLE tblxyz as (Select * from tblPlayers);

The SQL Server equivalent is

SELECT * INTO tblxyz FROM tblPlayers ;

The only way I thought of doing it was to create temporary tables (see
original question).

Without more of a spec (DDL for the tables, some INSERT statements of sample
data) it's impossible for us to know if that's really the best way to
achieve what you want.

--
David Portas
SQL Server MVP
--

[James Reid]

Thank you. That works great. Now I create tbltemp and from tbltemp I
create a tbltempa and then drop tbltemp. All in a stored procedure.
Then after I'm done with my selection of the recordset I'll drop the
tbltempa.

Thanks again.

Jim
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

SQL is a declarative language. To get the most out of SQL, start by writing
a query that will produce the end result you require rather than thinking of
procedural solutions like [Query 1] -> [Temp Table] -> [Query 2] -> etc.
From your description I doubt that temp tables are the most concise,
efficient or maintainable solution to your problem.

--
David Portas
SQL Server MVP
--

[James Reid]

David,

You are right. My problem was that I didn't know how to create a select
within a select. What I ended up with was just that and it is all in a
single stored procedure with no temporary tables. Works really well.

My biggest problem was all new things I was trying to do. I have always
done simple select statements before both with Views and Stored
Procedures.

Then I discovered that I needed to feed parameters to the first select
which knocked out Views. Then I needed only the top 20 by date and from
the top 20 the lowest 10(numerically) of those top 20.

It got very confusing real fast when I had a stored procedure that
select via param for the top 20. It would pick the top 20 first and
then the parms. Which wasn't the sequence I wanted.

Since I was only running on SQL/2k I ended up with the following stored
procedure:
CREATE PROCEDURE [SelectUSGA1]]
@PlayerID integer
AS
SELECT TOP 10 WITH TIES dbo.tblScores.[Date], dbo.tblScores.P layer,
dbo.tblScores.H andiDiff, dbo.tblScores.T ees, dbo.tblScores.H 1,
dbo.tblScores.H 2, dbo.tblScores.H 3, dbo.tblScores.C ourse,
dbo.tblCourse.C ourseID, dbo.tblCourse.C ourseName, dbo.tblCourse.H ole1,
dbo.tblCourse.H ole2, dbo.tblCourse.H ole3, dbo.tblCourse.H ole4,
dbo.tblCourse.H ole5, dbo.tblCourse.H ole6, dbo.tblCourse.H ole7,
dbo.tblCourse.H ole8, dbo.tblCourse.H ole9, dbo.tblCourse.H ole10,
dbo.tblCourse.H ole11, dbo.tblCourse.H ole12, dbo.tblCourse.H ole13,
dbo.tblCourse.H ole14, dbo.tblCourse.H ole15, dbo.tblCourse.H ole16,
dbo.tblCourse.H ole17, dbo.tblCourse.H ole18, dbo.tblCourse.T ,
dbo.tblCourse.R ating, dbo.tblCourse.S lope, dbo.tblPlayers. Player_ID,
dbo.tblPlayers. PlayerLastName, dbo.tblPlayers. PlayerFirstName ,
dbo.tblPlayers. PlayerNickName, dbo.tblScores.H 4, dbo.tblScores.H 5,
dbo.tblScores.H 6, dbo.tblScores.H 7, dbo.tblScores.H 8, dbo.tblScores.H 9,
dbo.tblScores.H 10, dbo.tblScores.H 11, dbo.tblScores.H 12,
dbo.tblScores.H 13, dbo.tblScores.H 14, dbo.tblScores.H 15,
dbo.tblScores.H 16, dbo.tblScores.H 17, dbo.tblScores.H 18,
dbo.tblScores.B 9, dbo.tblScores.T 18, dbo.tblScores.P 1, dbo.tblScores.F 9,
dbo.tblScores.P 2, dbo.tblScores.P 3, dbo.tblScores.P 4, dbo.tblScores.P 5,
dbo.tblScores.P 6,
dbo.tblScores.P 7, dbo.tblScores.P 8, dbo.tblScores.P 9, dbo.tblScores.P 10,
dbo.tblScores.P 11, dbo.tblScores.P 12, dbo.tblScores.P 13,
dbo.tblScores.P 15, dbo.tblScores.P 14,
dbo.tblScores.P 16, dbo.tblScores.P 17, dbo.tblScores.P 18,
dbo.tblScores.F W1, dbo.tblScores.F W2, dbo.tblScores.F W3,
dbo.tblScores.F W4, dbo.tblScores.F W5, dbo.tblScores.F W6,
dbo.tblScores.F W7, dbo.tblScores.F W8, dbo.tblScores.F W9,
dbo.tblScores.F W10, dbo.tblScores.F W11, dbo.tblScores.F W12,
dbo.tblScores.F W13, dbo.tblScores.F W14, dbo.tblScores.F W15,
dbo.tblScores.F W16, dbo.tblScores.F W17,
dbo.tblScores.F W18, dbo.tblScores.F WT, dbo.tblScores.T otalPutts,
dbo.tblScores.A ctualDiff, dbo.tblScores.T otPars, dbo.tblScores.T otBirds,
dbo.tblScores.T otEagles, dbo.tblScores.T otBogys,
dbo.tblScores.T ot2Bogys, dbo.tblScores.A vgPutts
FROM dbo.tblScores
LEFT OUTER JOIN
dbo.tblCourse ON dbo.tblScores.C ourse = dbo.tblCourse.C ourseID
LEFT OUTER JOIN
dbo.tblPlayers ON dbo.tblScores.P layer =
dbo.tblPlayers. Player_ID
WHERE tblPlayers.Play er_ID=@PlayerID
order by date, HandiDiff;
GO
Works really well now.

Jim
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Looks like you just need date in DESCending order (the default is
ascending).

SELECT TOP 10 WITH TIES
...
ORDER BY date DESC, handidiff

Note that you may or may not want the WITH TIES option. Without it, TOP may
give non-deterministic results - if there are values where the sort columns
are equal (tied) then only some subset of the rows may be shown if the
result would otherwise give more than 10 rows.

--
David Portas
SQL Server MVP
--

I posted another message because with your solution it gave me 10 rows by
date which isn't exactly what I was looking for. I have to selection for a row of golf scores, the most current 20 rows by
date for a particular player. Then I have to select the lowest 10 scores from that subset. Then I use those 10 to calculate the USGA Handicap Index. What I'm getting is the newest 10 by date which may not be the best scores

[James Reid]

Still have a problem with SQL. I have tried the following:

Select top 10 with ties, date, score
From tblScores
Where playerID = 3
Order by date desc, score;

The problem is that it selects the top 10 scores by date when in fact I
want the most current top 20 scores and from the most current top 20 I
want to pick the top 10 scores by lowest score.

So if I had 100 rows I would get the most current 20. From that group I
would select by score the lowest 10.

I'm not sure how TIES works but it's not doing what I wanted.

Any help would be appreciated.

Jim

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[David Portas]

Try this:

SELECT TOP 10 WITH TIES *
FROM
(SELECT TOP 20 WITH TIES
S.[date], S.player, S.handidiff, S.tees, S.h1, S.h2, S.h3, S.course,
C.courseid, C.coursename, C.hole1, C.hole2, C.hole3, C.hole4, C.hole5,
C.hole6, C.hole7, C.hole8, C.hole9, C.hole10, C.hole11, C.hole12,
C.hole13, C.hole14, C.hole15, C.hole16, C.hole17, C.hole18, C.t,
C.rating, C.slope, P.player_id, P.playerlastnam e, P.playerfirstna me,
P.playernicknam e, S.h4, S.h5, S.h6, S.h7, S.h8, S.h9, S.h10, S.h11,
S.h12, S.h13, S.h14, S.h15, S.h16, S.h17, S.h18, S.b9, S.t18,
S.p1, S.f9, S.p2, S.p3, S.p4, S.p5, S.p6, S.p7, S.p8, S.p9, S.p10,
S.p11, S.p12, S.p13, S.p15, S.p14, S.p16, S.p17, S.p18, S.fw1, S.fw2,
S.fw3, S.fw4, S.fw5, S.fw6, S.fw7, S.fw8, S.fw9, S.fw10, S.fw11,
S.fw12, S.fw13, S.fw14, S.fw15, S.fw16, S.fw17, S.fw18, S.fwt,
S.totalputts, S.actualdiff, S.totpars, S.totbirds, S.toteagles,
S.totbogys, S.tot2bogys, S.avgputts
FROM dbo.tblScores AS S
LEFT OUTER JOIN
dbo.tblCourse AS C
ON S.course = C.courseid
LEFT OUTER JOIN
dbo.tblPlayers AS P
ON S.player = P.player_id
WHERE P.player_id = @PlayerID
ORDER BY S.[date] DESC, S.handidiff) AS T
ORDER BY handidiff

--
David Portas
SQL Server MVP
--