Get Match Data On top

Question

I have multiple like operator on different columns as below:

SELECT d.ID ,d.DealerCode,d.AffiliationCode,d.LegalName,d.ShipperCode,d.PrimaryUserId,d.PrimaryContactId,              
       FROM Dealer AS d              
       LEFT JOIN Country c on c.Id=d.CountryId              
       LEFT JOIN [User] u on u.Id=d.PrimaryUserId              
       LEFT JOIN RegistrationSource rs on rs.Id=d.RegistrationSourceId              
       WHERE d.TenantId='4c0a42de-6ce5-4faa-8b5d-da4d7c3294b4' AND 
       (
        u.UserName LIKE '%AQ%' 
        OR d.DealerCode LIKE '%AQ%' 
        OR d.LegalName LIKE '%AQ%' 
        OR d.ShipperCode LIKE '%AQ%'
       ) 
       ORDER BY d.DealerCode ASC

Is it possible to find that row set first(on top) which have exact match value with any one of these column value

Replace `LIKE '%AQ%'` on `= 'AQ'`? – Uriil Jun 27 '14 at 06:54 — Uriil, Jun 27 '14 at 06:54

score 0 · Answer 1 · answered Jun 27 '14 at 07:07

Try a UNION - that should allow you to list two sets of data one after the other (untested):

SELECT d.ID 
       ,d.DealerCode,d.AffiliationCode,d.LegalName, ...
   FROM Dealer AS d              
   LEFT JOIN Country c on c.Id=d.CountryId              
   LEFT JOIN [User] u on u.Id=d.PrimaryUserId              
   LEFT JOIN RegistrationSource rs on rs.Id=d.RegistrationSourceId              
   WHERE d.TenantId='4c0a42de-6ce5-4faa-8b5d-da4d7c3294b4' AND 
   (
    u.UserName = 'AQ' 
    OR d.DealerCode = 'AQ' 
    OR d.LegalName = 'AQ' 
    OR d.ShipperCode = 'AQ'
   ) 
UNION
   SELECT d.ID , d.DealerCode,d.AffiliationCode, ...
   FROM Dealer AS d              
   LEFT JOIN Country c on c.Id=d.CountryId              
   LEFT JOIN [User] u on u.Id=d.PrimaryUserId              
   LEFT JOIN RegistrationSource rs on rs.Id=d.RegistrationSourceId              
   WHERE d.TenantId='4c0a42de-6ce5-4faa-8b5d-da4d7c3294b4' AND 
   (
    u.UserName LIKE '%AQ%' 
    OR d.DealerCode LIKE '%AQ%' 
    OR d.LegalName LIKE '%AQ%' 
    OR d.ShipperCode LIKE '%AQ%'
   ) 
   ORDER BY d.DealerCode ASC

If there is more than a "first one" you might have to restrict using TOP 1

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

You can use ORDER BY to make rows with exact match appears on top, for example :

......
......
ORDER BY
    (CASE WHEN 
        u.UserName = 'AQ' 
        OR d.DealerCode = 'AQ' 
        OR d.LegalName = 'AQ' 
        OR d.ShipperCode = 'AQ'
    THEN 0
    ELSE 1
    END), d.DealerCode

Related question : MySQL order by "best match"

edited May 23 '17 at 12:29

Community

1
1

answered Jun 27 '14 at 07:14

har07

88,338
12
84
137

score 0 · Answer 3 · answered Jul 02 '14 at 07:08

DECLARE @OrderByFeild VarChar(25)
DECLARE @OrderByOrder VarChar(5)

SELECT @OrderByFeild = SUBSTRING(@OrderBy, 1, CHARINDEX(' ', @OrderBy) - 1),
@OrderByOrder = SUBSTRING(@OrderBy, CHARINDEX(' ', @OrderBy) + 1, LEN(@OrderBy))

IF @OrderByFeild = 'DealerCode' and @OrderByOrder = 'ASC' BEGIN

--Your Select Statement Here..
    ORDER BY CASE 
     WHEN d.DealerCode LIKE ''+@SearchQuery+''  THEN 5 
     WHEN d.DealerCode LIKE '%'+@SearchQuery+'%' THEN 4 
     WHEN u.UserName LIKE '%'+@SearchQuery+'%' THEN 3 
     WHEN d.LegalName LIKE '%'+@SearchQuery+'%' THEN 2
     WHEN d.ShipperCode LIKE '%'+@SearchQuery+'%' THEN 1   END
END

ELSE IF @OrderByFeild = 'DealerCode' BEGIN

--Your Select Statement Here..
    ORDER BY CASE 
     WHEN d.DealerCode LIKE ''+@SearchQuery+''  THEN 1 
     WHEN d.DealerCode LIKE '%'+@SearchQuery+'%' THEN 2 
     WHEN u.UserName LIKE '%'+@SearchQuery+'%' THEN 3 
     WHEN d.LegalName LIKE '%'+@SearchQuery+'%' THEN 4
     WHEN d.ShipperCode LIKE '%'+@SearchQuery+'%' THEN 5   END

END

ELSE BEGIN

    --Your Select Statement Here..
    -- ANY Other sorting you need to do
END

Get Match Data On top

3 Answers3