How can I use Sql to Order By This Statement?

How can I order the list 'widgets_spec by number of widgets?

select distinct
     m.p_c_id
    ,(select distinct '<li>' +convert(varchar,widgets) + '<br> '
      from dbo.spec_master m2
          where m.p_c_id = m2.p_c_id and widgets is not null
          for xml path(''), type).value('.[1]', 'nvarchar(max)'
     ) as widgets_spec
from dbo.spec_master m
inner join dbo.ProductVaration pv on pv.p_c_id = m.p_c_id
inner join dbo.开发者_Python百科Varation v on v.varation_id = pv.varation_type_id
where v.varation_id  = 4
group by m.p_c_id

Right now output looks like:

<li>10<br> <li>12<br> <li>15<br> <li>8<br>

When I want it to look like:

<li>8<br> <li>10<br> <li>12<br> <li>15<br> 

Thanks for your help.

EDIT: I'm trying to order the internal select statement that concatenates the values.

You do not need both Distinct and Group By. You should use one or the other. In this case, I believe you have to use Group By for it to work.

Select m.p_c_id
    , (
        Select '<li>' + Cast( m2.num_of_lights As varchar(10)) + '<br /> '
        From dbo.spec_master As m2
        Where m.p_c_id = m2.p_c_id 
            And m2.num_of_lights Is Not Null
        Group By m2.num_of_lights   
        Order By m2.num_of_lights
        For Xml Path(''), type).value('.[1]', 'nvarchar(max)'
        ) As numLights_spec
From dbo.spec_master As m
    Inner Join dbo.ProductVaration As pv 
        On pv.p_c_id = m.p_c_id
    Inner Join dbo.Varation As v 
        On v.varation_id = pv.varation_type_id
Where v.varation_id  = 4
Group by m.p_c_id

  select distinct
     m.p_c_id
    ,(select distinct '<li>' +convert(varchar,num_of_lights) + '<br> '
      from dbo.spec_master m2
          where m.p_c_id = m2.p_c_id and num_of_lights is not null
      ORDER BY convert(varchar,num_of_lights)
     ) as numLights_spec
  from dbo.spec_master m
  inner join dbo.ProductVaration pv on pv.p_c_id = m.p_c_id
  inner join dbo.Varation v on v.varation_id = pv.varation_type_id
  where v.varation_id  = 4
  group by m.p_c_id
) As SubA

Some of the other answers here won't work, since ordering by the now-varchar num_of_lights will put '8' after '15' as is happening now. You want to order the numLights numerically, which isn't going to happen with those html tags around them. You can add a subselect to your subselect so that you order them, then select them with the tags around them. Example (not tested):

SELECT * FROM (
  select distinct
  m.p_c_id
 ,(select distinct '<li>' +convert(varchar,num_of_lights) + '<br> '
  from (select distinct p_c_id, num_of_lights from dbo.spec_master order by num_of_lights) m2
      where m.p_c_id = m2.p_c_id and num_of_lights is not null
      for xml path(''), type).value('.[1]', 'nvarchar(max)'
 ) as numLights_spec
from dbo.spec_master m
inner join dbo.ProductVaration pv on pv.p_c_id = m.p_c_id
inner join dbo.Varation v on v.varation_id = pv.varation_type_id
where v.varation_id  = 4
group by m.p_c_id

Personally, I'd just add the html tags in whatever back-end code is getting the result of the query.