displaying all columns using group by clause

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 Programming

displaying all columns using group by clause

Author

Topic

ravivyas.rv
Starting Member

5 Posts

Posted - 2009-10-05 : 12:39:46

ble/ view : table1
no. country name
2 USA ra
3 USA aa
4 ITALY vv
15 INDIA abc
1 INDIA vir
3 ENGLAND eng
5 ENGLAND ng

Now I want the result by grouping country with max(no) as
either the results as
no. country name
3 USA aa
4 ITALY vv
15 INDIA abc
5 ENGLAND ng

or
country name
USA aa
ITALY vv
INDIA abc
ENGLAND ng

pls help me out...
what could be the query...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-05 : 13:02:05

Here's one way:


select max(ca.[no]) as [no]
       ,t1.country
       ,max(ca.[name]) as name
from   table1 t1
cross apply (
       select top 1 [no], [name]
       from   table1
       where  country = t1.country
       order by [no] desc
       ) ca
group by t1.country

EDIT:
this assumes you are using MS Sql Server version 2005 or later

Be One with the Optimizer
TG

ravivyas.rv
Starting Member

5 Posts

Posted - 2009-10-05 : 13:25:37

No Its not working...

And am using oracle sql plus...
and m repeating my query as there was some display prob

table/ view : table1
no. / country / name
2 / USA / ra
3 / USA / aa
4 / ITALY / vv
15 / INDIA / abc
1 / INDIA / vir
3 / ENGLAND / eng
5 / ENGLAND / ng

Now I want the result by grouping country displaying only max(no):
either the results as
no. / country / name
3 / USA / aa
4 / ITALY / vv
15 / INDIA / abc
5 / ENGLAND / ng

or(preferred)
country / name
USA / aa
ITALY / vv
INDIA / abc
ENGLAND / ng

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 13:41:34

quote:
Originally posted by ravivyas.rv

No Its not working...

And am using oracle sql plus...

Well......

This is a sql server forum.....

But this should work...as it's ANSI..well except for the table variable


DECLARE @t99 table([no.] int, country varchar(10), [name] varchar(10))

INSERT INTO @t99([no.], country, [name])
SELECT 2,  'USA',     'ra'   UNION ALL
SELECT 3,  'USA',     'aa'   UNION ALL
SELECT 4,  'ITALY',   'vv'   UNION ALL
SELECT 15, 'INDIA',   'abc'  UNION ALL
SELECT 1,  'INDIA',   'vir'  UNION ALL
SELECT 3,  'ENGLAND', 'eng'  UNION ALL
SELECT 5,  'ENGLAND', 'ng'

SELECT * 
  FROM @t99 o
 WHERE EXISTS ( SELECT country, MAX(i.[no.])
				  FROM @t99 i
				 WHERE i.country = o.country
				GROUP BY country
				  HAVING MAX(i.[no.]) = o.[no.])

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

gvphubli
Yak Posting Veteran

54 Posts

Posted - 2009-10-05 : 13:43:39

Use any of the aggrigate functions like min, max (which ever is appropriate in your case) on all the columns that you want to specify in the SELECT list.

TechnologyYogi
http://gvphubli.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 13:44:54

quote:
Originally posted by gvphubli

Use any of the aggrigate functions like min, max (which ever is appropriate in your case) on all the columns that you want to specify in the SELECT list.

TechnologyYogi
http://gvphubli.blogspot.com/

WHAT...does THAT...mean?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ravivyas.rv
Starting Member

5 Posts

Posted - 2009-10-05 : 13:55:06

hey thanx brett... its working.... :)
sorry for posting it in wrong form..

I have one more doubt if I only want to print country and name only and not the no.
then wat could be the query...

thanx in advance...

rv

ravivyas.rv
Starting Member

5 Posts

Posted - 2009-10-05 : 13:56:45

hey i got it...!!!
It would be fine if you tell a bit of its working

rv

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 14:01:31

I guess you did..


SELECT country, [name] 
  FROM @t99 o
 WHERE EXISTS ( SELECT country, MAX(i.[no.])
				  FROM @t99 i
				 WHERE i.country = o.country
				GROUP BY country
				  HAVING MAX(i.[no.]) = o.[no.])

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-05 : 14:02:48

quote:
Originally posted by ravivyas.rv

hey i got it...!!!
It would be fine if you tell a bit of its working

rv

the subquery gives you largest no value for each group of country. the outer query retrieves details of records with this no values.

ravivyas.rv
Starting Member

5 Posts

Posted - 2009-10-05 : 14:12:49

yepp brett.. I exacttly did the same thing.. thanx again...

and thanx visakh for the explanation...

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-05 : 14:16:07

quote:
Originally posted by visakh16

the subquery gives you largest no value for each group of country. the outer query retrieves details of records with this no values.

That's actually rather funny

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources