I have a table like this:

in which the code of symptoms(S) of three diseases(D) are shown. I want to rearrange this table (D-S) such that the diseases with more symptoms come up i.e. order it by decreasing the numbers of symptoms as below:

Can anyone help me to write a SQL code for it in SQL server?

I had tried to do this as the following but this doesn't work:

SELECT *
FROM ( 
    select D, Count(S) cnt
    from  [D-S]
    group by D  
)  Q
order by Q.cnt desc

CodePudding user response：

select
    D,
    S
from
    D-S
order by
    count(*) over(partition by D) desc, 
    D, 
    S;

CodePudding user response：

Two easy ways to approach this:

--==== Sample Data
DECLARE @t TABLE (D INT, S INT);
INSERT  @t VALUES(2,1),(2,3),(4,2),(4,3),(4,5),(6,1);

--==== Using Window Function
SELECT  t.D, t.S
FROM    (SELECT t.*, Rnk = COUNT(*) OVER (PARTITION BY t.D) FROM  @t AS t) AS t
ORDER BY t.Rnk DESC;

--==== Using standard GROUP BY
SELECT t.*
FROM  @t AS t
JOIN
(
  SELECT t2.D, Cnt = COUNT(*)
  FROM     @t AS t2
  GROUP BY t2.D
) AS t2 ON t.D = t2.D
ORDER BY t2.Cnt DESC;

Results:

D           S
----------- -----------
4           2
4           3
4           5
2           1
2           3
6           1