Geek Answers Handbook

SQL Server query selects 1 from each subgroup

I have a data set and you need to pull out one record for each set of CON / OWNER / METHOD / MATRIX. If there is a nonzero RESULT, I want it. Otherwise, I want the one with the highest COUNT. How can I request this?

CON      OWNER      METHOD      MATRIX  RESULT  COUNT
*CON_1   OWNER_1    METHOD_A    SOLID   NULL    503
CON_1    OWNER_1    METHOD_A    SOLID   NULL    1

*CON_1   OWNER_1    METHOD_A    SOIL    NULL    1305
CON_1    OWNER_1    METHOD_A    SOIL    NULL    699

*CON_2   OWNER_2    METHOD_B    SOLID   290     687
CON_2    OWNER_2    METHOD_B    SOLID   NULL    NULL
CON_2    OWNER_2    METHOD_B    SOLID   450     600

CON_2    OWNER_2    METHOD_B    WATER   NULL    1
*CON_2   OWNER_2    METHOD_B    WATER   400     NULL

for the result, I would just like the marked entries, and I show how each set is grouped.

This is bad SQL:

select top (1) CON, OWNER, METHOD, MATRIX, RESULT, COUNT
from #TempTable
group by CON, OWNER, METHOD, MATRIX
order by CON, OWNER, METHOD, MATRIX, COUNT

... . , NULL, , (1) 1 . , , (, ( SQL Server), ( 1) ?)

?

+5
3

, 100% , , .

select 
    * 
from
    (select
        CON,
        OWNER,
        METHOD,
        MATRIX,
        RESULT,
        COUNT,
        RANK() OVER(PARTITION BY CON, OWNER, METHOD,MATRIX ORDER BY RESULT,COUNT DESC) as rnk
    FROM #TempTable
) a
WHERE rnk = 1
+11

, ( SQL Server 2008, )

SELECT CON, OWNER, METHOD, MATRIX, RESULT, `COUNT`
FROM
 (SELECT CON, OWNER, METHOD, MATRIX, RESULT, `COUNT`, 
  RANK() OVER (PARTITION BY CON, OWNER, METHOD, MATRIX
               ORDER BY RESULT DESC, `COUNT` DESC) AS rk
  FROM temptable
 ) AS s
WHERE rk=1;

, , , , . , (, NULL), count -. BTW, count . , . row_number, , , .

+2
select DetailID, field2, field3, field4, MasterID 
from table outer
where DetailID= 
    (
        select max(DetailID) 
        From table inner 
        where outer.MasterID = inner.MasterID
        group by MasterID 
    )

.

0

More articles:


All Articles