christopher ACHARD christopher ACHARD - 1 month ago 9
ASP.NET (C#) Question

ORDER BY, SELECT with UNION sort difficulty

I have difficulty using the keyword UNION in my SQL request,
I need to sort my result by column which is not displayed by a SELECT,

DECLARE @search0 varchar(30);

SET @search0 = 'joll'


SELECT c.CdCnd AS n, FORMAT(c.ModifieLe, 'd', 'fr-FR') AS Modifié, c.Nom+' '+c.prenom AS 'Nom Complet', c.TelMobile AS 'Tel. Mob', c.SuiviPar AS 'Suivi par', a.LibAction AS 'à faire', c.Remuneration AS Rémunération, p.LibPrio AS Priorité, c.disponibilite AS Disponibilité, c.MotCleTech AS MCTech, c.MotCleFct AS MCFonc, c.MotCleEnt AS MCEnt, c.Details AS 'Détails', c.DateDispo AS DTDispo, c.Mobilite AS Mobilité
FROM Candidat c
LEFT JOIN TypAction a
ON c.CdAction = a.CdAction
LEFT JOIN TypPriorite p
ON c.CdPrio = p.CdPrio
WHERE ( ( Nom LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI OR prenom LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI OR MotCleTech LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI OR MotCleFct LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI OR MotCleEnt LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI ) )

UNION

SELECT c.CdCnd AS n, FORMAT(c.ModifieLe, 'd', 'fr-FR') AS Modifié, c.Nom+' '+c.prenom AS 'Nom Complet', c.TelMobile AS 'Tel. Mob', c.SuiviPar AS 'Suivi par', a.LibAction AS 'à faire', c.Remuneration AS Rémunération, p.LibPrio AS Priorité, c.disponibilite AS Disponibilité, c.MotCleTech AS MCTech, c.MotCleFct AS MCFonc, c.MotCleEnt AS MCEnt, c.Details AS 'Détails', c.DateDispo AS DTDispo, c.Mobilite AS Mobilité
FROM Candidat c
LEFT JOIN TypAction a
ON c.CdAction = a.CdAction
LEFT JOIN TypPriorite p
ON c.CdPrio = p.CdPrio
WHERE ( 1 = 2 OR c.CdCnd = '3' OR c.CdCnd = '48' OR c.CdCnd = '16' )

ORDER BY Modifié DESC


Here, i need to sort my all result by DateTime with the column c.ModifieLe,
But, with the UNION restriction, I am able to use this column without displaying it, and i need to FORMAT my date in 'fr-FR',

So, i use "Modifié" to sort my result, but this sort like a varchar type...

I need to FORMAT my DateTime in french cultur et keep it to Date Type.

Thanks for all help.

Answer

First apply UNION and Then sort them:

;WITH T AS
(
    SELECT 
        c.CdCnd AS n, 
        c.ModifieLe, 
        c.Nom+' '+c.prenom AS 'Nom Complet', 
        c.TelMobile AS 'Tel. Mob', 
        c.SuiviPar AS 'Suivi par', 
        a.LibAction AS 'à faire', 
        c.Remuneration AS Rémunération, 
        p.LibPrio AS Priorité, 
        c.disponibilite AS Disponibilité, 
        c.MotCleTech AS MCTech, 
        c.MotCleFct AS MCFonc, 
        c.MotCleEnt AS MCEnt, 
        c.Details AS 'Détails', 
        c.DateDispo AS DTDispo, 
        c.Mobilite AS Mobilité
    FROM Candidat c
    LEFT JOIN TypAction a 
        ON c.CdAction = a.CdAction
    LEFT JOIN TypPriorite p 
        ON c.CdPrio = p.CdPrio  
    WHERE  (  ( Nom LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR prenom LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleTech LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleFct LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleEnt LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  )  ) 

    UNION 

    SELECT 
        c.CdCnd AS n, 
        c.ModifieLe, 
        c.Nom+' '+c.prenom AS 'Nom Complet', 
        c.TelMobile AS 'Tel. Mob', 
        c.SuiviPar AS 'Suivi par', 
        a.LibAction AS 'à faire', 
        c.Remuneration AS Rémunération, 
        p.LibPrio AS Priorité, 
        c.disponibilite AS Disponibilité, 
        c.MotCleTech AS MCTech, 
        c.MotCleFct AS MCFonc, 
        c.MotCleEnt AS MCEnt, 
        c.Details AS 'Détails', 
        c.DateDispo AS DTDispo, 
        c.Mobilite AS Mobilité
    FROM Candidat c
    LEFT JOIN TypAction a 
        ON c.CdAction = a.CdAction
    LEFT JOIN TypPriorite p 
        ON c.CdPrio = p.CdPrio  
    WHERE  ( 1 = 2  OR  c.CdCnd = '3' OR  c.CdCnd = '48' OR  c.CdCnd = '16' )
)
SELECT
    [n],    
    FORMAT([ModifieLe], 'd', 'fr-FR') AS Modifié,  
    [Nom Complet], 
    [Tel. Mob], 
    [Suivi par], 
    [à faire], 
    [Rémunération], 
    [Priorité], 
    [Disponibilité], 
    [MCTech], 
    [MCFonc], 
    [MCEnt], 
    [Détails], 
    [DTDispo], 
    [Mobilité]
FROM T ORDER BY CAST(ModifieLe AS DATETIME) DESC