Return these records, and *ONE* matching record for each...

Return these records, and *ONE* matching record for each...

Post by Dan Guzma » Mon, 20 Aug 2007 22:43:55


Thanks for the DDL.


You can specify an aggregate function like MIN or MAX if you don't have
specific criteria of which value you want:

SELECT
MIN(
COALESCE(MatchingNames.Name, NamesEndingInDash.Name)) AS Name
FROM (
SELECT Name
FROM dbo.TmpTable
WHERE RIGHT(Name, 1) = '-'
) AS NamesEndingInDash
LEFT JOIN dbo.TmpTable AS MatchingNames ON
MatchingNames.Name LIKE NamesEndingInDash.Name + '%' AND
MatchingNames.Name > NamesEndingInDash.Name
GROUP BY
NamesEndingInDash.Name

It seems to me that the need to parse names like this is symptomatic of a
database design issue. This query shows that there is a relationship here
but the relationship data (first name segment) is not stored atomically.

--
Hope this helps.

Dan Guzman
SQL Server MVP
 
 
 

Return these records, and *ONE* matching record for each...

Post by » Mon, 20 Aug 2007 23:42:12

an,


I fully agree. Unfortunately, this is a very old database that is 1 of
those
"designed by someone else" and is "too late to change now" situations.

Anyway... thanks for the help... and the near instant reply. Wow.


"Dan Guzman" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...



 
 
 

Return these records, and *ONE* matching record for each...

Post by Pawel Pota » Mon, 20 Aug 2007 23:59:10

Another solution:

SELECT TT.Name
FROM (
SELECT Name
FROM TmpTable
WHERE RIGHT(Name,1) = '-') T
INNER JOIN TmpTable TT
ON T.Name = LEFT(TT.Name,CHARINDEX('-',TT.Name))
WHERE T.Name = TT.Name
OR TT.Name = (SELECT MAX(Name)
FROM TmpTable
WHERE LEFT(Name,CHARINDEX('-',Name)) = T.Name)

--
Regards
Pawel Potasinski
[ http://www.yqcomputer.com/ ]


Utkownik <"NewCoderGuy"> napisaw wiadomoi
>>I have a MS-SQL 2000 database with records like: >> >> DOG- >> CAT- >> BIRD- >> FISH- >> DOG-47 >> DOG-48 >> CAT-AAA >> CAT-123 >> HORSE-BBB >> >> I need a SELECT statement that will give me a list of all the records >> that end in "-"... and 1 (doesn't matter which) of its matching >> counterparts. >> >> So the results would produce: >> DOG- >> DOG-47 (DOG-48 is NOT needed here) >> CAT- >> CAT-AAA (CAT-123 is NOT needed here) >> BIRD- (No matching record, so just return the "- ending" >> record) >> FISH- (No matching record, so just return the "- ending" >> record) >> (HORSE-BBB is not included at all, because >> there's no "- ending" record) >> >> Thanks. >> >> ============================================ >> >> >> CREATE TABLE TmpTable >> ( >> Name VarChar(30) >> ) >> >> INSERT INTO TmpTable(Name) VALUES('DOG-') >> INSERT INTO TmpTable(Name) VALUES('CAT-') >> INSERT INTO TmpTable(Name) VALUES('BIRD-') >> INSERT INTO TmpTable(Name) VALUES('FISH-') >> INSERT INTO TmpTable(Name) VALUES('DOG-47') >> INSERT INTO TmpTable(Name) VALUES('DOG-48') >> INSERT INTO TmpTable(Name) VALUES('CAT-AAA') >> INSERT INTO TmpTable(Name) VALUES('CAT-123') >> INSERT INTO TmpTable(Name) VALUES('HORSE-BBB') >> >> SELECT * FROM TmpTable >> DROP TABLE TmpTable >> >> >> >>