CODEWITHSUNDEEP
c#sqlasp.netado.netdatalist
gkrishy
gkrishy

Reputation: 756

How to keep the specific records at first position in datalist?

Am using datalist for one of my scenario. Datalist Items are: Name and Email. These things are binding from database. So far so good.

Concept:

User will register their name and email id in registration form. Once registration completed, data will store into table(registration). Also user need to verify their email id. Verified email will store into other table(verified) in my database.

What I want is:

I need to display the verified items only at first. After that unverified items should display in my datalist.

What I tried is:

I used DataList Itemdatabound. where i can do color difference between verified and unverified items. But I fail to display the verified records at first.

My query as follows ,

Select Distinct t1.name from Registration as t1 
inner join Verification as t2 on t1.Email=t2.Email 
and t2.status='Success'

Upvotes: 0

Views: 95

Answers (2)

Vardan Shahinyan
Vardan Shahinyan

Reputation: 40

Try below code. ORDER BY T2.Status DESC clause here will ensure that those records which exists both in Registration and Verification tables will be retrieved in first place. For more info on ORDER BY see here: https://msdn.microsoft.com/en-us/library/ms188385.aspx

  SELECT name, T1.Email
      FROM Registration T1
        LEFT JOIN Verification T2
          ON T1.Email = T2.Email
     ORDER BY T2.status DESC, T1.name ASC

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

Try this: (I've edited my answer to avoid the error on order by and distinct)

SELECT name, status
FROM (
    SELECT Distinct t1.name as name, t2.status as status
    FROM Registration as t1 
    LEFT JOIN Verification as t2 ON t1.Email=t2.Email 
    ) InnerSelect
ORDER BY CASE WHEN status='Success' THEN 0 ELSE 1 END, -- This is the 'trick'...
name

Upvotes: 0

Related Questions