Problem solve Get help with specific problems with your technologies, process and projects.

Selecting first row from each group ordered by three columns

How can I select the first row from each group ordered by three columns?

I have a summary table:

 server_type server bad good unknown =========== ====== === ==== ======= Web s1 3 2 7 WebLogic s1 6 1 0 Web s3 9 0 2 Web s1 0 0 12 WebLogic s2 17 8 1 WebLogic s3 8 1 0
My requirement is that I want to find the row that has the maximum bad duration for combination of servertype and server. In the above example, if we take server type as web, we have two distinct servers s1 and s3. For this combination of servertype and server, I should select the row that has the maximum bad. If there is a tie, then it should max unknown and then max good.

Required output is:

 Web s3 9 0 2 WebLogic s2 17 8 1

You can do this query without a primary key on your table but it is a lot less verbose if you have one. In general all tables should have a primary key even if it is an arbitrary number. So for this example I use an identity column.

First I create a table that matches yours (except the PK):

 create table #MyTable ( idcol int identity(1,1) PRIMARY KEY, server_type varchar(20), server varchar(10), bad int, good int, unknown int ) insert #MyTable(server_type,server,bad,good,unknown) values('Web','s1',3,2,7) insert #MyTable(server_type,server,bad,good,unknown) values('WebLogic','s1',6,1,0) insert #MyTable(server_type,server,bad,good,unknown) values('Web','s3',9,0,2) insert #MyTable(server_type,server,bad,good,unknown) values('Web','s1',0,0,12) insert #MyTable(server_type,server,bad,good,unknown) values('WebLogic','s2',17,8,1) insert #MyTable(server_type,server,bad,good,unknown) values('WebLogic','s3',8,1,0)
Then all I need to do is run the following query:
 select * From #MyTable mt1 WHERE IdCol=(Select top 1 IDCol From #MyTable mt2 WHERE mt1.server_type=mt2.server_type ORDER BY mt2.bad desc, mt2.unknown desc, mt2.good desc)
Of course, instead of this temporary table you will use the permanent one within your database.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.