What is this called, and how to do it? |
|
Author |
Message |
erezy
|
Posted: Thu Jan 12 08:54:36 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
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?
SQL Server164
|
|
|
|
|
SQL
|
Posted: Thu Jan 12 08:54:36 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
If you don't care which one do this
select Item,max(detail) from #TestJoins
group by Item
http://sqlservercode.blogspot.com/
|
|
|
|
|
Rick
|
Posted: Thu Jan 12 08:58:50 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
Try the following:
SELECT Item,
MIN(Detail)
FROM #TestJoins
GROUP BY Item
Rick Sawtell
MCT, MCSD, MCDBA
|
|
|
|
|
RyanPowers
|
Posted: Thu Jan 12 09:03:02 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
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
> 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?
>
>
>
|
|
|
|
|
Ian
|
Posted: Thu Jan 12 09:30:52 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
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
|
|
|
|
|
RyanPowers
|
Posted: Thu Jan 12 11:38:05 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
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
> 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
>
>
>
|
|
|
|
|
RyanPowers
|
Posted: Thu Jan 12 11:45:03 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
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
> 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
>
>
>
> > 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
> >
> >
> >
|
|
|
|
|
Jim
|
Posted: Thu Jan 12 12:03:31 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
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)
> 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
>
>
|
|
|
|
|
Ian
|
Posted: Fri Jan 13 12:02:51 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
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
> 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)
>
>
>> 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
>>
>>
>
>
|
|
|
|
|
Steve
|
Posted: Fri Jan 13 12:45:38 CST 2006 |
Top |
SQL Server Developer >> What is this called, and how to do it?
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
> 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
>
|
|
|
|
|
|
|