Re: SELECT'ing one row from duplicates
From: Steve Kass (skass_at_drew.edu)
Date: 09/01/04
- Next message: oj: "Re: returning error from sp using sp_executesql"
- Previous message: Pankaj Agarwal [MSFT]: "RE: Change in Statistics"
- In reply to: Tom Richards: "SELECT'ing one row from duplicates"
- Next in thread: Tom Richards: "Re: SELECT'ing one row from duplicates"
- Reply: Tom Richards: "Re: SELECT'ing one row from duplicates"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 01 Sep 2004 16:44:02 -0400
Tom,
Since multiple rows contain the same value of KEY, KEY is not a
primary key for the table. If you have a primary key (and the
collations for whichever of KEY and primary_key_column are strings match
on both servers), you can do this:
select KEY, ADDRESS_LINE_1, ADDRESS_LINE_2
from Tom T1
where primary_key_column = (
select min(primary_key_column)
from Tom T2
where T2.KEY = T1.KEY
)
The solution you suggest will return NULL if there are no non-null
values of the column, and it can be made to give the same results on
both servers if you specify the same collation (i.e., write
MIN(ADDRESS_LINE_1 COLLATE Latin1_General_CS_AS), or whatever collation
you want). But it may not give you ADDRESS_LINE_1 and ADDRESS_LINE_2
values from the same row of the table. Additionally, if there is a
reason for having different collations for ADDRESS_LINE_1/2 on the two
different servers, you really don't have the same table structure on
each server, and it's not even clear what it means to want the same
answer from each server. If you get answer X from one server and answer
Y from the other, it could be that X=Y is true under the first server's
collation and X=Y is false under the second server's collation. The
notion of "same" for strings depends on the collation.
Steve Kass
Drew University
Tom Richards wrote:
>I have a table with a key and a number of fields which constitute an address
>
>eg:
>
>KEY ADDRESS_LINE_1 ADDRESS_LINE_2
>-------------------------------------------------
>FRED 1, HOG STREET HOGLAND
>FRED 2, HOG STREET HOGLANDIO
>FRED NULL NULL
>
>I need to select the key (ie FRED) and one of these addresses. However, the
>select will reside on 2 different sql server 2000 servers and needs to behave
>the same on both.
>
>Can I do something like
>
>SELECT KEY,
> MIN(ADDRESS1),
> MIN(ADDRESS2)
>FROM TABLE
>GROUP BY KEY
>
>Will the fact that MIN sorts in character order sequence ensure that I get
>the same row back on both servers? Also, what happens to NULLs? I'm hoping I
>get a NULL back rather than the row being ignored...
>
>Thanks for any help,
>Tom
>
>
>
>
- Next message: oj: "Re: returning error from sp using sp_executesql"
- Previous message: Pankaj Agarwal [MSFT]: "RE: Change in Statistics"
- In reply to: Tom Richards: "SELECT'ing one row from duplicates"
- Next in thread: Tom Richards: "Re: SELECT'ing one row from duplicates"
- Reply: Tom Richards: "Re: SELECT'ing one row from duplicates"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
