Radwa February 2016

how to sort according to special case in sql

I have this case i have stored procedure get all persons as couples male and female i want to sort those couple according to male name i try some sing like that

SELECT          P.GenderTypeID, 
                Couple.CoupleID, 
                P.PersonID , 
                p.NameAr, 
                p.NameEn , 
                p.Mobile 
FROM            tbl_Couples Couple 
LEFT OUTER JOIN tbl_Persons P 
ON              P.CoupleID = Couple.CoupleID 
ORDER BY 
                CASE WHEN P.GenderTypeID = 1 
                THEN '1'--Male 
                WHEN P.GenderTypeID = 2 
                THEN '2'--Female 
                ELSE  P.GenderTypeID END ASC , 
                p.NameAr 

put it get male first then female

i get that

GenderTypeID CoupleID PersonID NameAr  NameEn  Mobile
1              3       10       أحمد    Ali     01255678668
1              5       15        سمير   Samier  01255353563
1              4       17        سيد    Said    012359989744
2              5       14      سامية    Samia   01156786868
2              4       16       سعاد    Suadd   01353563563
2              3       12        منى    Mona    010264646444

put i expect

GenderTypeID CoupleID PersonID NameAr  NameEn  Mobile
1              3       10       أحمد    Ali     01255678668
2              3       12        منى    Mona    010264646444
1              5       15        سمير   Samier  01255353563
2              5       14      سامية    Samia   01156786868
1              4       17        سيد    Said    012359989744
2              4       16       سعاد    Suadd   01353563563

Answers


Muhammad Azim February 2016

You don't need to use any case statement in your order by clause, Just use this

SELECT          P.GenderTypeID, 
            Couple.CoupleID, 
            P.PersonID , 
            p.NameAr, 
            p.NameEn , 
            p.Mobile 
FROM            tbl_Couples Couple 
LEFT OUTER JOIN tbl_Persons P 
ON              P.CoupleID = Couple.CoupleID 
ORDER BY P.GenderTypeID, p.NameAr

Or if you want to order your record by CoupleID just change the order by

ORDER BY Couple.CoupleID, p.NameAr


Henk Kok February 2016

Use a CTE to first select only the males, which you then join with the tbl_Couples and tbl_Persons tables, which will give a result set in which the male part of the couple is duplicated for the "males" CTE.

WITH tbl_MaleOnly
AS
(
    SELECT *
    FROM   tbl_Persons
    WHERE  GenderTypeID = 1
)
SELECT          P.GenderTypeID, 
                Couple.CoupleID, 
                P.PersonID , 
                p.NameAr, 
                p.NameEn , 
                p.Mobile 
FROM            tbl_MaleOnly Male 
LEFT OUTER JOIN tbl_Couples Couple 
ON              Couple.CoupleID = Male.CoupleID
LEFT OUTER JOIN tbl_Persons P 
ON              P.CoupleID = Couple.CoupleID 
ORDER BY        Male.NameAr
,               P.GenderTypeID

Instead of a CTE, you could also use an inline view: replace "tbl_MaleOnly" in the main select with the CTE select in parenthesis.

Post Status

Asked in February 2016
Viewed 2,213 times
Voted 10
Answered 2 times

Search




Leave an answer