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_RetrieveAllPledgeParticipants

AS

SELECT pi_id, pi_fname, pi_lname, pldg_date, pldg_complete
FROM dbo.WellnessChoice_PersonalInfo AS peri
INNER JOIN dbo.WellnessChoice_Pledge AS pl
ON peri.pi_id = pl.pi_id
ORDER BY pi_lname ASC
LVL 4
asp_net2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xananduCommented:
to order by 2 different fields use
ORDER BY pi_lname ASC, pldg_complete DESC

This will sort by last name, then by pldg_complete field
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sshah254Commented:
SELECT pi_id, pi_fname, pi_lname, pldg_date, pldg_complete
FROM dbo.WellnessChoice_PersonalInfo AS peri
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_PersonalInfo AS peri
INNER JOIN dbo.WellnessChoice_Pledge AS pl
ON peri.pi_id = pl.pi_id
ORDER BY pi_lname ASC, pldg_complete

SS
0
asp_net2Author Commented:
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.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

asp_net2Author Commented:
Maybe I'm doing something wrong. The table WellnessChoice_PersonalInfo contains ALL employees. Some of those employees in the WellnessChoice_PersonalInfo table may not even be in the WellnessChoice_Pledge Table. If a user is in the WellnessChoice_Pledge Table then they will have a value of 1 assigned to the pldg_complete field. If they are not in the WellnessChoice_Pledge Table then display a 0.
0
xananduCommented:
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_PersonalInfo AS peri
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.
0
asp_net2Author Commented:
@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.
0
xananduCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.