Solved

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

Posted on 2011-02-15
3
405 Views
Last Modified: 2012-06-21
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
Comment
Question by:erlythornton
  • 2
3 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 34903734
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
 

Author Comment

by:erlythornton
ID: 34903825
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
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 34903827
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now