inserting into table using union and order by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

inserting into table using union and order by

Author

Topic

AndyC
Yak Posting Veteran

53 Posts

Posted - 2009-12-31 : 19:32:05

Hi

I'm trying to insert rows into a table using both union and order by (see below) but am running into an error 'Incorrect syntax near the keyword 'Union'.' Removing the order by obviates the problem but does not produce the data I am after

There is no problem creating a view but doing a simple select from that takes an age cf selecting from a table

Any suggestions appreciated


insert into tblTemp
SELECT  top 1  tblPlayers.PLAYERID, Count(tblGoals.TIME) AS Tot,'1993/4' as Season,
CASE when tblPlayers.FIRSTNAME is null then tblPlayers.LASTNAME else tblPlayers.FIRSTNAME + ' ' + tblPlayers.LASTNAME end as name

FROM (((tblMatch INNER JOIN tblMatchTeam ON (tblMatch.MATCHID = tblMatchTeam.MATCHID) AND (tblMatch.MATCHID = tblMatchTeam.MATCHID)) INNER JOIN (tblPlayer_Match INNER JOIN tblGoals ON (tblPlayer_Match.PLAYER_MATCH = tblGoals.PLAYER_MATCH) AND (tblPlayer_Match.PLAYER_MATCH = tblGoals.PLAYER_MATCH)) ON tblMatchTeam.TEAMMATCHID = tblPlayer_Match.TEAMMATCHID) INNER JOIN tblPlayerClub ON tblPlayer_Match.PLAYER_TEAM = tblPlayerClub.PLAYER_TEAM) INNER JOIN tblPlayers ON tblPlayerClub.PLAYERID = tblPlayers.PLAYERID
WHERE tblMatch.DATE>'8/1/1993'  and tblMatch.DATE < RIGHT('0' + CAST(Month(getDate()) AS varchar(3)), 2)+'/'+RIGHT('0' + CAST(Day(getDate()) AS varchar(3)), 2)+'/'+CAST(Year(getDate())-16 AS varchar(4))
GROUP BY tblPlayers.PLAYERID,tblPlayers.FIRSTNAME,tblPlayers.LASTNAME

ORDER BY Count(tblGoals.TIME) DESC

Union

SELECT  top 1  tblPlayers.PLAYERID, Count(tblGoals.TIME) AS Tot,'1992/3' as Season,
CASE when tblPlayers.FIRSTNAME is null then tblPlayers.LASTNAME else tblPlayers.FIRSTNAME + ' ' + tblPlayers.LASTNAME end as name

FROM (((tblMatch INNER JOIN tblMatchTeam ON (tblMatch.MATCHID = tblMatchTeam.MATCHID) AND (tblMatch.MATCHID = tblMatchTeam.MATCHID)) INNER JOIN (tblPlayer_Match INNER JOIN tblGoals ON (tblPlayer_Match.PLAYER_MATCH = tblGoals.PLAYER_MATCH) AND (tblPlayer_Match.PLAYER_MATCH = tblGoals.PLAYER_MATCH)) ON tblMatchTeam.TEAMMATCHID = tblPlayer_Match.TEAMMATCHID) INNER JOIN tblPlayerClub ON tblPlayer_Match.PLAYER_TEAM = tblPlayerClub.PLAYER_TEAM) INNER JOIN tblPlayers ON tblPlayerClub.PLAYERID = tblPlayers.PLAYERID
WHERE tblMatch.DATE>'8/1/1992'  and tblMatch.DATE < RIGHT('0' + CAST(Month(getDate()) AS varchar(3)), 2)+'/'+RIGHT('0' + CAST(Day(getDate()) AS varchar(3)), 2)+'/'+CAST(Year(getDate())-17 AS varchar(4))
GROUP BY tblPlayers.PLAYERID,tblPlayers.FIRSTNAME,tblPlayers.LASTNAME

ORDER BY Count(tblGoals.TIME) DESC
GO

Andrew Clark

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-12-31 : 23:25:49

Use two INSERT statements to get around this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-01-01 : 08:34:12

Thanks for the reply

Presumably you mean adding a line

INSERT into tblTemp

immediately prior to the second SELECT statement

This results in same error

As I mentioned, I get two rows inserted correctly with the original code
provided there is no ORDER BY clause

Andrew Clark

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 09:21:32

Try this:


insert into tblTemp
select * from
(
SELECT  top 1  tblPlayers.PLAYERID, Count(tblGoals.TIME) AS Tot,'1993/4' as Season,
CASE when tblPlayers.FIRSTNAME is null then tblPlayers.LASTNAME else tblPlayers.FIRSTNAME + ' ' + tblPlayers.LASTNAME end as name

FROM (((tblMatch INNER JOIN tblMatchTeam ON (tblMatch.MATCHID = tblMatchTeam.MATCHID) AND (tblMatch.MATCHID = tblMatchTeam.MATCHID)) INNER JOIN (tblPlayer_Match INNER JOIN tblGoals ON (tblPlayer_Match.PLAYER_MATCH = tblGoals.PLAYER_MATCH) AND (tblPlayer_Match.PLAYER_MATCH = tblGoals.PLAYER_MATCH)) ON tblMatchTeam.TEAMMATCHID = tblPlayer_Match.TEAMMATCHID) INNER JOIN tblPlayerClub ON tblPlayer_Match.PLAYER_TEAM = tblPlayerClub.PLAYER_TEAM) INNER JOIN tblPlayers ON tblPlayerClub.PLAYERID = tblPlayers.PLAYERID
WHERE tblMatch.DATE>'8/1/1993'  and tblMatch.DATE < RIGHT('0' + CAST(Month(getDate()) AS varchar(3)), 2)+'/'+RIGHT('0' + CAST(Day(getDate()) AS varchar(3)), 2)+'/'+CAST(Year(getDate())-16 AS varchar(4))
GROUP BY tblPlayers.PLAYERID,tblPlayers.FIRSTNAME,tblPlayers.LASTNAME

ORDER BY Count(tblGoals.TIME) DESC
)t1
Union
select * from
(
SELECT  top 1  tblPlayers.PLAYERID, Count(tblGoals.TIME) AS Tot,'1992/3' as Season,
CASE when tblPlayers.FIRSTNAME is null then tblPlayers.LASTNAME else tblPlayers.FIRSTNAME + ' ' + tblPlayers.LASTNAME end as name

FROM (((tblMatch INNER JOIN tblMatchTeam ON (tblMatch.MATCHID = tblMatchTeam.MATCHID) AND (tblMatch.MATCHID = tblMatchTeam.MATCHID)) INNER JOIN (tblPlayer_Match INNER JOIN tblGoals ON (tblPlayer_Match.PLAYER_MATCH = tblGoals.PLAYER_MATCH) AND (tblPlayer_Match.PLAYER_MATCH = tblGoals.PLAYER_MATCH)) ON tblMatchTeam.TEAMMATCHID = tblPlayer_Match.TEAMMATCHID) INNER JOIN tblPlayerClub ON tblPlayer_Match.PLAYER_TEAM = tblPlayerClub.PLAYER_TEAM) INNER JOIN tblPlayers ON tblPlayerClub.PLAYERID = tblPlayers.PLAYERID
WHERE tblMatch.DATE>'8/1/1992'  and tblMatch.DATE < RIGHT('0' + CAST(Month(getDate()) AS varchar(3)), 2)+'/'+RIGHT('0' + CAST(Day(getDate()) AS varchar(3)), 2)+'/'+CAST(Year(getDate())-17 AS varchar(4))
GROUP BY tblPlayers.PLAYERID,tblPlayers.FIRSTNAME,tblPlayers.LASTNAME

ORDER BY Count(tblGoals.TIME) DESC
)t2

No, you're never too old to Yak'n'Roll if you're too young to die.

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-01-01 : 11:02:03

Thanks very much Yak
That worked a treat
Is this documented somewhere or did you just fathom it out by yourself

Andrew Clark

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-01 : 11:41:34

Years of reading questions and solutions and a job migrating data from legacy systems to new systems

No, you're never too old to Yak'n'Roll if you're too young to die.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-01 : 13:40:51

quote:
Originally posted by AndyC

Thanks for the reply

Presumably you mean adding a line

INSERT into tblTemp

immediately prior to the second SELECT statement

This results in same error

As I mentioned, I get two rows inserted correctly with the original code
provided there is no ORDER BY clause

Andrew Clark

I meant remove the UNION. You'd have INSERT/SELECT/ORDER BY twice.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-01-05 : 13:35:41

Tx Cinderella

Andrew Clark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources