• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 413
  • Last Modified:

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
0
erlythornton
Asked:
erlythornton
  • 2
1 Solution
 
SharathData EngineerCommented:
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

0
 
erlythorntonAuthor Commented:
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'
0
 
SharathData EngineerCommented:
SELECT TOP 1 t1.* 
    FROM TABLENAME t1 
         JOIN (  SELECT TOP 1 DATASTORE, 
                              COUNT(* ) cnt 
                   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

0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now