sql: return MIN values only once for each line

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Armando - 29 Sep 2004 12:59 GMT

Hi SQL experts (I hope),

I am confronted with sth for which I have trouble thinking of an
answer w/o using subqueries. I'm sure it'll use MIN() & Group By - but
there I'll have to pass...

A resultset is like this:

c1PK c2 c3
AAA - 1 - 10000 <-- = lowest of aaa
AAA - 1 - 10000
AAA - 2 - 10001
AAB - 1 - 09999 <-- = lowest of aab
AAB - 3 - 89389
CCC - 4 - 98989 <-- = lowest of ccc

I would like to see the LOWEST VALUE of C2 for each line only once,
i.e.
AAA - 1 - 10000
AAB - 1 - 09999
CCC - 4 - 98989

Note: I am using a NCR Teradata DB.

Can you help me out ???
THX!

Armando,
Netherlands

Reply to this Message

Laconic2 - 29 Sep 2004 14:02 GMT

> Hi SQL experts (I hope),
>
[quoted text clipped - 25 lines]
> Armando,
> Netherlands

The first answer I come up with uses a sub query.

Why do you want to avoid subqueries?
Is it to make the code easier to read?
Is it to improve performance?
Did the homework assignment rule out subqueries?

Reply to this Message

Dieter Nöth - 29 Sep 2004 16:10 GMT

> I am confronted with sth for which I have trouble thinking of an
> answer w/o using subqueries. I'm sure it'll use MIN() & Group By - but
[quoted text clipped - 17 lines]
>
> Note: I am using a NCR Teradata DB.

You can use OLAP functions from Standard SQL 1999:
select *
from tab
qualify row_number() over
(partition by c1pk order by c2) = 1

or
qualify row_number() over
(partition by c1pk order by c2, c3) = 1

If you're pre V2R5 there's no row_number:
qualify sum(1) over
(partition by c1pk order by c2) = 1

Dieter

Reply to this Message

Armando - 30 Sep 2004 10:58 GMT

> > I am confronted with sth for which I have trouble thinking of an
> > answer w/o using subqueries. I'm sure it'll use MIN() & Group By - but
[quoted text clipped - 33 lines]
>
> Dieter

Thanks Dieter,

I don't exactly know yet what the 'Partition By' does and I will look
into this, but it seems to work just fine (on V2R5)!

Regards,
Armando

Reply to this Message

Kristian Damm Jensen - 29 Sep 2004 20:45 GMT

> Hi SQL experts (I hope),
>
[quoted text clipped - 21 lines]
>
> Can you help me out ???

First I have to ask you a question.

It data like this possible:

c1PK c2 c3
AAA - 1 - 10000
AAA - 1 - 10002

i.e. is c3 independent of c2?

If - as I expect - the answer is yes, then which of the two lines
above would you like to return?

If the answer is no then

select c1PK, min(c2), c3
from Set
group by c1PK, c3

will do the trick.

Regards,
Kristian

Reply to this Message

Armando - 30 Sep 2004 07:23 GMT

> > Hi SQL experts (I hope),
> >
[quoted text clipped - 45 lines]
> Regards,
> Kristian

Hi Kristian,

Thx for the swift response!
The answer is _yes_ they're independent. (The 2nd option isn't
sufficient then.)

It'll probably help to show the real situation:

Contract_Nr Version Status_Sort* Status
1100141145    1        3 arc
1100141145    1        3 can
1100142899    1        1 act
1100243970    3        1 act
1100243970    1        3 can
1100243970    2        3 wfa

Note: Status_Sort is generated thru a "Case when", and creates a value
based on a series of statuses, e.g. "either WFA or UC result in 2" and
"CAN or ARC result in 3", therefore they can appear more than once.

I only want the (contract_nr, version) returned of which the status is
lowest, i.e.
1100141145,1
1100243970,3
etc.

Thanks once again,
Armando

Reply to this Message

Kristian Damm Jensen - 30 Sep 2004 11:48 GMT

<snip>

> It'll probably help to show the real situation:
>
[quoted text clipped - 15 lines]
> 1100243970,3
> etc.

But then the solution is even simpler

select contract_nr, min(version)
from Tab
group by contract_nr

Regards,
Kristian

Reply to this Message

Tokunaga T. - 30 Sep 2004 16:13 GMT

> It'll probably help to show the real situation:
>
[quoted text clipped - 15 lines]
> 1100243970,3
> etc.

How about this?
SELECT t1.Contract_Nr
, t1.Version
FROM Contract_Status t1
, Contract_Status t2
WHERE t2.Contract_Nr = t1.Contract_Nr
GROUP BY
t1.Contract_Nr, t1.Status, t1.Version
HAVING
MIN(t2.Status) = t1.Status
;

Reply to this Message