Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • 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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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