The Union of Asteroids - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

The Union of Asteroids

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-01-25 : 12:33:49

I am stuck with a union problem....

My code:

--Top 3 Asteroids by Group by Distance

DROP TABLE [dbo].[Asteroids]
DROP TABLE [dbo].[Top3]
GO

CREATE TABLE [dbo].[Asteroids]
(Number nvarchar (05) NULL,
Designation nvarchar (20) NULL,
Distance_AU decimal(5,3) NULL,
Type nvarchar (20) NULL)
GO

INSERT Asteroids VALUES ('887 ','Alinda ','2.484','Amor 3 ')
INSERT Asteroids VALUES ('1915 ','Quetzalcoatl','2.542','Amor 3 ')
INSERT Asteroids VALUES ('2608 ','Sineca ','2.503','Amor 3 ')
INSERT Asteroids VALUES ('3360 ','1981 VA ','2.468','Apollo 3 ')
INSERT Asteroids VALUES ('4179 ','Toutatis ','2.510','Apollo 3 ')
INSERT Asteroids VALUES ('5836 ','1993 MF ','2.445','Amor 3 ')
INSERT Asteroids VALUES ('6318 ','Conkrite ','2.510','Mars-crosser')
INSERT Asteroids VALUES ('6322 ','1991 CQ ','2.516','Mars-crosser')
INSERT Asteroids VALUES ('6489 ','Golevka ','2.493','Apollo 3 ')
INSERT Asteroids VALUES ('6491 ','1991 OA ','2.509','Amor 3 ')
INSERT Asteroids VALUES ('7092 ','Cadmus ','2.524','Apollo 3 ')
INSERT Asteroids VALUES ('7345 ','Happer ','2.450','Mars-crosser')
INSERT Asteroids VALUES ('8201 ','1994 AH2 ','2.526','Apollo 3 ')
INSERT Asteroids VALUES ('8709 ','Kadlu ','2.534','Mars-crosser')
INSERT Asteroids VALUES ('13551','1992 FL1 ','2.527','Mars-crosser')
INSERT Asteroids VALUES ('16588','1992 ST ','2.554','Mars-crosser')
INSERT Asteroids VALUES ('19356','1997 GH3 ','2.507','Amor 3 ')
INSERT Asteroids VALUES ('30825','1990 TG1 ','2.439','Apollo 3 ')
GO

SELECT * FROM [dbo].[Asteroids]
GROUP BY Type,Number,Designation,Distance_AU
ORDER BY Type,Number,Designation,Distance_AU Desc
GO

SELECT TOP 3 * INTO [dbo.].[Top3]
FROM [dbo].[Asteroids] WHERE Type = 'Amor 3'
UNION ALL
SELECT TOP 3 * INTO [dbo.].[Top3]
FROM [dbo].[Asteroids] WHERE Type = 'Apollo 3 '
UNION ALL
SELECT TOP 3 * INTO [dbo.].[Top3]
GROUP BY Type,Number,Designation,Distance_AU
ORDER BY Type,Number,Designation,Distance_AU Desc
GO

But I get this:

Server: Msg 196, Level 15, State 1, Line 6
SELECT INTO must be the first query in an SQL statement containing a UNION operator.

How else can I get my top 3 asteroids by distance by type together for one report?

Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-25 : 12:39:03

Where are you selecting the last Top 3 from?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-25 : 12:40:49

This works..


DROP TABLE [dbo].[Asteroids] 
DROP TABLE [dbo].[Top3]
GO

CREATE TABLE [dbo].[Asteroids] 
(Number nvarchar (05) NULL,
Designation nvarchar (20) NULL,
Distance_AU decimal(5,3) NULL,
Type nvarchar (20) NULL)
GO

INSERT Asteroids VALUES ('887 ','Alinda ','2.484','Amor 3 ') 
INSERT Asteroids VALUES ('1915 ','Quetzalcoatl','2.542','Amor 3 ') 
INSERT Asteroids VALUES ('2608 ','Sineca ','2.503','Amor 3 ')
INSERT Asteroids VALUES ('3360 ','1981 VA ','2.468','Apollo 3 ')
INSERT Asteroids VALUES ('4179 ','Toutatis ','2.510','Apollo 3 ')
INSERT Asteroids VALUES ('5836 ','1993 MF ','2.445','Amor 3 ')
INSERT Asteroids VALUES ('6318 ','Conkrite ','2.510','Mars-crosser')
INSERT Asteroids VALUES ('6322 ','1991 CQ ','2.516','Mars-crosser')
INSERT Asteroids VALUES ('6489 ','Golevka ','2.493','Apollo 3 ')
INSERT Asteroids VALUES ('6491 ','1991 OA ','2.509','Amor 3 ')
INSERT Asteroids VALUES ('7092 ','Cadmus ','2.524','Apollo 3 ')
INSERT Asteroids VALUES ('7345 ','Happer ','2.450','Mars-crosser')
INSERT Asteroids VALUES ('8201 ','1994 AH2 ','2.526','Apollo 3 ')
INSERT Asteroids VALUES ('8709 ','Kadlu ','2.534','Mars-crosser')
INSERT Asteroids VALUES ('13551','1992 FL1 ','2.527','Mars-crosser')
INSERT Asteroids VALUES ('16588','1992 ST ','2.554','Mars-crosser')
INSERT Asteroids VALUES ('19356','1997 GH3 ','2.507','Amor 3 ')
INSERT Asteroids VALUES ('30825','1990 TG1 ','2.439','Apollo 3 ')
GO

SELECT * FROM [dbo].[Asteroids]
GROUP BY Type,Number,Designation,Distance_AU 
ORDER BY Type,Number,Designation,Distance_AU Desc 
GO

SELECT TOP 3 * INTO [Top3]
FROM [dbo].[Asteroids] WHERE Type = 'Amor 3'
UNION ALL
SELECT TOP 3 *
FROM [dbo].[Asteroids] WHERE Type = 'Apollo 3 '
GROUP BY Type,Number,Designation,Distance_AU 
ORDER BY Type,Number,Designation,Distance_AU Desc 
GO

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2006-01-25 : 13:05:00

Thanks Rick! I see my mistake. Please feel free to hit me in the head with a hammer.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-25 : 13:31:29

Really, there is only one query you need to run:

select * from Asteroids where GonnaDestoryTheEarth = 'Y'

Please be so kind as to post the result set for us, so that we may plan accordingly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources