CODEWITHSUNDEEP
sqlpostgresql
user638514
user638514

Reputation: 31

SQL -- How to select a unique field with another field associated with min value

There is a table that has three attributes:

TableA:
     -- Attribute1
     -- Attribute2
     -- Attribute3

Now I want to get all three attributes out, with distinct Attribute2 that has the minimum value of Attribute3

Here is the SQL statement that I can come up with:

select TableA.Attribute1, TableA.Attribute2, TableA.Attribute3
from TableA, 
(select Attribute2, min(Attribute3) as minAttribute3
from TableA
group by Attribute2) as TableB
where TableA.Attribute2 = TableB.Attribute2
and TableA.Attribute3= TableB.minAttribute3

Example table data is as follows:

Attribute1  Attribute2  Attribute3
att1_001    att2_001    1
att1_002    att2_001    2
att1_003    att2_001    3
att1_004    att2_002    10
att1_005    att2_002    11

Here is the expected output result:

Attribute1  Attribute2  Attribute3
att1_001    att2_001    1
att1_004    att2_002    10

I am sure this is not an optimized one and am looking for one:)

Thank you in advance

Upvotes: 0

Views: 321

Answers (2)

user533832
user533832

Reputation:

How about using a windowing function to rank the rows:

create view v as 
select * from (values ('att1_001', 'att2_001', 1), 
                      ('att1_002', 'att2_001', 2), 
                      ('att1_003', 'att2_001', 3), 
                      ('att1_004', 'att2_002', 10), 
                      ('att1_005', 'att2_002', 11)) as foo(a1, a2, a3);

select * from v;

    a1    |    a2    | a3
----------+----------+----
 att1_001 | att2_001 |  1
 att1_002 | att2_001 |  2
 att1_003 | att2_001 |  3
 att1_004 | att2_002 | 10
 att1_005 | att2_002 | 11
(5 rows)

select a1, a2, a3
from ( select a1, a2, a3, row_number() over (partition by a2 order by a3) as ord
       from v ) z 
where ord=1;

    a1    |    a2    | a3
----------+----------+----
 att1_001 | att2_001 |  1
 att1_004 | att2_002 | 10
(2 rows)

Note that whatever approach you use, you need to consider what results you want when there is a tie. This approach will choose in an arbitrary way between tied rows - yours will expand the results.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

In PostgreSQL I'd do this.

select TableA.Attribute1, TableA.Attribute2, TableA.Attribute3
from TableA
inner join (select Attribute2, min(Attribute3) as minAttribute3
            from TableA
            group by Attribute2) t
on  TableA.Attribute2 = t.Attribute2 
and TableA.Attribute3 = t.minAttribute3

att1_001   att2_001    1
att1_004   att2_002   10

Whether this is optimal is impossible to say. Compare the query plans (EXPLAIN ANALYZE select-statement) of both statements on your server.

Upvotes: 1

Related Questions