asked on

Find an MSSQL Query to find the name of the 1st available row with the maximum count matching criteria.

Hi,

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

Sharath S

SQL Server 2000? try like this.

SELECT TOP 1 t1.* 
    FROM TABLENAME t1 
         JOIN (  SELECT TOP 1 DATASTORE, 
                              COUNT(* ) 
                   FROM TABLENAME 
                  WHERE SIZE = 'small' 
                        AND ASSETSTATE = 'No' 
               GROUP BY DATASTORE 
               ORDER BY 2 DESC) t2 
           ON t1.DATASTORE = t2.DATASTORE 
ORDER BY t1.Name

Open in new window

erlythornton

ASKER

Can you check the syntax? Here is the error:
msg 8155, Level 16State 2, Line 1
no column was specified for column 2 of 't2'

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial