SQL Server: How to Hide ORDER BY Column in Results

pivotsql server

I'm quite new to SQL programming, so i apologize in Advance for all the irrelevant Information and stupid Questions 🙂

I have a database in Microsoft SQL Server Management Studio.

I select data from multiple tables in the database according to different criterias. I join these datas toigether with UNION. Because UNION has its own order, I also created a column in each SELECT statement called filtering, and then at the end of the UNION I ORDERed the data BY this column> filtering.
Then I insert my results into a table I have previously created. Until now everything functions perfectly.
Then I close the statement, and start a new one with basically almost the same information. (Only the weekday changes from 21 to 22). I also insert these records into the new table, and there I have my "error".

Becuase of the ORDER BY column (filtering) these records are inserted into row 4, 5 and 6, instead of 1, 2 and 3.

Is there a waz to correct this, for example not to display the filtering column, or JOIN by the filtering column?

As I said I'm quite new to SQL (started learning it about about 2 Weeks ago) any help is highly appreciated!

My code and the results 

DELETE FROM [Test].[dbo].[Übersicht_Woche_RAP]
INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W21, id)
SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
ORDER BY filtering;

INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W22, id)
SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
UNION
SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
UNION
SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
ORDER BY filtering

enter image description here

Best Answer

The variant with direct separate SELECTs:

WITH
cte21 AS (SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
          UNION
          SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 21)
          UNION
          SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, 
          BuchDatum) = 21)
         ),
cte22 AS (SELECT SUM(try_convert(numeric(38, 5), Rüsten_Ist)) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
          UNION
          SELECT SUM(ISNULL(try_convert(numeric(38, 5), Arb__Plan), 0) + ISNULL(try_convert(numeric(38, 5), Masch__Ist), 0) + ISNULL(try_convert(numeric(38, 5), IstArb), 0)) AS B, 2 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = 22)
          UNION
          SELECT SUM(try_convert(numeric(38, 5), Ausschuß)) AS C, 4 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, 
          BuchDatum) = 22)
         )
INSERT INTO [Test].[dbo].[Übersicht_Woche_RAP] (W21, W22, id)
SELECT cte21.Rüsten, cte22.Rüsten, cte21.filtering
FROM cte21
JOIN cte22 ON cte21.filtering = cte22.filtering

But you can create more compact version by create cte AS (SELECT 21 weeknumber UNION SELECT 22) and cross apply it to formalized version of your query (add GROUP BY and wrap values with MAX(), or use PIVOT).

Related Question