Top and Bottom in Same Table - 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 2008 Forums

Transact-SQL (2008)

Top and Bottom in Same Table

Author

Topic

ross_gt
Starting Member

23 Posts

Posted - 2011-05-31 : 15:49:12

I am trying to do the top 20 and bottom 20 skus. I figured a union was appropriate however I get this message

Msg 116, Level 16, State 1, Line 16
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

USE Customer_Analytics

SELECT DISTINCT([SKU Number]), [Extended Sales PMAR Amount]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted]
WHERE [Extended Sales PMAR Amount] IN
(SELECT TOP(20)[SKU Number], [Extended Sales PMAR Amount]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted]
WHERE [Extended Sales PMAR Amount] >= '20100101' AND [Extended Sales PMAR Amount] < '20110101'
ORDER BY [Extended Sales PMAR Amount] ASC

UNION ALL

SELECT TOP(20)[SKU Number], [Extended Sales PMAR Amount]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted]
WHERE [Extended Sales PMAR Amount] >= '20100101' AND [Extended Sales PMAR Amount] < '20110101'
ORDER BY [Extended Sales PMAR Amount] DESC);

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-31 : 16:03:38

The inner query should return only one column.


SELECT DISTINCT([SKU Number]), [Extended Sales PMAR Amount]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted]
WHERE [Extended Sales PMAR Amount] IN
(SELECT TOP(20)[SKU Number], [Extended Sales PMAR Amount]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted]
WHERE [Extended Sales PMAR Amount] >= '20100101' AND [Extended Sales PMAR Amount] < '20110101'
ORDER BY [Extended Sales PMAR Amount] ASC

UNION ALL

SELECT TOP(20)[SKU Number], [Extended Sales PMAR Amount]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted]
WHERE [Extended Sales PMAR Amount] >= '20100101' AND [Extended Sales PMAR Amount] < '20110101'
ORDER BY [Extended Sales PMAR Amount] DESC);

ross_gt
Starting Member

23 Posts

Posted - 2011-05-31 : 17:18:12

I now receive this message: Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '('.

USE Customer_Analytics

SELECT '2011 YTD Revenue' AS Canada, [SHIPTO], [SGN], [NSGN]
FROM
(SELECT A.[Extended Sales PMAR Amount], A.[Country of Ultimate Destination Code], B.[SGN_Number], B.[NSGN_Name], B.[COUNTRY]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted] AS A
INNER JOIN [ReferenceData].[dbo].[AB] AS B
ON B.[COUNTRY] LIKE N'%Canada' AND A.[Country of Ultimate Destination Code] LIKE N'%Canada%'
AND A.[Order Date (YYYYMMDD)] >= '20110101' AND B.[SHIP_TO_CREATED_DATE] >= '20110101') AS SourceTable
PIVOT

(SUM(CAST([Extended Sales PMAR Amount] AS FLOAT))
FOR [Country of Ultimate Destination Code] IN ([SHIPTO],[SGN], [NSGN])) AS PivotTable;

ross_gt
Starting Member

23 Posts

Posted - 2011-05-31 : 17:24:24

Posted in wrong topic. sorry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources