August 17, 2012 at 12:56 am
hi friends,
create table emp(ename varchar(34),job char(33),sal varchar(22))
insert into emp values('sa','sales','400')
insert into emp values('ka','manager','4000')
insert into emp values('sam','Markting','2500')
insert into emp values('sabu','system','3900')
in these table model i ve a report now i need to display the 3rd max salry peoples,......like ... when i am entering postion its display the tat postion of max salry..... ve write on single query
August 17, 2012 at 1:14 am
From what I understand, this should do it:
Declare @position Char(33)
Set @position = 'sales'
Select ename, job From
(
Select *, ROW_NUMBER() Over (Partition By job Order By Sal) As rn From Ex
) As a
Where job = @position AND rn = 3
I hope this is what you are looking for.
August 17, 2012 at 1:16 am
raghuldrag (8/17/2012)
hi friends,create table emp(ename varchar(34),job char(33),sal varchar(22))
insert into emp values('sa','sales','400')
insert into emp values('ka','manager','4000')
insert into emp values('sam','Markting','2500')
insert into emp values('sabu','system','3900')
in these table model i ve a report now i need to display the 3rd max salry peoples,......like ... when i am entering postion its display the tat postion of max salry..... ve write on single query
Please use Dense rank on sal for this.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
August 17, 2012 at 1:22 am
Also, use proper types for your columns. Numeric values -like a salary- should not be entered as strings. If you store them as strings the values may not sort as you would expect. For example:
select max(salary)
from (
select '20' as salary
union all select '100'
) t
The output of this is:
----
20
(1 row(s) affected)
Probably not what you wanted, is it?
August 17, 2012 at 1:25 am
vinu512 (8/17/2012)
From what I understand, this should do it:
Declare @position Char(33)
Set @position = 'sales'
Select ename, job From
(
Select *, ROW_NUMBER() Over (Partition By job Order By Sal) As rn From Ex
) As a
Where job = @position AND rn = 3
I hope this is what you are looking for.
Just one note. If we are searching max there should be "Order By Sal desc". I think it's a mistype.
raghuldrag
Consider the case where there will be no more than 2 persons on postion. Should we loose them, or not?
If not, so maybe there is better to use: rn <=3, and than top(1)...order by rn desc.
Also, why are you defining number column like varchar datatype? There might be a problem, because strings sorted in another way than numbers.
August 17, 2012 at 1:34 am
rhythmk (8/17/2012)
raghuldrag (8/17/2012)
hi friends,create table emp(ename varchar(34),job char(33),sal varchar(22))
insert into emp values('sa','sales','400')
insert into emp values('ka','manager','4000')
insert into emp values('sam','Markting','2500')
insert into emp values('sabu','system','3900')
in these table model i ve a report now i need to display the 3rd max salry peoples,......like ... when i am entering postion its display the tat postion of max salry..... ve write on single query
Please use Dense rank on sal for this.
Agreed that you should look at RANK() and DENSE_RANK() and decide which to use based on the business rules for tie breakers.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 17, 2012 at 3:02 am
not working the ROW_NUMBER(), and DENSE_RANK() function in server 2000
August 17, 2012 at 3:14 am
raghuldrag (8/17/2012)
not working the ROW_NUMBER(), and DENSE_RANK() function in server 2000
Correct. They are SQL 2005 features and this is a SQL 2008 forum.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 17, 2012 at 3:21 am
need the output in sql server 2000 friends
August 17, 2012 at 3:31 am
Operating only with stone knives and bearskins at my disposal, I offer you this:
create table #emp(ename varchar(34),job char(33),sal varchar(22))
insert into #emp values('sa','sales','400')
insert into #emp values('ka','manager','4000')
insert into #emp values('sam','Markting','2500')
insert into #emp values('sabu','system','3900')
SELECT TOP 3 *
FROM #emp
ORDER BY CAST(sal AS INT) DESC
DROP TABLE #emp
However if there's a tie for the #3 spot, one will get left out. You might want to check to see if using TOP PERCENT might work better for your case.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 17, 2012 at 3:46 am
There is a dedicated forum here for SQL 2000:
http://www.sqlservercentral.com/Forums/Forum8-1.aspx
Ok for this time.
Lets have a look your sample from beginning.
1. First of all why are you using varchar for sal column? Do you realise that if you compare 1900 to 900 as string the 1900 will be less than 900? I've changed your sample to have sal in numeric datatype.
2. Your sample has not enough data to produce a full picture. Am I right to understand that you want to retrieve the records having the 3rd largest [sal] per [job]?
In SQL2000, you are not going to have nice query...
create table #emp(ename varchar(34),job char(33),sal money)
insert into #emp values('sa','sales','400')
insert into #emp values('ka','manager','4000')
insert into #emp values('sam','Markting','2500')
insert into #emp values('sabu','system','3900')
insert into #emp values('sa1','sales','300')
insert into #emp values('ka1','manager','3000')
insert into #emp values('sam1','Markting','1500')
insert into #emp values('sabu1','system','2900')
insert into #emp values('sa2','sales','200')
insert into #emp values('ka2','manager','2000')
insert into #emp values('sam2','Markting','500')
insert into #emp values('sabu2','system','1900')
insert into #emp values('sa3','sales','100')
insert into #emp values('ka3','manager','1000')
insert into #emp values('sam3','Markting','400')
insert into #emp values('sabu3','system','900')
select ef.*
from #emp ef
join (
select e3.job, MAX(e3.sal) mxsal3
from #emp e3
join (
select e2.job, MAX(e2.sal) mxsal2
from #emp e2
join (select job, MAX(sal) mxsal1
from #emp
group by job) m1
on m1.job = e2 .job and m1.mxsal1 > e2.sal
group by e2.job
) m2
on m2.job = e3.job and m2.mxsal2 > e3.sal
group by e3.job
) m3
on m3.job = ef.job and m3.mxsal3 = ef.sal
August 17, 2012 at 3:53 am
SomewhereSomehow (8/17/2012)
vinu512 (8/17/2012)
From what I understand, this should do it:
Declare @position Char(33)
Set @position = 'sales'
Select ename, job From
(
Select *, ROW_NUMBER() Over (Partition By job Order By Sal) As rn From Ex
) As a
Where job = @position AND rn = 3
I hope this is what you are looking for.
Just one note. If we are searching max there should be "Order By Sal desc". I think it's a mistype.
Yes, my bad...sorry about that. Its supposed to be Order by DESC.
But, I didn't know that the OP was looking for a solution for SQL Server 2000 :unsure:
Anyways, I'l give it a shot and get back with something Rahul.
August 17, 2012 at 3:55 am
ya its workin..... suppose i need the particular top 3rd postion of max salary means how to modify
August 17, 2012 at 3:59 am
raghuldrag (8/17/2012)
ya its workin..... suppose i need the particular top 3rd postion of max salary means how to modify
I'm not 100% sure what exactly working...
And what do you mean by " i need the particular top 3rd postion of max salary means how to modify"
Could you show exact expected results based on the data setup, please?
August 17, 2012 at 4:32 am
friends ,
select top 2*
from empy order by cast(sal as int)desc
in these query has given first two max values, now i need the ouput while i am giving the position =3 means third max sal has to display.....i.e 2500
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply