RE: ORDER BY with CASE in SELCT DISTINCT

SSC Enthusiast

Points: 141

July 24, 2017 at 7:47 am

#1952371

Sorry for my bad first question, i hope that i follows the rules now😉
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Multisorting','U') IS NOT NULL
DROP TABLE #Multisorting

--===== Create the test table with
GO
/****** Object: Table [dbo].[Multisorting] Script Date: 24/07/2017 15:36:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Multisorting](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [varieteit] [nvarchar](20) NULL,
    [referentie] [nvarchar](20) NULL,
    [stockeerder] [nvarchar](50) NULL,
    [verpak] [nvarchar](10) NULL,
    [pallet] [nvarchar](10) NULL,
    [eenheid] [numeric](6, 2) NULL,
    [aantal] [int] NULL,
    [sorteerverkoop] [nvarchar](1) NULL,
    [klasse] [nvarchar](10) NULL,
    [maat] [nvarchar](10) NULL,
    [kistpal] [int] NULL,
CONSTRAINT [PK_Sorteerpalet] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Multisorting] ON

INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (1, N'CONFERENCE', N'B.F.V.00001', N'BELCOOL', N'PXPLA', N'', CAST(450.00 AS Numeric(6, 2)), 1, NULL, N'3', N'', 1)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (2, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'65/75', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (3, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (4, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (5, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (6, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'', CAST(12.00 AS Numeric(6, 2)), 32, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (7, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'60/70', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (8, N'DURANDEAU', N'HEALT00002', N'BOUSSIER', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'55/65', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (9, N'DURANDEAU', N'HEALT00002', N'COENEN', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'65/75', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (10, N'DURANDEAU', N'HEALT00002', N'BOUSSIER', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (11, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'2', N'60/70', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (12, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'2', N'65/75', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (13, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'2', N'55/65', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (14, N'CONFERENCE', N'B.F.V.00001', N'BELCOOL', N'PXPLA', N'', CAST(450.00 AS Numeric(6, 2)), 1, NULL, N'3', N'', 1)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (15, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'BLOK', CAST(12.00 AS Numeric(6, 2)), 80, NULL, N'2', N'60/70', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (16, N'CONFERENCE', N'B.F.V.00001', N'BELCOOL', N'PXPLA', N'', CAST(450.00 AS Numeric(6, 2)), 1, NULL, N'3', N'', 1)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (19, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'', CAST(12.00 AS Numeric(6, 2)), 14, NULL, N'1', N'70/80', 80)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (20, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'BELDOOS', N'BLOK', CAST(7.00 AS Numeric(6, 2)), 72, NULL, N'A2++', N'75/85', 72)
INSERT [dbo].[Multisorting] ([id], [varieteit], [referentie], [stockeerder], [verpak], [pallet], [eenheid], [aantal], [sorteerverkoop], [klasse], [maat], [kistpal]) VALUES (21, N'CONFERENCE', N'B.F.V.00001', N'JEAN DENIS', N'PAK', N'0', CAST(12.00 AS Numeric(6, 2)), 14, NULL, N'1', N'70/80', 80)
SET IDENTITY_INSERT [dbo].[Multisorting] OFF
/****** Object: StoredProcedure [dbo].[S_Multisortering] Script Date: 24/07/2017 15:36:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[S_Multisortering]
@id as int    = 0
AS

BEGIN

SELECT
sub.varieteit
,sub.stockeerder
,sub.referentie
,sub.klasse
,sub.maat
,aantal
,sub.verpak
,sub.eenheid
,sub.totaal
,sub.pallet
,sub.kistpal
,sub.stapels

FROM
(
Select Distinct
    SPL.pallet,
            SPL.Aantal,
        SPL.referentie,
            SPL.eenheid,
       SPL.stockeerder,
            SPL.varieteit,
            SPL.klasse,
            SPL.maat,
            SPL.verpak,
            CAST(SPL.aantal as int ) * CAST(SPL.eenheid as numeric) as totaal,
    SPL.kistpal,
            (SELECT COUNT(*) AS "aantal_palletten" FROM Multisorting WHERE Aantal = SPL.Aantal and pallet = SPL.pallet) AS stapels,
            -- With this testtable the SELECT COUNT Work not perfect, but this is not the problem
            Orderby= case @id when 1 then 'varieteit' else 'stockeerder' end


FROM    Multisorting SPL
       Where SPL.sorteerverkoop IS NULL
                    ) sub
ORDER BY sub.Orderby DESC

END

Ken er nog niet veel van, maar leer het wel
Do not know much about it yet, but learn