group by and min()

I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
int, others varchar. Assume the following data.
c1 c2 c3
--
2 d11 d111
1 d22 d111
3 d33 d333
I need to group by c3 and return the c2 that corresponds with the min()
of c1. I know I can write a simple function like below but would like
to know if there's a better way... ie no function.
select min(c1), dbo.getr2(c1, c3), c3
from table
group by c3
results
1 d22 d111
3 d33 d333
Thanks in advance.
jghwhat about select c3, min(c1) from table group by c3
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<justingharvey@.yahoo.com> wrote in message
news:1157504143.981683.101690@.i3g2000cwc.googlegroups.com...
>I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||I need the c2 that corresponds with the min(c1).
tks
Hilary Cotter wrote:[vbcol=seagreen]
> what about select c3, min(c1) from table group by c3
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <justingharvey@.yahoo.com> wrote in message
> news:1157504143.981683.101690@.i3g2000cwc.googlegroups.com...|||Hi,
Is what you what the C2 corresponding to the minimum value of C1 for each
distinct value of C3?
If so the following may be along the right lines:-
DROP TABLE Test
CREATE TABLE Test
(
C1 int,
C2 varchar(5),
C3 varchar(5)
)
INSERT INTO Test
(C1, C2, C3)
VALUES
(2, 'd11', 'd111')
INSERT INTO Test
(C1, C2, C3)
VALUES
(1, 'd22', 'd111')
INSERT INTO Test
(C1, C2, C3)
VALUES
(3, 'd33', 'd333')
SELECT C2
FROM TEST INNER JOIN
( SELECT MIN(C1) AS C1, C3
FROM TEST
GROUP BY C3
) AS MinC1ForEachC3
ON Test.C1 = MinC1ForEachC3.C1
AND Test.C3 = MinC1ForEachC3.C3
It might be what you need ... if not let us know
Craig
"justingharvey@.yahoo.com" wrote:

> I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||I'd do it like this:
select c1, c2, c3 from table
inner join
(
select min(c1) as Minc1, c3
from table
group by c3
) a
on table.c1 = a.Minc1
and table.c3 = a.c3
The function solution seems incorrect (or perhaps I didn't follow what your
function does), as the derivation needs to be applied after the grouping.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||How about ...
select c2 from Table1 where C1 IN
(
select min(c1) from Table1 group by c3
)
"justingharvey@.yahoo.com" wrote:

> I have a simple table. Let's say 3 columns, c1, c2, and c3. c1 is
> int, others varchar. Assume the following data.
> c1 c2 c3
> --
> 2 d11 d111
> 1 d22 d111
> 3 d33 d333
> I need to group by c3 and return the c2 that corresponds with the min()
> of c1. I know I can write a simple function like below but would like
> to know if there's a better way... ie no function.
> select min(c1), dbo.getr2(c1, c3), c3
> from table
> group by c3
> results
> 1 d22 d111
> 3 d33 d333
>
> Thanks in advance.
> jgh
>|||It wouldn't work. If we add another row to the table (at the bottom):
c1 c2 c3
--
2 d11 d111
1 d22 d111
3 d33 d333
2 d44 d444
The subquery returns 1,2,3 so the outer query now returns d11,d22,d33,d44,
but d11 shouldn't be returned.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com