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.
Author |
Topic |
Neven1986
Starting Member
6 Posts |
Posted - 2013-01-23 : 18:51:39
|
Hello,i was wondering if someone could give me advice on my problem.I have 2 tables. One called PrefixList and one called CustomerHistory.PrefixListID | Prefix(varchar)---------------1 | 38512 | 385 | CustomerHistoryCustID | OrderNo (varchar)------------------------ 1 | 38512345 2 | 38554321 | What I want to do is to compare Prefix column from PrefixList table, with the begining of string in OrderNo column from CustomerHistory table. But that should be best match. For example: OrderNo 38512345 should be compared with Prefix 3851 beacuse first 4 chars are identical, rather than with Prefix 385 which have just 3 identical chars.My main concern is how to do this: I could iterate through both tables and compare each row from one table with each row from second table, but I think this could be overkill for DB. Specially because CustomerHistory could have 500k rows and PrefixList could have 100 rows. Processing 500000 * 100 rows can have huge impact on DB.Should I use temp tables, table variables or maybe cursors I'm little lost here, do you have any suggestions?Thank you |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-23 : 19:14:24
|
[code]SELECT ch.CustID, ch.OrderNo, pl.PrefixFROM CustomerHistory ch OUTER APPLY ( SELECT TOP (1) pl.Prefix FROM PrefixList pl WHERE ch.OrderNo LIKE pl.Prefix+'%' ORDER BY LEN(Prefix) DESC ) pl;[/code] |
|
|
Neven1986
Starting Member
6 Posts |
Posted - 2013-01-26 : 18:32:26
|
quote: Originally posted by James K
SELECT ch.CustID, ch.OrderNo, pl.PrefixFROM CustomerHistory ch OUTER APPLY ( SELECT TOP (1) pl.Prefix FROM PrefixList pl WHERE ch.OrderNo LIKE pl.Prefix+'%' ORDER BY LEN(Prefix) DESC ) pl;
James thank you very much for your answer, it didn't work for me as I expected. But it gave me idea in which direction should I go. As someone wise said: I didn't saw the forest, because of trees.Sorry for late response.The solution i found working is:SELECT ch.CustID, ch.OrderNo, pl.PrefixFROM CustomerHistory ch OUTER APPLY ( SELECT TOP (1) pl.Prefix FROM PrefixList pl WHERE SUBSTRING(ch.OrderNo,1,LEN(pl.Prefix)) = pl.Prefix ORDER BY LEN(Prefix) DESC ) pl; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-27 : 14:02:56
|
[code]SELECT *FROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY ch.OrderNo ORDER BY LEN(pl.Prefix) DESC) AS SeqFROM CustomerHistory chINNER JOIN PrefixList plON ch.OrderNo LIKE pl.Prefix+'%')tWHERE seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-27 : 14:07:06
|
why LIKE didnt work for you? its equivalent to logic you've written using substring.whats the datatype of OrderNo and Prefix fields?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-27 : 16:54:26
|
Just seconding what Visakh said - logically the substring query and the like query should be equivalent, if the data type of your columns are VARCHAR(nn) or NVARCHAR(nn) The reason LIKE clause would be better than the substring is that, with the like clause SQL Server can potentially use an index on the OrderNo column if there is one, but it cannot with the substring method. |
|
|
|
|
|
|
|