Help sorting based on two fields
Hello Experts,
I have the following stored procedure below and I would like to sort all data based on the pi_lname field (Users Last Name) and then sort based on the pldg_complete field. The pldg_complete field will either have a value of 0 or 1. Is ths possible and if so how can I do that with my SP below.
CREATE PROCEDURE WellnessChoice_RetrieveAll
AS
SELECT pi_id, pi_fname, pi_lname, pldg_date, pldg_complete
FROM dbo.WellnessChoice_Persona
INNER JOIN dbo.WellnessChoice_Pledge AS pl
ON peri.pi_id = pl.pi_id
ORDER BY pi_lname ASC
I have the following stored procedure below and I would like to sort all data based on the pi_lname field (Users Last Name) and then sort based on the pldg_complete field. The pldg_complete field will either have a value of 0 or 1. Is ths possible and if so how can I do that with my SP below.
CREATE PROCEDURE WellnessChoice_RetrieveAll
AS
SELECT pi_id, pi_fname, pi_lname, pldg_date, pldg_complete
FROM dbo.WellnessChoice_Persona
INNER JOIN dbo.WellnessChoice_Pledge AS pl
ON peri.pi_id = pl.pi_id
ORDER BY pi_lname ASC
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Neither of those samples worked. For example pldg_complete has a value of either 0 or 1. I only see all of the rows where pldg_complete = 1 and not 0.
ASKER
Maybe I'm doing something wrong. The table WellnessChoice_PersonalInf
change the query around a bit
SELECT pi_id, pi_fname, pi_lname, pldg_date, pldg_complete
FROM dbo.WellnessChoice_Pledge AS pl
LEFT JOIN dbo.WellnessChoice_Persona
ON pl.pi_id = peri.pi_id
ORDER BY pi_lname ASC, pldg_complete
(NOTE: left join instead of inner join forces ALL records from the Pledge table to show regardless of 0 or 1) if this doesnt work, try RIGHT JOIN instead, im not sure which table has the feilds that you MUST have, instead of those which are being correlated to the original table.
SELECT pi_id, pi_fname, pi_lname, pldg_date, pldg_complete
FROM dbo.WellnessChoice_Pledge AS pl
LEFT JOIN dbo.WellnessChoice_Persona
ON pl.pi_id = peri.pi_id
ORDER BY pi_lname ASC, pldg_complete
(NOTE: left join instead of inner join forces ALL records from the Pledge table to show regardless of 0 or 1) if this doesnt work, try RIGHT JOIN instead, im not sure which table has the feilds that you MUST have, instead of those which are being correlated to the original table.
ASKER
@xanandu
Ok, Left Join worked as I needed but it first sorts pi_lname but does not sort pldg_complete. For example if I have a few users who have a value of 0 assigned for them in the pldg_complete field then it's showed as lisetd by pi_lname asc. I guess I should sort using pldg_complete first then pi_lname.
Ok, Left Join worked as I needed but it first sorts pi_lname but does not sort pldg_complete. For example if I have a few users who have a value of 0 assigned for them in the pldg_complete field then it's showed as lisetd by pi_lname asc. I guess I should sort using pldg_complete first then pi_lname.
you have it exactly. the ORDER BY just causes a sequence of ordering. if you are sorting in the wrong order, flip the fields around that you are ordering by, and you should be good to go.
FROM dbo.WellnessChoice_Persona
INNER JOIN dbo.WellnessChoice_Pledge AS pl
ON peri.pi_id = pl.pi_id
ORDER BY pldg_complete, pi_lname ASC
or
SELECT pi_id, pi_fname, pi_lname, pldg_date, pldg_complete
FROM dbo.WellnessChoice_Persona
INNER JOIN dbo.WellnessChoice_Pledge AS pl
ON peri.pi_id = pl.pi_id
ORDER BY pi_lname ASC, pldg_complete
SS