Improve company productivity with a Business Account.Sign Up

x
?
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
Medium Priority
?
416 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 41

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 41

Accepted Solution

by:
Sharath earned 2000 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This article describes and provides a custom-made tool I wrote to give businesses a means of identifying commercial music content, without having to expend too much effort. Business recordings are easily identified from possibly illegal music files …
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Starting up a Project

606 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