select * from (
SELECT distinct n.*,b.pkid FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ) tem ORDER BY CASE WHEN CONTAINS(*, @str1) THEN 1 WHEN (BrandId=PKId and CONTAINS(name, @str1)) THEN 2 ELSE 3 END
SELECT distinct n.*,b.pkid FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ) tem ORDER BY CASE WHEN CONTAINS(*, @str1) THEN 1 WHEN (BrandId=PKId and CONTAINS(name, @str1)) THEN 2 ELSE 3 END
SELECT distinct n.* FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1))
) temp
ORDER BY CASE WHEN CONTAINS(n.*, @str1) THEN 1 WHEN (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) THEN 2 ELSE 3 END
as OrderByCol FROM goods n,Brands b WHERE CONTAINS(n.*, @str1) or (n.BrandId=b.PKId and CONTAINS(b.name, @str1)) ORDER BY OrderByCol