Select A group by B, where C is_minimal

[TorF]

Hello,

I am looking for help with an SQL (simple?) problem.

Here a little example, better than extra larges sentences...

Here my TABLE t
id A B
1 5 1
2 2 1
3 15 1
4 7 2
5 3 2
6 1 2
7 6 2
8 7 2

I need to get the 'id'
for each group B
where A is minimal in each group

For this example, result is
id A B
2 2 1
6 1 2

And if it's possible, a more hardous result...

Counting the position of B in each group, so result is :
id A B C
1 5 1 2
2 2 1 1
3 15 1 3
4 7 2 4
5 3 2 2
6 1 2 1
7 6 2 3
8 8 2 5

Thanks to give me a SQL query that can help me or the fantastic SQL instruction that missed me...

TorF

 

[gregsimpson]

As a start point for the first query, does the following come close to what you want.

select id,A,B
from TABLE
where (B,A) IN
(SELECT B,MIN(A)
from TABLE
GROUP BY B)
ORDER BY ID

With DB2 you can use RANK to get close to the results you want for the second part of the query. I know this is an ANSI forum, but it always helps to state which DBMS you are using, in case there is not an ANSI solution, but maybe a Vendor specific solution.

 

[TorF]

Thanks for query and comments.

I'm using Access and RANK is not recognize.
Is there something equivalent ?

TorF

 

[Guest_imported]

For the second part, if you're using Access then you can do the following:
(I'll assume your first table is called T1)

select id, A, B
into T2
from T1
order by B, A asc;

giving:

id A B
2 2 1
1 5 1
3 15 1
6 1 2
5 3 2
7 6 2
4 7 2
8 7 2

Go into table design and add an autonumber field called id2 (starting at 1 incrementing by 1).

giving:

id A B id2
2 2 1 1
1 5 1 2
3 15 1 3
6 1 2 4
5 3 2 5
7 6 2 6
4 7 2 7
8 7 2 8

select B, min(id2) min_id2
into T3
from T2
group by B;

giving:

B min_id2
1 1
2 4


select T2.id, T2.A, T2.B, (T2.id2 - T3.id2 + 1)C
from T2, T3
where T2.B = T3.B
order by T2.id;

This gives you your result.

 

[ThomVF]

In ORACLE,

Query1:
select avg(id), min(cola), colb
from example
group by colb;

AVG(ID) MIN(COLA) COLB
---------- ---------- ----------
2 2 1
6 1 2

Query2:
Using a cursor, this can be done easily using PL/SQL

=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[karluk]

ThomVF's suggestion is very clever, but based on my testing it won't work. The function avg appears to calculate an average over all rows with the same value of colb, not over the single row with minumum value in cola. I guess that's what one would expect, but it's easy to convince oneself that the min function is somehow restricting the number of rows that are being averaged.

 

[ThomVF]

I stand corrected,it only works for the case where there is one instance of the minimum. If there are more than one, you get the average of the minimum-valued id's,not the id value itself.

I apologize for not running that one through. =================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[karluk]

Sorry, but it's even worse than that. The reason I decided to test was because of the possibility of more than one row with a minimum value of colA. However the test clearly showed that the average is taken over all rows with identical values in colB (the group by column). That makes your suggestion unworkable in all but a few special cases.

 

[tlbroadbent]

The Access Queries would be as follows.

Select Row Matching Minimum A Value:
SELECT t.ID, t.A, t.B
FROM TekTest AS t
INNER JOIN [SELECT B, Min(A) AS MinA
FROM TekTest
GROUP BY TekTest.B]. AS q
ON (t.A=q.MinA) AND (t.B=q.B);

Rank Rows:
SELECT
TekTest.ID, TekTest.A,
TekTest.B,
(Select count(*) From TekTest As t
Where t.B=TekTest.B And t.A<=TekTest.A) AS C
FROM TekTest;

I recommend that you join Tek-Tips rather than continuing to post as a visitor. It is free and there are several benefits to membership including email notification of replies to your posts and easy access to all posts that you have made.

I also recommend posting Access Query questions in forum701. Jet SQL is somewhat non-compliant with the ANSI standard. It is better to ask Access Jet SQL questions in the correct forum. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.