Find an MSSQL Query to find the name of the 1st available row with the maximum count matching criteria.
Posted on 2011-02-15
I am trying to find the name of the datastore that has the most segments available. I need a query that will give me the 1st available Name from This table where the AssetState=’No’ AND Size = ‘small’ that has the most rows returned. That would indicate the datastore with the most capacity.
Currently this is returning the the Datastore that has the most capacity but I actually need the name column.
SELECT TOP 1 DATASTORE, COUNT(*) FROM TABLENAME WHERE SIZE='small' AND ASSETSTATE='No' GROUP BY DATASTORE ORDER BY 2 DESC
How do I get a Max count per datastore and then get the first available name?
Name Size AssetState Datastore
Datastore-1-01 small Reserved Datastore-1
Datastore-1-02 small No Datastore-1
Datastore-1-03 small No Datastore-1
Datastore-2-01 small No Datastore-2
Datastore-2-02 small No Datastore-2
Datastore-2-03 small No Datastore-2
Datastore-3-01 large Reserved Datastore-3
Datastore-3-02 large No Datastore-3
Datastore-3-03 large No Datastore-3
Datastore-4-01 large No Datastore-4
Datastore-4-02 large No Datastore-4
Datastore-4-03 large No Datastore-4