display the top 2 salaries from emlpoyee table using subquery

i have a requirement to fetch the top 2 salaries from employee table
iam using below query but getting error
please can anyone help?

SELECT iICompanyID,

       iIsequence

FROM employee

WHERE dIAmount IN (SELECT MAX(dIAmount)FROM employee)

   OR dIAmount IN ((SELECT MAX(diamount)

                    FROM employee

                    WHERE diamount IN (SELECT dIAmount

                                       FROM employee

                                       WHERE dIAmount NOT IN (SELECT MAX(diamount)FROM employee)

                                      )

edited Dec 4 '18 at 12:14

Salman A

183k66340435

asked Nov 22 '18 at 12:49

Mahesh Kumar

112

3

This is why good use of whitespace is important. Now that I've formatted your query it should be very evident what is wrong with it (apart from the insane amount of nested SELECT statements). Also, when posting in regards to an error, it's important to include the error. The volunteers here can't run your SQL without sample data, nor can they see your screen

– Larnu
Nov 22 '18 at 12:52

add a comment |

i have a requirement to fetch the top 2 salaries from employee table
iam using below query but getting error
please can anyone help?

SELECT iICompanyID,

       iIsequence

FROM employee

WHERE dIAmount IN (SELECT MAX(dIAmount)FROM employee)

   OR dIAmount IN ((SELECT MAX(diamount)

                    FROM employee

                    WHERE diamount IN (SELECT dIAmount

                                       FROM employee

                                       WHERE dIAmount NOT IN (SELECT MAX(diamount)FROM employee)

                                      )

edited Dec 4 '18 at 12:14

Salman A

183k66340435

asked Nov 22 '18 at 12:49

Mahesh Kumar

112

3

This is why good use of whitespace is important. Now that I've formatted your query it should be very evident what is wrong with it (apart from the insane amount of nested SELECT statements). Also, when posting in regards to an error, it's important to include the error. The volunteers here can't run your SQL without sample data, nor can they see your screen

– Larnu
Nov 22 '18 at 12:52

add a comment |

i have a requirement to fetch the top 2 salaries from employee table
iam using below query but getting error
please can anyone help?

SELECT iICompanyID,

       iIsequence

FROM employee

WHERE dIAmount IN (SELECT MAX(dIAmount)FROM employee)

   OR dIAmount IN ((SELECT MAX(diamount)

                    FROM employee

                    WHERE diamount IN (SELECT dIAmount

                                       FROM employee

                                       WHERE dIAmount NOT IN (SELECT MAX(diamount)FROM employee)

                                      )

edited Dec 4 '18 at 12:14

Salman A

183k66340435

asked Nov 22 '18 at 12:49

Mahesh Kumar

112

i have a requirement to fetch the top 2 salaries from employee table
iam using below query but getting error
please can anyone help?

SELECT iICompanyID,

       iIsequence

FROM employee

WHERE dIAmount IN (SELECT MAX(dIAmount)FROM employee)

   OR dIAmount IN ((SELECT MAX(diamount)

                    FROM employee

                    WHERE diamount IN (SELECT dIAmount

                                       FROM employee

                                       WHERE dIAmount NOT IN (SELECT MAX(diamount)FROM employee)

                                      )

sql sql-server

edited Dec 4 '18 at 12:14

Salman A

183k66340435

asked Nov 22 '18 at 12:49

Mahesh Kumar

112

edited Dec 4 '18 at 12:14

Salman A

183k66340435

asked Nov 22 '18 at 12:49

Mahesh Kumar

112

edited Dec 4 '18 at 12:14

Salman A

183k66340435

edited Dec 4 '18 at 12:14

Salman A

183k66340435

edited Dec 4 '18 at 12:14

Salman A

183k66340435

asked Nov 22 '18 at 12:49

Mahesh Kumar

112

asked Nov 22 '18 at 12:49

Mahesh Kumar

112

asked Nov 22 '18 at 12:49

Mahesh Kumar

112

3

This is why good use of whitespace is important. Now that I've formatted your query it should be very evident what is wrong with it (apart from the insane amount of nested SELECT statements). Also, when posting in regards to an error, it's important to include the error. The volunteers here can't run your SQL without sample data, nor can they see your screen

– Larnu
Nov 22 '18 at 12:52

add a comment |

3

This is why good use of whitespace is important. Now that I've formatted your query it should be very evident what is wrong with it (apart from the insane amount of nested SELECT statements). Also, when posting in regards to an error, it's important to include the error. The volunteers here can't run your SQL without sample data, nor can they see your screen

– Larnu
Nov 22 '18 at 12:52

This is why good use of whitespace is important. Now that I've formatted your query it should be very evident what is wrong with it (apart from the insane amount of nested SELECT statements). Also, when posting in regards to an error, it's important to include the error. The volunteers here can't run your SQL without sample data, nor can they see your screen

– Larnu
Nov 22 '18 at 12:52

add a comment |

2 Answers
2

active

oldest

votes

You can use DENSE_RANK window function to find the employees sorted by salary. The following should return top 2 (possibly more if there are ties) employees by salary:

SELECT *

FROM (

    SELECT *, DENSE_RANK() OVER (ORDER BY dIAmount DESC) AS rnk

    FROM employee

) x

WHERE rnk <= 2

edited Nov 22 '18 at 13:27

answered Nov 22 '18 at 12:52

Salman A

183k66340435

If you want to know more about DENSE_RANK, read up on it here....

– Birel
Nov 22 '18 at 13:23

add a comment |

You can simply use DISTINCT TOP(2) with ORDER BY diamount DESC as below.

SELECT * 

FROM employee

WHERE dIAmount IN (

        SELECT DISTINCT TOP (2) dIAmount 

        FROM employee 

        ORDER BY dIAmount DESC

    )

Or you can use JOIN instead of WHERE IN condition as below.

SELECT * 

FROM employee E

INNER JOIN (

        SELECT DISTINCT TOP (2) dIAmount AS dIAmount

        FROM employee 

        ORDER BY dIAmount DESC

) M ON E.dIAmount = M.dIAmount

edited Nov 26 '18 at 4:37

answered Nov 22 '18 at 12:54

Karan

3,3912424

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53431414%2fdisplay-the-top-2-salaries-from-emlpoyee-table-using-subquery%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

You can use DENSE_RANK window function to find the employees sorted by salary. The following should return top 2 (possibly more if there are ties) employees by salary:

SELECT *

FROM (

    SELECT *, DENSE_RANK() OVER (ORDER BY dIAmount DESC) AS rnk

    FROM employee

) x

WHERE rnk <= 2

edited Nov 22 '18 at 13:27

answered Nov 22 '18 at 12:52

Salman A

183k66340435

If you want to know more about DENSE_RANK, read up on it here....

– Birel
Nov 22 '18 at 13:23

add a comment |

You can use DENSE_RANK window function to find the employees sorted by salary. The following should return top 2 (possibly more if there are ties) employees by salary:

SELECT *

FROM (

    SELECT *, DENSE_RANK() OVER (ORDER BY dIAmount DESC) AS rnk

    FROM employee

) x

WHERE rnk <= 2

edited Nov 22 '18 at 13:27

answered Nov 22 '18 at 12:52

Salman A

183k66340435

If you want to know more about DENSE_RANK, read up on it here....

– Birel
Nov 22 '18 at 13:23

add a comment |

You can use DENSE_RANK window function to find the employees sorted by salary. The following should return top 2 (possibly more if there are ties) employees by salary:

SELECT *

FROM (

    SELECT *, DENSE_RANK() OVER (ORDER BY dIAmount DESC) AS rnk

    FROM employee

) x

WHERE rnk <= 2

edited Nov 22 '18 at 13:27

answered Nov 22 '18 at 12:52

Salman A

183k66340435

You can use DENSE_RANK window function to find the employees sorted by salary. The following should return top 2 (possibly more if there are ties) employees by salary:

SELECT *

FROM (

    SELECT *, DENSE_RANK() OVER (ORDER BY dIAmount DESC) AS rnk

    FROM employee

) x

WHERE rnk <= 2

edited Nov 22 '18 at 13:27

answered Nov 22 '18 at 12:52

Salman A

183k66340435

edited Nov 22 '18 at 13:27

answered Nov 22 '18 at 12:52

Salman A

183k66340435

answered Nov 22 '18 at 12:52

Salman A

183k66340435

answered Nov 22 '18 at 12:52

Salman A

183k66340435

If you want to know more about DENSE_RANK, read up on it here....

– Birel
Nov 22 '18 at 13:23

add a comment |

If you want to know more about DENSE_RANK, read up on it here....

– Birel
Nov 22 '18 at 13:23

If you want to know more about DENSE_RANK, read up on it here....

– Birel
Nov 22 '18 at 13:23

add a comment |

You can simply use DISTINCT TOP(2) with ORDER BY diamount DESC as below.

SELECT * 

FROM employee

WHERE dIAmount IN (

        SELECT DISTINCT TOP (2) dIAmount 

        FROM employee 

        ORDER BY dIAmount DESC

    )

Or you can use JOIN instead of WHERE IN condition as below.

SELECT * 

FROM employee E

INNER JOIN (

        SELECT DISTINCT TOP (2) dIAmount AS dIAmount

        FROM employee 

        ORDER BY dIAmount DESC

) M ON E.dIAmount = M.dIAmount

edited Nov 26 '18 at 4:37

answered Nov 22 '18 at 12:54

Karan

3,3912424

add a comment |

You can simply use DISTINCT TOP(2) with ORDER BY diamount DESC as below.

SELECT * 

FROM employee

WHERE dIAmount IN (

        SELECT DISTINCT TOP (2) dIAmount 

        FROM employee 

        ORDER BY dIAmount DESC

    )

Or you can use JOIN instead of WHERE IN condition as below.

SELECT * 

FROM employee E

INNER JOIN (

        SELECT DISTINCT TOP (2) dIAmount AS dIAmount

        FROM employee 

        ORDER BY dIAmount DESC

) M ON E.dIAmount = M.dIAmount

edited Nov 26 '18 at 4:37

answered Nov 22 '18 at 12:54

Karan

3,3912424

add a comment |

You can simply use DISTINCT TOP(2) with ORDER BY diamount DESC as below.

SELECT * 

FROM employee

WHERE dIAmount IN (

        SELECT DISTINCT TOP (2) dIAmount 

        FROM employee 

        ORDER BY dIAmount DESC

    )

Or you can use JOIN instead of WHERE IN condition as below.

SELECT * 

FROM employee E

INNER JOIN (

        SELECT DISTINCT TOP (2) dIAmount AS dIAmount

        FROM employee 

        ORDER BY dIAmount DESC

) M ON E.dIAmount = M.dIAmount

edited Nov 26 '18 at 4:37

answered Nov 22 '18 at 12:54

Karan

3,3912424

You can simply use DISTINCT TOP(2) with ORDER BY diamount DESC as below.

SELECT * 

FROM employee

WHERE dIAmount IN (

        SELECT DISTINCT TOP (2) dIAmount 

        FROM employee 

        ORDER BY dIAmount DESC

    )

Or you can use JOIN instead of WHERE IN condition as below.

SELECT * 

FROM employee E

INNER JOIN (

        SELECT DISTINCT TOP (2) dIAmount AS dIAmount

        FROM employee 

        ORDER BY dIAmount DESC

) M ON E.dIAmount = M.dIAmount

edited Nov 26 '18 at 4:37

answered Nov 22 '18 at 12:54

Karan

3,3912424

edited Nov 26 '18 at 4:37

answered Nov 22 '18 at 12:54

Karan

3,3912424

answered Nov 22 '18 at 12:54

Karan

3,3912424

answered Nov 22 '18 at 12:54

Karan

3,3912424

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu