Tuesday, March 20, 2012

Problem finding values with aggregate functions

Hi all!

In a statement I want to find the IDENTITY-column value for a row that
has the smallest value. I have tried this, but for the result i also
want to know the row_id for each. Can this be solved in a neat way,
without using temporary tables?

CREATE TABLE some_table
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,

row_value integer,
row_name varchar(30)
)
GO
/* DROP TABLE some_table */

insert into some_table (row_name, row_value) VALUES ('Alice', 0)
insert into some_table (row_name, row_value) VALUES ('Alice', 1)
insert into some_table (row_name, row_value) VALUES ('Alice', 2)
insert into some_table (row_name, row_value) VALUES ('Alice', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 2)
insert into some_table (row_name, row_value) VALUES ('Bob', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 4)
insert into some_table (row_name, row_value) VALUES ('Celine', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 6)

select min(row_value), row_name from some_table group by row_nameJon wrote:
> Hi all!
> In a statement I want to find the IDENTITY-column value for a row
that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?
> CREATE TABLE some_table
> (
> row_id INTEGER
> NOT NULL
> IDENTITY(1,1)
> PRIMARY KEY,
> row_value integer,
> row_name varchar(30)
> )
> GO
> /* DROP TABLE some_table */
> insert into some_table (row_name, row_value) VALUES ('Alice', 0)
> insert into some_table (row_name, row_value) VALUES ('Alice', 1)
> insert into some_table (row_name, row_value) VALUES ('Alice', 2)
> insert into some_table (row_name, row_value) VALUES ('Alice', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 2)
> insert into some_table (row_name, row_value) VALUES ('Bob', 3)
> insert into some_table (row_name, row_value) VALUES ('Bob', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 4)
> insert into some_table (row_name, row_value) VALUES ('Celine', 5)
> insert into some_table (row_name, row_value) VALUES ('Celine', 6)
> select min(row_value), row_name from some_table group by row_name

*Assuming* that row_name/row_value combinations are unique, then it
would be:

select row_id,row_value,row_name from some_table t1 inner join (select
min(row_value) as row_value, row_name from some_table group by
row_name) t2 on t1.row_value = t2.row_value and t1.row_name =
t2.row_name

*Is* my assumption correct? If not, then there's some additional
grouping on the outer query, and a decision to be made on which row_id
to return (e.g. Min())|||Jon (jonsjostedt@.hotmail.com) writes:
> In a statement I want to find the IDENTITY-column value for a row that
> has the smallest value. I have tried this, but for the result i also
> want to know the row_id for each. Can this be solved in a neat way,
> without using temporary tables?

Yes:

select s.row_id, x.min_value, x.row_name
from some_table s
join (select min_value = min(row_value), row_name
from some_table
group by row_name) as x on x.min_value = s.row_value
and x.row_name = s.row_name

What you see there is a *derived table*. A derived is sort of a temp
table within the query, but it is never materialized. In fact, the
optimizer may recast the computation order as long as this does not
affect the result.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

No comments:

Post a Comment