I wanna find second highest value in our aggregate. Am using This Queries

SELECT {SalaryInfo}.[Name],MAX({SalaryInfo}.[Salary]) AS {SalaryInfo}.[Salary] FROM {SalaryInfo}
(Where {SalaryInfo}.[Salary]<(SELECT MAX({SalaryInfo}.[Salary]) FROM {SalaryInfo}))

SalaryInfo-Aggregate Name

Name & Salary is Column Name

but I cant find Desire Output.Help Me to find out the second highest value in that table 


Hi Murugan,

Am I correct in assuming your aggregate looks something like this?

And that you wish to find the second highest salary in the aggregate?

Kind regards,
Glenn

Hi Murugan,


In attachment you'll find an example of how to do this using either an aggregate or an advanced sql query.

Hope this helps!

Kind regards,
Glenn

You could use row_number function to help you execute this type of logic.

By doing that you are explicitly ordering the records from the highest value to the lower. That means that you can access any position you want. In the example I'm accessing the second highest value but if you want to access the third or fourth, just change the last condition accordingly.

SELECT *
FROM
    (
        SELECT
                {SalaryInfo}.[Salary]
                ,ROW_NUMBER() OVER (ORDER BY {SalaryInfo}.[Salary] DESC) ROWNUM
        FROM {SalaryInfo}
    ) AUX1
WHERE AUX1.ROWNUM = 2

glenn michiels wrote:

Hi Murugan,


In attachment you'll find an example of how to do this using either an aggregate or an advanced sql query.

Hope this helps!

Kind regards,
Glenn


Hi,

thanks for the prompt response but still now am not getting output. I want use one SQL Widget one structure and 1 aggregate  

Ruben Bernardo wrote:

You could use row_number function to help you execute this type of logic.

By doing that you are explicitly ordering the records from the highest value to the lower. That means that you can access any position you want. In the example I'm accessing the second highest value but if you want to access the third or fourth, just change the last condition accordingly.

SELECT *
FROM
    (
        SELECT
                {SalaryInfo}.[Salary]
                ,ROW_NUMBER() OVER (ORDER BY {SalaryInfo}.[Salary] DESC) ROWNUM
        FROM {SalaryInfo}
    ) AUX1
WHERE AUX1.ROWNUM = 2

Hi,

This is right one but I want show multiple people values  


glenn michiels wrote:

Hi Murugan,

Am I correct in assuming your aggregate looks something like this?

And that you wish to find the second highest salary in the aggregate?

Kind regards,
Glenn

Yes. Correct glenn


Murugan S S wrote:

glenn michiels wrote:

Hi Murugan,


In attachment you'll find an example of how to do this using either an aggregate or an advanced sql query.

Hope this helps!

Kind regards,
Glenn


Hi,

thanks for the prompt response but still now am not getting output. I want use one SQL Widget one structure and 1 aggregate  

As shown in the example you'll get back the second-highest salary as intended (I used 2 methods to show you it can be done using either an aggregate or an advanced sql query). 

Perhaps I misunderstood the response you're trying to get?

Could you tell me what exactly you're trying to get as a response?

Regards,
Glenn


glenn michiels wrote:

Murugan S S wrote:

glenn michiels wrote:

Hi Murugan,


In attachment you'll find an example of how to do this using either an aggregate or an advanced sql query.

Hope this helps!

Kind regards,
Glenn


Hi,

thanks for the prompt response but still now am not getting output. I want use one SQL Widget one structure and 1 aggregate  

As shown in the example you'll get back the second-highest salary as intended (I used 2 methods to show you it can be done using either an aggregate or an advanced sql query). 

Perhaps I misunderstood the response you're trying to get?

Could you tell me what exactly you're trying to get as a response?

Regards,
Glenn


Hi,


Let me tell..I have 100 People Salary details like Name, Salary. In that 20 people having highest salary,40 people Having Highest salary. In that case I want to show the second highest salary people details in my screen with use of one SQL widget ,One Structure ,One aggregate . This is the my exact Requirement


Murugan S S wrote:

Hi,


Let me tell..I have 100 People Salary details like Name, Salary. In that 20 people having highest salary,40 people Having Highest salary. In that case I want to show the second highest salary people details in my screen with use of one SQL widget ,One Structure ,One aggregate . This is the my exact Requirement


The SQL row_number function also lets you group you set by any condition you want. You can execute your requirement just by adding 'PARTITION BY {SalaryInfo}.[Salary]' before ordering the results. 

Basically you are grouping your set by Salary and creating groups of people based on their salary.


Hi Murugan,

Since you already have the advancedSql query to find the second-highest salary, could you not use that one in an additional SqlQuery or SELECT statement to get the people who have that specific salary?

 Something like this should return every person with the second-highest salary.

SyntaxEditor Code Snippet

SELECT {SalaryInfo}.* from {SalaryInfo} where {SalaryInfo}.[Salary]=(
SELECT MAX({SalaryInfo}.[Salary]) from {SalaryInfo} where {SalaryInfo}.[Salary] < (select MAX({SalaryInfo}.[Salary]) from {SalaryInfo}));

Although there are probably easier ways to do this.