need query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

need query

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2007-10-31 : 03:30:40

table data :
a b c

1 2 3
1 2 4
1 2 5
1 9 3
1 9 5

output shud be

a b c
1 2 3
1 9 3
Top1 of a,b key column

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-31 : 03:54:51

select a, b, min(c)
from urTable
group by a, b

Em

niranjankumark
Posting Yak Master

164 Posts

Posted - 2007-10-31 : 03:57:17

thank u ..i got the answer.. smart thinking ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 04:20:34

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

niranjankumark
Posting Yak Master

164 Posts

Posted - 2007-10-31 : 04:37:19

hi elancaster ..
if i have n number of varchar and integer coulmn mean
also i need to select all columns .. a and b is only key..

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-31 : 04:41:40

can you give an example with more sample data? i'm not clear from your question whether you want to include the other columns in your 'group by' or ignore them or get a 'min' value again

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-31 : 04:42:20

quote:
Originally posted by niranjankumark

hi elancaster ..
if i have n number of varchar and integer coulmn mean
also i need to select all columns .. a and b is only key..

It depends.
You need to post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

niranjankumark
Posting Yak Master

164 Posts

Posted - 2007-10-31 : 04:49:53

a b c d .......
1 2 dr hi
1 2 fr hello
1 2 gr hy
1 9 gr ht
1 9 bg tr

Assume there is no integer column ...a & b is only key column...
top 1 shud taken ....
a b c d
1 2 dr hi
1 9 gr ht

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-31 : 04:54:57

you can still use the min function on a string. the question really is whether that should genuinely be the basis for selection? i.e. do you want the first one alphabetically or is there some other criteria you've not explained?

Em

niranjankumark
Posting Yak Master

164 Posts

Posted - 2007-10-31 : 05:04:25

I need only top 1 of group by a,b. if i put min of in varchar not getting actual result.. like integer

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-31 : 05:09:03

but the question is what do you mean by top? the first alphabetically? the first by some id column? the first record based on 1 column or a combination of columns? etc...

you can just group by a and b and put a min function around your other columns but it's not really clear from you description whether that would give you the desired result or not

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-31 : 05:09:29

quote:
Originally posted by niranjankumark

I need only top 1 of group by a,b. if i put min of in varchar not getting actual result.. like integer

TOP 1 without ORDER BY has no meaning. You said you wanted TOP 1 GROUP by a, b but what is the order ?

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

niranjankumark
Posting Yak Master

164 Posts

Posted - 2007-10-31 : 05:21:38

a b c d e
1 2 dr hi 3
1 2 fr he 4
1 2 gr hy 3
1 9 gr ht 2
1 9 bg tr 4

Assume there is no integer column ...a & b is only key column...
top 1 shud taken ....
a b c d e
1 2 dr hi 3
1 9 gr ht 2

select a,b,c,e,min(d) from table
group by a,b,c,e

here group by shud have all select column except min() column.so expected result cant come.. my point
take column a ,b .. with distinct value ...
ex :
a b
1 2
1 9
mean with this top 1 of 1,2 row(single)
and top 1 of 1,9 row ( single)

I need to select all column.. so the problem comes..

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-31 : 05:27:13

i was thinking more like...


declare @table table (a int, b int, c varchar(10), d varchar(10))

insert into @table
select 1,2, 'dr', 'hi'
union all select 1, 2, 'fr', 'hello'
union all select 1, 2, 'gr', 'hy'
union all select 1, 9, 'gr', 'ht'
union all select 1, 9, 'bg', 'tr'

select a,b,min(c),min(d)
from @table
group by a,b

but the question is whether a simple min function will meet your requirement or whether there is a more 'contrived' method for selecting which record to show?

Em

niranjankumark
Posting Yak Master

164 Posts

Posted - 2007-10-31 : 05:35:12

hi elancaster ...
by query the output is right .. but i have more than 100 columns ...
so min() has to put in all.. it will decrease performance ???

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-31 : 05:43:59

decrease performance compared to what? i'm still not sure that it's actually what you want. is there not perhaps just a few of those 100 columns that contain the value you want the 'min' for? and then just select the rest of the columns based on that?
i.e.


declare @table table (a int, b int, c varchar(10), d varchar(10),e int)

insert into @table
select 1,2, 'dr', 'hi',10
union all select 1, 2, 'fr', 'hello',9
union all select 1, 2, 'gr', 'hy',15
union all select 1, 9, 'gr', 'ht',6
union all select 1, 9, 'bg', 'tr',5

select t1.* 
from 
@table t1 join 
(
select a,b,min(e) as e
from @table
group by a,b) t2
on t1.a = t2.a and t1.b=t2.b and t1.e=t2.e

niranjankumark
Posting Yak Master

164 Posts

Posted - 2007-10-31 : 07:16:53

yes enclaster this is what i need....
thank a lot ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources