CODEWITHSUNDEEP
sql
S. Arzoo
S. Arzoo

Reputation: 73

Sort SQL table with values from another table

I have an SQL table(tblRequests) with the list of requests submitted by different users. The fields are:

RequestID, UserID, DateSubmitted, Status

I have another table(tblImportantUsers) with the list of important users. This table has only one field of UserID.

I want to display the requests submitted by the important users on top. I can achieve this using the below query:

select r.RequestID, r.UserID, r.DateSubmitted, r.Status
from tblRequests r left join tblImportantUsers u on r.UserID = u.UserID
order by u.UserID desc, r.DateSubmitted desc

However, I only want the active requests of important users to show on top. The inactive requests should be sorted according to the DateSubmitted field. I even tried the UNION method but it doesn't work either.

Is there any way I can achieve this without adding any extra columns in my requests table?

Thanks

Data in tblRequests:

RequestID   UserID   DateSubmitted   Status
1           205      3/12/2016       0
2           208      3/16/2016       1
3           203      3/17/2016       0
4           241      3/17/2016       1
5           210      3/18/2016       0
6           205      3/18/2016       1
7           203      3/19/2016       1
8           241      3/19/2016       1

Data in tblImportantUsers:

UserID
205
203

Required Result:

RequestID   UserID   DateSubmitted   Status
7           203      3/19/2016       1
6           205      3/18/2016       1
8           241      3/19/2016       1
5           210      3/18/2016       0
4           241      3/17/2016       1
3           203      3/17/2016       0
2           208      3/16/2016       1
1           205      3/12/2016       0

Upvotes: 2

Views: 66

Answers (2)

K. Leonard
K. Leonard

Reputation: 26

Try this, it works on redshift:

   select
       a.requestid
     , a.userid
     , a.datesubmitted
     , a.status
   from
     tblrequests a 
     left join tblimportantusers b on (a.userid = b.userid)
   order by
     (case when status = 1 and b.userid is not null then 1 else 0 end) desc
     , date submitted desc
     , userid desc

Upvotes: 1

user3652621
user3652621

Reputation: 3634

This should do it:

select RequestID, UserID, DateSubmitted, Status
from (
select r.RequestID, r.UserID, r.DateSubmitted, r.Status, 1 as ind
from tblRequests r inner join tblImportantUsers u on r.UserID = u.UserID
order by r.status desc, r.DateSubmitted desc

union all

select r.RequestID, r.UserID, r.DateSubmitted, r.Status, 0 as ind
from tblRequests r left outer join tblImportantUsers u on r.UserID =    u.UserID
where u.UserID is null
order by r.status desc, r.DateSubmitted desc)

order by ind desc, DateSubmitted desc, userID

Doing an inner join and a left OUTER join

Upvotes: 0

Related Questions