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
406 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This is about my first experience with programming Arduino.
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

832 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