SQL Query for DB2 with group by

[mathon]

hi,

I have the following table

Col.1 Col.2 Col.3
5032 03869723 228.25
5033 03869723 228.25
5034 03869723 178.55
5035 03869723 49.7
5036 03869723 6
5037 03869723 104.75
5038 03869723 43.7
5039 03869723 67.8
5040 03869723 6
1 03870085 136.19
7 03870085 136.19
125 03870085 47.62
127 03870085 88.57
1000 03870085 47.62
1002 03870085 88.57
1 03870194 181.93
7 03870194 181.93
125 03870194 47.62
126 03870194 134.31
1000 03870194 47.62
1001 03870194 123.81
1009 03870194 10.5
5005 03870482 105.7
5006 03870482 105.7
5007 03870482 105.7

The result should look like this, it means the minimum of Col. 1 for every number in Col.2 and value in Col.3.

Col.1 Col.2 Col.3
5032 03869723 228.25
1 03870085 136.19
1 03870194 181.93
5005 03870482 105.7

Has anybody an idea how this could work?

Regards
matti

 

[ca8msm]

If I understand you correctly, you want a simply group by expression e.g.

select min(col1), col2, col3
from table
group by col2, col3

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[mathon]

Unfortunately not, because when I use this query there are still displayed more rows per number in Col.2. But I would like to have one row per number in Col.2 and it should be this row where there is the minimum in Col.1.

So for the first number 03869723 it should be the row with the number 5032 in Col.1 because it is the minimum value of all rows with the number 03869723.

Do you know what I mean?

Regards
matti

 

[ca8msm]

Oh, yes I see. Try this then:

select min(a.col1), a.col2, a.col3
from @table1 a,
(select min(col1) as min1, col2 from @table1 group by col2) b
where a.col1 = b.min1
group by a.col2, a.col3
order by col2

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[mathon]

hmm...when I this query the AQT is processing the query and does not stop, but the table has not so many entries that it will process with no end...is something wrong with the query?

 

[ca8msm]

It worked fine with your sample data:

DECLARE @TABLE1 TABLE (col1 int, col2 int, col3 decimal(10,1)) 
INSERT @TABLE1 VALUES ('5032', '3869723', '228.25')

INSERT @TABLE1 VALUES ('5033', '3869723', '228.25')
INSERT @TABLE1 VALUES ('5034', '3869723', '178.55')
INSERT @TABLE1 VALUES ('5035', '3869723', '49.7')
INSERT @TABLE1 VALUES ('5036', '3869723', '6')
INSERT @TABLE1 VALUES ('5037', '3869723', '104.75')
INSERT @TABLE1 VALUES ('5038', '3869723', '43.7')
INSERT @TABLE1 VALUES ('5039', '3869723', '67.8')
INSERT @TABLE1 VALUES ('5040', '3869723', '6')
INSERT @TABLE1 VALUES ('1', '3870085', '136.19')
INSERT @TABLE1 VALUES ('7', '3870085', '136.19')
INSERT @TABLE1 VALUES ('125', '3870085', '47.62')
INSERT @TABLE1 VALUES ('127', '3870085', '88.57')
INSERT @TABLE1 VALUES ('1000', '3870085', '47.62')
INSERT @TABLE1 VALUES ('1002', '3870085', '88.57')
INSERT @TABLE1 VALUES ('1', '3870194', '181.93')
INSERT @TABLE1 VALUES ('7', '3870194', '181.93')
INSERT @TABLE1 VALUES ('125', '3870194', '47.62')
INSERT @TABLE1 VALUES ('126', '3870194', '134.31')
INSERT @TABLE1 VALUES ('1000', '3870194', '47.62')
INSERT @TABLE1 VALUES ('1001', '3870194', '123.81')
INSERT @TABLE1 VALUES ('1009', '3870194', '10.5')
INSERT @TABLE1 VALUES ('5005', '3870482', '105.7')
INSERT @TABLE1 VALUES ('5006', '3870482', '105.7')
INSERT @TABLE1 VALUES ('5007', '3870482', '105.7')

select distinct a.col1, a.col2, a.col3
from @table1 a,
(select min(col1) as min1, col2 from @table1 group by col2) b
where a.col1 = b.min1

You could also try a variation on this (but it appears to have the same execution plan) such as:

select distinct a.col1, a.col2, a.col3
from @table1 a,
(select min(col1) as min1, col2 from @table1 group by col2) b
where a.col1 = b.min1

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ESquared]

Um, your title says "for DB2"

Does that mean you are not using Microsoft SQL Server which this forum is dedicated to and which answers you get here may not apply to other database systems? I suppose anything with ANSI syntax might work but if you want specialized help you should post in the IBM: DB2 forum.

So many people are posting in here lately askinfg for help with other database systems besides Microsoft SQL Server. What gives?

 

[onpnt]

>>What gives?

Brain cell count?

[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!

http://www.lessthandot.com

coming to your keyboards soon[/sub]

 

[ESquared]

I'm not trying to insult anyone, I'm just curious as there have been a lot lately. It certainly does involve not paying attention, at least. Of which I am often guilty, though I try not to be.