How to get last emplyee Ids which are deleted (multiple) and get last emplyee Ids which are not deleted (single/multiple) in a single query?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

VS2005

, +

How to get last emplyee Ids which are deleted (multiple) and get last emplyee Ids which are not deleted (single/multiple)in a single query ?

My code is as follows:

SQL

Select C3EmpRegId,EmpId,CitizenId,OldCardNo,OldAccountNo,NewCardNo,NewAccountNo
from
(SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as OldCardNo,AccountNo as OldAccountNo FROM EMPLOYEE
WHERE EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1 and IsDeleted=1

UNION

SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as NewCardNo,AccountNo as NewAccountNo FROM EMPLOYEE
WHERE EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1 and IsDeleted=0)
results
ORDER by C3EmpRegId desc

On trying as below I get the desired result(last data with IsDeleted=0)(Separate Query)

SQL

SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as OldCardNo,AccountNo as OldAccountNo FROM EMPLOYEE
WHERE EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1 and IsDeleted=0
ORDER by C3EmpRegId desc

On trying as below I get the desired result(last data with IsDeleted=1)(Separate Query)

SQL

SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as OldCardNo,AccountNo as OldAccountNo FROM EMPLOYEE
WHERE EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1 and IsDeleted=1
ORDER by C3EmpRegId desc

Posted 24-Feb-14 0:24am

Rahul 105

Updated 24-Feb-14 0:33am

Maciej Los

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 24-Feb-14 6:26am

Consider UNION...

Maciej Los 24-Feb-14 7:14am

Not sure what you mean: "How to get last emplyee Ids deleted and not deleted in a single query"?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-02-24T01:42:00

First of all, please read my comment to the question.

You're looking for query like that, probably:

SQL

SELECT C3EmpRegId, EmpId, CitizenId, CardNo, AccountNo, IsDeleted
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY IsDeleted ORDER BY C3EmpRegId) AS RowNo, C3EmpRegId, EmpId, CitizenId, CardNo, AccountNo, IsDeleted
    FROM EMPLOYEE 
) AS A
WHERE RowNo = 1 AND EmpId='556556y'  and CorporateId='IBM'  and SalaryAdvance=1

For further information, please see ranking functions[^].

By The Way:
You can't use different column names with UNION[^]!
SQL

SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as OldCardNo, AccountNo as OldAccountNo FROM EMPLOYEE ... UNION SELECT top 1 C3EmpRegId,EmpId,CitizenId,CardNo as NewCardNo,AccountNo as NewAccountNo FROM EMPLOYEE ...

If you would like to get old and new CardNo and AccountNo, youo should join 2 result sets as follow:

SQL

SELECT t1.C3EmpRegId, t1.EmpId, t1.CitizenId, t1.CardNo AS OldCardNo, t1.AccountNo AS OldAccountNo, t2.CardNo AS NewCardNo, t2.AccountNo AS NewAccountNo
FROM (
    SELECT *
    FROM Employee
    WHERE IsDeleted =1
) AS t1 INNER JOIN (
        SELECT C3EmpRegId, EmpId, CitizenId, CardNo, AccountNo
        FROM Employee
        WHERE AND IsDeleted =0
    ) AS t2 ON t1.EmpId = t2.EmpId
WHERE t1.EmpId='556556y' AND t1.CorporateId='IBM' AND t1.SalaryAdvance=1
ORDER BY t1.C3EmpRegId

Note: I don't know the structure of your database (tables), so above query can miss your needs.