check for matching adress - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

check for matching adress

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-23 : 11:12:03

I do have a table with 5000 records. I do have to make sure all the records that have same shipid has same ship_address1, ship_address2 values. what is the better approach to do this. should i use cursor?
can you post sample query to do this.

Table1:
Ship_Id Ship_addr1 ship_addr2
123 fairmont drive fairmont
123 jackson drive fairmont
123 fairmont drive fairmont
234 ocean ln. florida
234 florida

End result should pull out all the records because the records under 123, 234 ship ids doesnt have same ship_addr1.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-23 : 11:14:30

to get all shipid's where there is a discrepancy

SELECT ShipID FROM Table1
GROUP BY ShipID
HAVING MIN(ShipAddr1 + char(9) + ShipAddr2) < MAX(ShipAddr1 + char(9) + ShipAddr2)

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-23 : 11:16:02

select t1.*
from Table1 t1
join
(select Ship_id
from Table1
group by ship_id
having count(distinct ship_addr1 > 1 or count(distinct ship_addr2) > 1
) a
on t1.ship_id = a.ship_id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-23 : 11:26:00

peso...How does min and max functions work in this case...I didnt understand

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-23 : 11:37:24

Just looks at the min value for the group and the max value - if they aren't equal then something is different.
It would have problems if there were nulls though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-23 : 11:40:10

I didnt know that we can use <, > operators while comparing strings??? can you tell me whts the difference if we use < and > operator.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-23 : 12:36:52

< means less than, > means greater than.

Being able to use min and max implies that < and > are meaningful.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-23 : 13:26:17

thanks a lot guys.

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-24 : 11:41:14

One more question....how does min() and max() functions work on strings. does it count the number of characters in a string and return one with less characters as min one and other as max??? or based on ascii values of string???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 11:45:02

It sorts them just as you sorts your DVD by name or your books by name.
Then it picks the "first" and "last" book by name.

E 12°55'05.25"
N 56°04'39.16"

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-24 : 11:46:41

I didnt get you...on what criteria does the sql server sort???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 11:49:32

The letters that build the name of the book.
Just as the letter in a record in a database phrase something.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 11:51:56

How do you sort the two dvds "Rainman" and "Matrix"?
You look at the first letter and see that "m" comes before "r". Now you can tell that "Matrix" is placed before "Rainman".

Are you with me?

SQL Server does exactly the same thing, but faster.
When sorting all records alphabetically, it returns the "first" for MIN and "last" for MAX function.

E 12°55'05.25"
N 56°04'39.16"

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-24 : 11:57:53

Thanks...got it...
Any idea how sql server interprets strings. Say we type "peso". how does it reads it and understands that it is "peso". Through ascii values?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-24 : 12:03:40

yes. and collation settings.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:04:25

Yes, depending on COLLATION setting for that columns.
If you have an CASE SENSITIVE collation, "peso" is not equal to "Peso" (using binary representation).
If you have an CASE INSENSITIVE collation, "peso" is equal to "Peso" (using character representation).

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:04:42

again...

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-24 : 12:06:02

3 in a day.. wow!!! that's like.. never happened before

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-24 : 12:08:23

Thanks guys...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 12:17:28

Yes. I must get my two caskets off my arms soon...

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources