Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

find max Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 12:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:52 AM
Points: 160, Visits: 399
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
Post #1346356
Posted Friday, August 17, 2012 1:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 2:40 AM
Points: 1,118, Visits: 1,573
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.


Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden
Post #1346361
Posted Friday, August 17, 2012 1:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 390, Visits: 605
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

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1346362
Posted Friday, August 17, 2012 1:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 411, Visits: 1,394
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?




Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
Post #1346363
Posted Friday, August 17, 2012 1:25 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, March 27, 2014 12:03 AM
Points: 75, Visits: 437
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.



I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Post #1346365
Posted Friday, August 17, 2012 1:34 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 3,590, Visits: 5,096
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1346368
Posted Friday, August 17, 2012 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:52 AM
Points: 160, Visits: 399
not working the ROW_NUMBER(), and DENSE_RANK() function in server 2000
Post #1346404
Posted Friday, August 17, 2012 3:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 3,590, Visits: 5,096
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1346411
Posted Friday, August 17, 2012 3:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 24, 2014 5:52 AM
Points: 160, Visits: 399
need the output in sql server 2000 friends
Post #1346414
Posted Friday, August 17, 2012 3:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:01 PM
Points: 3,590, Visits: 5,096
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1346424
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse