What is this called, and how to do it?

microsoft.public.sqlserver.programming

12 Jan 2006 2:42 PM

Ian Boyd

It's a very common question:

Input
Item    Detail
====    ======
A    red
B    green
B    blue
C    red
C    blue
C    green
D    violet
D    blue
D    green
D    red
E    blue
E    violet
F    red

For every item, i want one one detail (i don't care which one):

Desired output
Item    Detail
====    ======

A    red
B    green
C    red
D    violet
E    blue
F    red

Simple DDL

CREATE TABLE #TestJoins (
Item varchar(50),
Detail varchar(50))
INSERT INTO #TestJoins (Item, Detail) VALUES ('A', 'red')
INSERT INTO #TestJoins (Item, Detail) VALUES ('B', 'green')
INSERT INTO #TestJoins (Item, Detail) VALUES ('B', 'blue')
INSERT INTO #TestJoins (Item, Detail) VALUES ('C', 'red')
INSERT INTO #TestJoins (Item, Detail) VALUES ('C', 'blue')
INSERT INTO #TestJoins (Item, Detail) VALUES ('C', 'green')
INSERT INTO #TestJoins (Item, Detail) VALUES ('D', 'violet')
INSERT INTO #TestJoins (Item, Detail) VALUES ('D', 'blue')
INSERT INTO #TestJoins (Item, Detail) VALUES ('D', 'green')
INSERT INTO #TestJoins (Item, Detail) VALUES ('D', 'red')
INSERT INTO #TestJoins (Item, Detail) VALUES ('E', 'blue')
INSERT INTO #TestJoins (Item, Detail) VALUES ('E', 'violet')
INSERT INTO #TestJoins (Item, Detail) VALUES ('F', 'red')

--SELECT * FROM #TestJoins

/*Desired output
==============
Item Detail
A red
B green
C red
D violet
E vlue
F red*/

/*This query doesn't work
    "An aggregate cannot appear in an ON clause unless it is in a subquery
contained in a HAVING clause or select list, and the column being aggregated
is an outer reference."
SELECT *
FROM #TestJoins t1
INNER JOIN #TestJoins t2
ON t1.Item = t2.Item
AND t1.Detail = MIN(t2.Detail)

DROP TABLE #TestJoins

First of all, what is what i want called? i see the question a lot in the
newsgroups - but now that i have to accomplish it, i can't find any. And i
also don't know what it would be called. Some ideas are:
    asymmetric self-join
    distinct with group by
    partial duplicate elimination
    group by with top

And next, what query accomplishes it?

12 Jan 2006 2:54 PM

SQL

If you don't care which one do this

select Item,max(detail) from #TestJoins
group by Item

http://sqlservercode.blogspot.com/

12 Jan 2006 2:58 PM

Rick Sawtell

Try the following:

SELECT Item,
MIN(Detail)
FROM #TestJoins
GROUP BY Item

Rick Sawtell
MCT, MCSD, MCDBA

12 Jan 2006 3:03 PM

Ryan Powers

I will leave it to others to give you a name for it. I am not sure what it
would be called.

Either of these SQLs would work to accomplish your goal.

SELECT Item, MIN(Detail) as Detail
FROM #TestJoins t1
GROUP BY Item
ORDER BY Item

SELECT Item, MAX(Detail) as Detail
FROM #TestJoins t1
GROUP BY Item
ORDER BY Item

HTH

--
Ryan Powers
Clarity Consulting
http://www.claritycon.com

Show quote

"Ian Boyd" wrote:

> It's a very common question:
>
> Input
> Item    Detail
> ====    ======
> A    red
> B    green
> B    blue
> C    red
> C    blue
> C    green
> D    violet
> D    blue
> D    green
> D    red
> E    blue
> E    violet
> F    red
>
> For every item, i want one one detail (i don't care which one):
>
> Desired output
> Item    Detail
> ====    ======
>
> A    red
> B    green
> C    red
> D    violet
> E    blue
> F    red
>
> Simple DDL
>
> CREATE TABLE #TestJoins (
> Item varchar(50),
> Detail varchar(50))
> INSERT INTO #TestJoins (Item, Detail) VALUES ('A', 'red')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('B', 'green')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('B', 'blue')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('C', 'red')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('C', 'blue')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('C', 'green')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('D', 'violet')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('D', 'blue')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('D', 'green')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('D', 'red')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('E', 'blue')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('E', 'violet')
> INSERT INTO #TestJoins (Item, Detail) VALUES ('F', 'red')
>
> --SELECT * FROM #TestJoins
>
> /*Desired output
>   ==============
> Item Detail
> A red
> B green
> C red
> D violet
> E vlue
> F red*/
>
> /*This query doesn't work
>     "An aggregate cannot appear in an ON clause unless it is in a subquery
> contained in a HAVING clause or select list, and the column being aggregated
> is an outer reference."
> SELECT *
> FROM #TestJoins t1
> INNER JOIN #TestJoins t2
> ON t1.Item = t2.Item
> AND t1.Detail = MIN(t2.Detail)
>
> DROP TABLE #TestJoins
>
>
> First of all, what is what i want called? i see the question a lot in the
> newsgroups - but now that i have to accomplish it, i can't find any. And i
> also don't know what it would be called. Some ideas are:
>     asymmetric self-join
>     distinct with group by
>     partial duplicate elimination
>     group by with top
>
> And next, what query accomplishes it?
>
>
>

12 Jan 2006 3:30 PM

Ian Boyd

Damn, i simplified my case too much, and you guys found the simple solution!

Consider the following, (or pretend that the order does matter, that there
is a date column and i want the oldest record)

CREATE TABLE #TestJoins (
Item varchar(50),
City varchar(50),
State varchar(50))
INSERT INTO #TestJoins (Item, City, State) VALUES ('A', 'Detroit', 'MI')
INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'ON')
INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'CA')
INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Morehead City',
'NC')
INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Newport', 'NC')
INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Bogue Pines', 'NC')
INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Mesa', 'AZ')
INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Tempe', 'AZ')
INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Flagstaff', 'AZ')
INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'San Antonio', 'TX')
INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Aberdeen', 'MD')
INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Baltimore', 'MD')
INSERT INTO #TestJoins (Item, City, State) VALUES ('F', 'St. Louis', 'MO')

/*Desired output:
Item City State
==== ==== =====
A Detroit MI
B Windsor ON
C Morehead City NC
D Mesa AZ
E Aberdeen MD
F St. Louis MO
*/

DROP TABLE #TestJoins

12 Jan 2006 5:38 PM

Ryan Powers

Well, that definitely makes it a bit trickier, as I am assuming that you need
to return a city and state from the same line.

I have come up with 2 ways that should work. The first is a bit more
clever, but the sql is uglier (basically, I added a rownum to the table and
joined to the minimum). The second is a bit of a hack, but should work fine.

In both cases, I used distinct in the outer sql since I would get duplicates
if we have the same value for all 3 fields. Also, the first solution would
be cleaner if I could move the results of the query that adds rownums to your
table into a temp table or table variable. But, I worked with the assumption
that you needed one sql statement.

One other note, I didn't get your exact desired output, because I needed to
use the first (or I could have used last) alphabetic row for each item.

CREATE TABLE #TestJoins (
Item varchar(50),
City varchar(50),
State varchar(50))

INSERT INTO #TestJoins (Item, City, State) VALUES ('A', 'Detroit', 'MI')
INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'ON')
INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'CA')
INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Morehead City',
'NC')
INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Newport', 'NC')
INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Bogue Pines', 'NC')
INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Mesa', 'AZ')
INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Tempe', 'AZ')
INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Flagstaff', 'AZ')
INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'San Antonio', 'TX')
INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Aberdeen', 'MD')
INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Baltimore', 'MD')
INSERT INTO #TestJoins (Item, City, State) VALUES ('F', 'St. Louis', 'MO')

select DISTINCT derived_2.item, derived_2.City, derived_2.State
FROM
(Select Item, Min(rownum) minrow
    FROM
    (
        Select Item, City, State,
            (SELECT Count(*)
                FROM #TestJoins b
                WHERE a.item > b.item
                OR a.item = b.item
                AND (a.City > b.City OR (a.City = b.City AND a.State >= b.State))
            ) as rownum
        FROM #TestJoins a) as inner_table
    GROUP By Item) as derived_1
INNER JOIN
(
    Select Item, City, State,
        (SELECT Count(*)
            FROM #TestJoins b
            WHERE a.item > b.item
            OR a.item = b.item
            AND (a.City > b.City OR (a.City = b.City AND a.State >= b.State))
        ) as rownum
    FROM #TestJoins a) as derived_2
ON derived_1.Item = derived_2.Item
AND derived_1.minrow = derived_2.rownum

Select DISTINCT outer_table.Item, outer_table.City, outer_table.State
FROM #TestJoins outer_table
INNER JOIN
    (Select Item, MIN(City+State) as CityState
    FROM #TestJoins a
    GROUP BY Item) as derived_1
ON outer_table.Item = derived_1.Item
AND outer_table.City + outer_table.State = derived_1.CityState

/*Desired output:
Item City State
==== ==== =====
A Detroit MI
B Windsor ON
C Morehead City NC
D Mesa AZ
E Aberdeen MD
F St. Louis MO
*/

DROP TABLE #TestJoins

HTH

--
Ryan Powers
Clarity Consulting
http://www.claritycon.com

Show quote

"Ian Boyd" wrote:

> Damn, i simplified my case too much, and you guys found the simple solution!
>
> Consider the following, (or pretend that the order does matter, that there
> is a date column and i want the oldest record)
>
> CREATE TABLE #TestJoins (
> Item varchar(50),
> City varchar(50),
> State varchar(50))
> INSERT INTO #TestJoins (Item, City, State) VALUES ('A', 'Detroit', 'MI')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'ON')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'CA')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Morehead City',
> 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Newport', 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Bogue Pines', 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Mesa', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Tempe', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Flagstaff', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'San Antonio', 'TX')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Aberdeen', 'MD')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Baltimore', 'MD')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('F', 'St. Louis', 'MO')
>
> /*Desired output:
> Item City State
> ==== ==== =====
> A Detroit MI
> B Windsor ON
> C Morehead City NC
> D Mesa AZ
> E Aberdeen MD
> F St. Louis MO
> */
>
> DROP TABLE #TestJoins
>
>
>

12 Jan 2006 5:45 PM

Ryan Powers

Realized there is a simpler rownum solution by using ranking within item
instead of overall rownum.

Here it is.

        Select DISTINCT Item, City, State
        FROM (Select Item, City, State,
            (SELECT Count(*)
                FROM #TestJoins b
                WHERE a.item = b.item
                AND (a.City > b.City OR (a.City = b.City AND a.State >= b.State))
            ) as rownum
        FROM #TestJoins a) as derived_table
        WHERE rownum = 1

--
Ryan Powers
Clarity Consulting
http://www.claritycon.com

Show quote

"Ryan Powers" wrote:

> Well, that definitely makes it a bit trickier, as I am assuming that you need
> to return a city and state from the same line.
>
> I have come up with 2 ways that should work. The first is a bit more
> clever, but the sql is uglier (basically, I added a rownum to the table and
> joined to the minimum). The second is a bit of a hack, but should work fine.
>
> In both cases, I used distinct in the outer sql since I would get duplicates
> if we have the same value for all 3 fields. Also, the first solution would
> be cleaner if I could move the results of the query that adds rownums to your
> table into a temp table or table variable. But, I worked with the assumption
> that you needed one sql statement.
>
> One other note, I didn't get your exact desired output, because I needed to
> use the first (or I could have used last) alphabetic row for each item.
>
> CREATE TABLE #TestJoins (
> Item varchar(50),
> City varchar(50),
> State varchar(50))
>
> INSERT INTO #TestJoins (Item, City, State) VALUES ('A', 'Detroit', 'MI')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'ON')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'CA')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Morehead City',
> 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Newport', 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Bogue Pines', 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Mesa', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Tempe', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Flagstaff', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'San Antonio', 'TX')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Aberdeen', 'MD')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Baltimore', 'MD')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('F', 'St. Louis', 'MO')
>
>
> select DISTINCT derived_2.item, derived_2.City, derived_2.State
> FROM
> (Select Item, Min(rownum) minrow
>     FROM
>     (
>         Select Item, City, State,
>             (SELECT Count(*)
>                 FROM #TestJoins b
>                 WHERE a.item > b.item
>                 OR a.item = b.item
>                 AND (a.City > b.City OR (a.City = b.City AND a.State >= b.State))
>             ) as rownum
>         FROM #TestJoins a) as inner_table
>     GROUP By Item) as derived_1
> INNER JOIN
> (
>     Select Item, City, State,
>         (SELECT Count(*)
>             FROM #TestJoins b
>             WHERE a.item > b.item
>             OR a.item = b.item
>             AND (a.City > b.City OR (a.City = b.City AND a.State >= b.State))
>         ) as rownum
>     FROM #TestJoins a) as derived_2
> ON derived_1.Item = derived_2.Item
> AND derived_1.minrow = derived_2.rownum
>
> Select DISTINCT outer_table.Item, outer_table.City, outer_table.State
> FROM #TestJoins outer_table
> INNER JOIN
>     (Select Item, MIN(City+State) as CityState
>     FROM #TestJoins a
>     GROUP BY Item) as derived_1
> ON outer_table.Item = derived_1.Item
> AND outer_table.City + outer_table.State = derived_1.CityState
>
>
> /*Desired output:
> Item City State
> ==== ==== =====
> A Detroit MI
> B Windsor ON
> C Morehead City NC
> D Mesa AZ
> E Aberdeen MD
> F St. Louis MO
> */
>
> DROP TABLE #TestJoins
>
> HTH
>
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
>
> "Ian Boyd" wrote:
>
> > Damn, i simplified my case too much, and you guys found the simple solution!
> >
> > Consider the following, (or pretend that the order does matter, that there
> > is a date column and i want the oldest record)
> >
> > CREATE TABLE #TestJoins (
> > Item varchar(50),
> > City varchar(50),
> > State varchar(50))
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('A', 'Detroit', 'MI')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'ON')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'CA')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Morehead City',
> > 'NC')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Newport', 'NC')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Bogue Pines', 'NC')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Mesa', 'AZ')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Tempe', 'AZ')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Flagstaff', 'AZ')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'San Antonio', 'TX')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Aberdeen', 'MD')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Baltimore', 'MD')
> > INSERT INTO #TestJoins (Item, City, State) VALUES ('F', 'St. Louis', 'MO')
> >
> > /*Desired output:
> > Item City State
> > ==== ==== =====
> > A Detroit MI
> > B Windsor ON
> > C Morehead City NC
> > D Mesa AZ
> > E Aberdeen MD
> > F St. Louis MO
> > */
> >
> > DROP TABLE #TestJoins
> >
> >
> >

12 Jan 2006 6:03 PM

Jim Underwood

One of the problems with your original query was that you had an agregate
function in your where clause. You need to use HAVING instead of WHERE to
put a condition on an agregate function. You also had no group by, which
would also give you problems.

That said, you can accomplish what you need with subqueries, although I'm
not certain if this would be considered ANSI compliant SQL. Basically you
set your where clause to select the min value of field 1 joining on whatever
you want to us as a key in the result set. Then you select the min value of
field 2, joining on your chosen key AND field 1. You can continue in this
way for as many fields as you need.

SELECT t1.Item, t1.city, t1.state
FROM #TestJoins t1
where t1.city = (select min(t2a.city) from #TestJoins t2a
                 where t2a.item = t1.item)
and t1.state = (select min(t2b.state) from #TestJoins t2b
                where t2b.item = t1.item
                and t2b.city=t1.city)

If you have a unique key in the table, you would select the min(PK) based on
Item.
i.e. lets say you have an additional Identity field (we'll call it MyID) in
#TestJoins. the query would be simplified to this...

SELECT t1.Item, t1.city, t1.state
FROM #TestJoins t1
where t1.city = (select min(t2a.MyID) from #TestJoins t2a
                 where t2a.item = t1.item)

Show quote

"Ian Boyd" <ian.msnews***@avatopia.com> wrote in message
news:OPvZt04FGHA.1312@TK2MSFTNGP09.phx.gbl...
> Damn, i simplified my case too much, and you guys found the simple
solution!
>
> Consider the following, (or pretend that the order does matter, that there
> is a date column and i want the oldest record)
>
> CREATE TABLE #TestJoins (
> Item varchar(50),
> City varchar(50),
> State varchar(50))
> INSERT INTO #TestJoins (Item, City, State) VALUES ('A', 'Detroit', 'MI')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'ON')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'CA')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Morehead City',
> 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Newport', 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Bogue Pines',
'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Mesa', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Tempe', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Flagstaff', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'San Antonio',
'TX')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Aberdeen', 'MD')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Baltimore', 'MD')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('F', 'St. Louis', 'MO')
>
> /*Desired output:
> Item City State
> ==== ==== =====
> A Detroit MI
> B Windsor ON
> C Morehead City NC
> D Mesa AZ
> E Aberdeen MD
> F St. Louis MO
> */
>
> DROP TABLE #TestJoins
>
>

13 Jan 2006 6:02 PM

Ian Boyd

Thank you Jim, and Ryan.

Even though i don't know what this kind of querying is called, at least now
i can search google for "ian boyd asymmetric join" and i'll always find this
reference.

Even though i don't know what th
Show quote

"Jim Underwood" <james.underw***@fallonclinic.com> wrote in message
news:%23k%23uAK6FGHA.2444@TK2MSFTNGP11.phx.gbl...
> One of the problems with your original query was that you had an agregate
> function in your where clause. You need to use HAVING instead of WHERE to
> put a condition on an agregate function. You also had no group by, which
> would also give you problems.
>
> That said, you can accomplish what you need with subqueries, although I'm
> not certain if this would be considered ANSI compliant SQL. Basically you
> set your where clause to select the min value of field 1 joining on
> whatever
> you want to us as a key in the result set. Then you select the min value
> of
> field 2, joining on your chosen key AND field 1. You can continue in this
> way for as many fields as you need.
>
> SELECT t1.Item, t1.city, t1.state
> FROM #TestJoins t1
> where t1.city = (select min(t2a.city) from #TestJoins t2a
>                 where t2a.item = t1.item)
> and t1.state = (select min(t2b.state) from #TestJoins t2b
>                where t2b.item = t1.item
>                and t2b.city=t1.city)
>
> If you have a unique key in the table, you would select the min(PK) based
> on
> Item.
> i.e. lets say you have an additional Identity field (we'll call it MyID)
> in
> #TestJoins. the query would be simplified to this...
>
> SELECT t1.Item, t1.city, t1.state
> FROM #TestJoins t1
> where t1.city = (select min(t2a.MyID) from #TestJoins t2a
>                 where t2a.item = t1.item)
>
>
> "Ian Boyd" <ian.msnews***@avatopia.com> wrote in message
> news:OPvZt04FGHA.1312@TK2MSFTNGP09.phx.gbl...
>> Damn, i simplified my case too much, and you guys found the simple
> solution!
>>
>> Consider the following, (or pretend that the order does matter, that
>> there
>> is a date column and i want the oldest record)
>>
>> CREATE TABLE #TestJoins (
>> Item varchar(50),
>> City varchar(50),
>> State varchar(50))
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('A', 'Detroit', 'MI')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'ON')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'CA')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Morehead City',
>> 'NC')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Newport', 'NC')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Bogue Pines',
> 'NC')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Mesa', 'AZ')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Tempe', 'AZ')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Flagstaff',
>> 'AZ')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'San Antonio',
> 'TX')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Aberdeen', 'MD')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Baltimore',
>> 'MD')
>> INSERT INTO #TestJoins (Item, City, State) VALUES ('F', 'St. Louis',
>> 'MO')
>>
>> /*Desired output:
>> Item City State
>> ==== ==== =====
>> A Detroit MI
>> B Windsor ON
>> C Morehead City NC
>> D Mesa AZ
>> E Aberdeen MD
>> F St. Louis MO
>> */
>>
>> DROP TABLE #TestJoins
>>
>>
>
>

13 Jan 2006 6:45 PM

Steve Kass

Ian,

If you want the oldest row for each value of Item, try

select * from yourTable
where datecolumn = (
select min(datecolumn)
from yourTable as T
where T.Item = yourTable.Item
)

This will give you ties if there are multiple "oldest" rows for an item.

Steve Kass
Drew University

Show quote

"Ian Boyd" <ian.msnews***@avatopia.com> wrote in message
news:OPvZt04FGHA.1312@TK2MSFTNGP09.phx.gbl...
> Damn, i simplified my case too much, and you guys found the simple
> solution!
>
> Consider the following, (or pretend that the order does matter, that there
> is a date column and i want the oldest record)
>
> CREATE TABLE #TestJoins (
> Item varchar(50),
> City varchar(50),
> State varchar(50))
> INSERT INTO #TestJoins (Item, City, State) VALUES ('A', 'Detroit', 'MI')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'ON')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('B', 'Windsor', 'CA')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Morehead City',
> 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Newport', 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('C', 'Bogue Pines',
> 'NC')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Mesa', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Tempe', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'Flagstaff', 'AZ')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('D', 'San Antonio',
> 'TX')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Aberdeen', 'MD')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('E', 'Baltimore', 'MD')
> INSERT INTO #TestJoins (Item, City, State) VALUES ('F', 'St. Louis', 'MO')
>
> /*Desired output:
> Item City State
> ==== ==== =====
> A Detroit MI
> B Windsor ON
> C Morehead City NC
> D Mesa AZ
> E Aberdeen MD
> F St. Louis MO
> */
>
> DROP TABLE #TestJoins
>