SQL Server query select 1 from each sub-group

Question

I have a set of data and need to pull out one record for each CON / OWNER / METHOD / MATRIX set. If there is a non-null RESULT, I want that one. Otherwise, I want the one with the highest COUNT. How do I query 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 a result, I would like just the starred records, and I'm showing 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

...because my count isn't part of the aggregate function. Nor does it deal with the RESULT being NULL or not, and top (1) won't return 1 from each grouping. However, I've not got farther by using a more complex query (such as based on the question at How can I select multiple columns from a subquery (in SQL Server) that should have one record (select top 1) for each record in the main query?)

How do I select one from each grouping?

It's hard to choose which to accept, @siva, @Paul Creasey, or @Andrew Lazarus. All helped. Andrew's was early and nicely formatted and had the better sorting; Siva's had the T1.Ranking qualifier at the end; and Paul was first and got me going. I gave an upvote to all, and since Paul's is slightly ahead, I'll accept it. But all are worthy. — thursdaysgeek, Jun 07 '11 at 23:55

Paul Creasey · Accepted Answer · 2011-06-06T22:43:08.337

11

Try this, not 100% sure the syntax is right, but it is close.

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

edited Jun 06 '11 at 22:43

answered Jun 06 '11 at 22:14

Paul Creasey

28,321
10
54
90

I have no knowledge that we can write the query in such a way too. Thanks it solves many problems of mine. – Moons Oct 19 '11 at 04:48

score 2 · Answer 2 · answered Jun 06 '11 at 22:23

In an RDBMS that supports windowing (includes SQL Server 2008, I believe)

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;

Note that your text implies only one not-null result per group but your example doesn't. This version will select the maximum result unless all results are the same (e.g., NULL) when count will be the tiebreaker. BTW, COUNT as a column name is going to cause all sorts of grief. This version also selects multiple rows in case of ties in both result and count. Switch rank to row_number to get only one regardless of whether there is a tie.

Oh yeah, I shouldn't have used COUNT as a name. I was anonymizing it, so it didn't have our exact names. — thursdaysgeek, Jun 06 '11 at 23:24

score 0 · Answer 3 · edited Dec 14 '12 at 10:02

0

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 
    )

You can use it as view and join.

edited Dec 14 '12 at 10:02

Cerbrus

70,800
18
132
147

answered Dec 14 '12 at 09:45

Namdar Tariq Faruqi

1

SQL Server query select 1 from each sub-group

3 Answers3

Linked