none
Order by Problem while fetching records from two Tables

    Question

  • Hii to all I have a query

     SELECT '[' + Isnull( feature.features, '') + '] [' 
                 + Isnull( subfeature.subfeature, '') 
                 + ']['
                 + CONVERT(VARCHAR, Isnull( subfeature.estimatedhour, 0)) 
                 + ']'                    AS FeatureSubName,
    			 feature.featureid        AS FeatureId, 
                  
                 feature.features         AS Feature, 
                 subfeature.subfeature    AS SubFeature, 
                 subfeature.description   AS Description, 
                ISNULL(subfeature.estimatedhour,0)  AS EstimHour,
    			 SubFeature.SubFeatureId as SubFeatureId
          FROM   feature 
                 LEFT JOIN subfeature 
                        ON feature.featureid = subfeature.featureid 
          WHERE  feature.isdeleted = 'False' 
                 AND feature.features LIKE '%registration%' 
                  OR subfeature.subfeature LIKE '%registration%' 
          ORDER  BY feature.features

    and I am getting Output

    According to me output is not Correct. I wants the selected rows should come Firstly

    I am searching for registration, so what i wants that it should retrieve firstly the exact match of String i am passing after that the rest.

    Where i am doing mistake 

    pls tell

    Thanks in Advance


    Niki

    Thursday, March 21, 2013 2:20 PM

Answers

  • I assume that "registration" really is a parameter. In this case you could do:

     SELECT '[' + Isnull( feature.features, '') + '] ['
                 + Isnull( subfeature.subfeature, '')
                 + ']['
                 + CONVERT(VARCHAR, Isnull( subfeature.estimatedhour, 0))
                 + ']'                    AS FeatureSubName,
              feature.featureid        AS FeatureId,

                 feature.features         AS Feature,
                 subfeature.subfeature    AS SubFeature,
                 subfeature.description   AS Description,
                ISNULL(subfeature.estimatedhour,0)  AS EstimHour,
              SubFeature.SubFeatureId as SubFeatureId
          FROM   feature
                 LEFT JOIN subfeature
                        ON feature.featureid = subfeature.featureid
          WHERE  feature.isdeleted = 'False'
             AND (feature.features LIKE '%' + @search + '%'
                  OR subfeature.subfeature LIKE '%' + @search + '%')
          ORDER  BY nullif(feature.features, @search)

    NULL sorts first in SQL Server why this works.

    Note that I added parenteses around the OR terms as I assume that this is wnat you mean.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by nikijain Thursday, March 21, 2013 2:41 PM
    Thursday, March 21, 2013 2:27 PM
  • NULLIF - Returns a null value if the two specified expressions are equal. - http://msdn.microsoft.com/en-IN/library/ms177562.aspx

    so in your case if you pass @search = 'Registration'

    and   ORDER  BY nullif(feature.features, @search) = ORDER  BY nullif(Registration, Registration) ,

    so above condition will be treated as NULL and it will sorted first like you desired.

    And the other searches which is not exactly Registration comes next...

    Thanks to Erland :)


    Thanks & Regards, sathya


    Thursday, March 21, 2013 2:51 PM
    Moderator

All replies

  • I assume that "registration" really is a parameter. In this case you could do:

     SELECT '[' + Isnull( feature.features, '') + '] ['
                 + Isnull( subfeature.subfeature, '')
                 + ']['
                 + CONVERT(VARCHAR, Isnull( subfeature.estimatedhour, 0))
                 + ']'                    AS FeatureSubName,
              feature.featureid        AS FeatureId,

                 feature.features         AS Feature,
                 subfeature.subfeature    AS SubFeature,
                 subfeature.description   AS Description,
                ISNULL(subfeature.estimatedhour,0)  AS EstimHour,
              SubFeature.SubFeatureId as SubFeatureId
          FROM   feature
                 LEFT JOIN subfeature
                        ON feature.featureid = subfeature.featureid
          WHERE  feature.isdeleted = 'False'
             AND (feature.features LIKE '%' + @search + '%'
                  OR subfeature.subfeature LIKE '%' + @search + '%')
          ORDER  BY nullif(feature.features, @search)

    NULL sorts first in SQL Server why this works.

    Note that I added parenteses around the OR terms as I assume that this is wnat you mean.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by nikijain Thursday, March 21, 2013 2:41 PM
    Thursday, March 21, 2013 2:27 PM
  • Hi Niki,

    You have explictily given the order clause.. "ORDER  BY feature.features". So it is sorting my Features column and then by FeatureId by default.

    Regards,

    Brindha.

    Thursday, March 21, 2013 2:30 PM
  • Yes, the expectation is incorrect.

    The like in where clause doesn't filter the output, doesn't define order of the data.]

    In the order by 'feature.features' is used , so the output will be in the order of 'feature.features' value.


    Thanks Sarat --Please use Marked as Answer if my post solved your problem and use Vote As Helpful if the post was useful.

    Thursday, March 21, 2013 2:32 PM
  • Hi Niki,

    ORDER BY Ascending or Descending is a simple case.

    If you have any conditions in ordering ,you can try ORDER BY with CASE statement. http://msdn.microsoft.com/en-IN/library/ms188385.aspx


    Thanks & Regards, sathya




    Thursday, March 21, 2013 2:34 PM
    Moderator
  • Thanks that Worked. But one thing to ask what does nullif do

    Niki


    • Edited by nikijain Thursday, March 21, 2013 2:44 PM Question
    Thursday, March 21, 2013 2:42 PM
  • NULLIF - Returns a null value if the two specified expressions are equal. - http://msdn.microsoft.com/en-IN/library/ms177562.aspx

    so in your case if you pass @search = 'Registration'

    and   ORDER  BY nullif(feature.features, @search) = ORDER  BY nullif(Registration, Registration) ,

    so above condition will be treated as NULL and it will sorted first like you desired.

    And the other searches which is not exactly Registration comes next...

    Thanks to Erland :)


    Thanks & Regards, sathya


    Thursday, March 21, 2013 2:51 PM
    Moderator