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
 General SQL Server Forums
 New to SQL Server Administration
 how to choose the maximum value after group by?

Author  Topic 

kelvinjpeak
Starting Member

4 Posts

Posted - 2015-01-13 : 13:46:22
Do Anyone know how to choose the maximum value after group by?

For example, i grouped-by a relation like this:
SELECT ENUM,COUNT(PCODE) as OnProjects
FROM employee-project
GROUP BY ENUM;

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2015-01-13 : 13:47:32
max()

?


Too old to Rock'n'Roll too young to die.
Go to Top of Page

kelvinjpeak
Starting Member

4 Posts

Posted - 2015-01-13 : 13:50:54
Yes, i do know that but i dont know how to add on it.
Sorry, i am kind of new LOL
quote:
Originally posted by webfred

max()

?


Too old to Rock'n'Roll too young to die.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-13 : 13:53:45
You haven't provided enough info, but here's my shot:
SELECT MAX(OnProjects)
FROM (
SELECT ENUM,COUNT(PCODE) as OnProjects
FROM employee-project
GROUP BY ENUM) t;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

kelvinjpeak
Starting Member

4 Posts

Posted - 2015-01-13 : 13:56:43
THX A LOT!
But could i use the LITMIT 1 statement to show the max value?
quote:
Originally posted by tkizer

You haven't provided enough info, but here's my shot:
SELECT MAX(OnProjects)
FROM (
SELECT ENUM,COUNT(PCODE) as OnProjects
FROM employee-project
GROUP BY ENUM) t;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-13 : 14:00:06
LIMIT is a MySql function. SQLTeam.com is for Microsoft SQL Server. Similar syntax, but there are times like now that they aren't the same.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

kelvinjpeak
Starting Member

4 Posts

Posted - 2015-01-13 : 14:04:02
Oh i got it,
BTW, what is the 't;' refer to ?
HAHA i should google it but i just want to ask it if you r willing to answer
quote:
Originally posted by tkizer

LIMIT is a MySql function. SQLTeam.com is for Microsoft SQL Server. Similar syntax, but there are times like now that they aren't the same.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-13 : 14:11:07
It's the alias for the derived table. An alias is required. I typically just use t. You can name it whatever you want.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -