how can i get second max from column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how can i get second max from column

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-09 : 03:24:42

Hi, i have following data in my table with column names "Name" and "Culture". Here if you observe data, the more cultures here is "DUTC". I want to retrive output as second highest cultues from the below data. i.e out put i want is GERM-3

I want second maximum culture with its count as output

Cultuer Name
SWED Aas
SCOT Flaas
NAMR ASPAAS
NAMR HAUKAAS
MUSL Ilyaas
MUSL ASAAS
GERM Quaas
GERM KLAAS
GERM Kohlhaas
DUTC DeHaas
DUTC Elenbaas
DUTC Naas
DUTC Nicolaas
DUTC Baas

G. Satish

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-09 : 03:32:34

[code]
select Culture
from
(
select Culture, row_no = row_number() over (order by count(*) desc)
from Culture
group by Culture
) c
where row_no = 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-09 : 03:33:28

SELECT Culture, COUNT(*) FROM Table
WHERE Culture NOT IN (SELECT TOP 1 Culture FROM Table GROUP BY Culture ORDER BY COUNT(*) DESC)
GROUP BY Culture

N 56°04'39.26"
E 12°55'05.63"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-09 : 03:40:56

[code]SELECT CULTUER, countC
FROM (
SELECT CULTUER, COUNT(CULTUER) as countC, ROW_NUMBER() OVER (ORDER BY COUNT(CULTUER) DESC) as Num
FROM YOURTABLE
GROUP BY CULTUER
)a
WHERE num = 2[/code]
Hope this can help you ^^

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-09 : 03:41:16

zzzzzzzzz slowest!!!

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-09 : 03:54:47

Thank you :)

quote:
Originally posted by Peso

SELECT Culture, COUNT(*) FROM Table
WHERE Culture NOT IN (SELECT TOP 1 Culture FROM Table GROUP BY Culture ORDER BY COUNT(*) DESC)
GROUP BY Culture

N 56°04'39.26"
E 12°55'05.63"

G. Satish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:29:50

quote:
Originally posted by Peso

SELECT Culture, COUNT(*) FROM Table
WHERE Culture NOT IN (SELECT TOP 1 Culture FROM Table GROUP BY Culture ORDER BY COUNT(*) DESC)
GROUP BY Culture

N 56°04'39.26"
E 12°55'05.63"

wont this give all Culture's except highest repeated one?
I think what OP asked for was this


SELECT TOP 1 Culture, COUNT(*) FROM Table 
WHERE Culture IN (SELECT TOP 2 Culture FROM Table GROUP BY Culture ORDER BY COUNT(*) DESC)
GROUP BY Culture
ORDER BY COUNT(*) ASC

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-10 : 01:38:58

see this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources