SQL Server query select 1 from each sub-group
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 开发者_如何学PythonMETHOD_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?
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
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.
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.
精彩评论