[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

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
0
asp_net2
Asked:
asp_net2
  • 3
  • 3
1 Solution
 
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
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now