How to select max row per group in SQL

Posted on 2009-07-09
Medium Priority
Last Modified: 2012-05-07
Here is some sample data from the case_text table

company_id    case_id    text_type_code    case_text_seq
MIS                  1              Resolution             21
MIS                  1              Resolution             22
MIS                  2              Resolution             5
MIS                  2              Resolution             6
MIS                  2              Resolution             4

I am trying to create a query that will return the row with the highest case_text_seq value for a given combination of company_id,  case_id,  and  text_type_code    

Using the sample data above, I would like the query results to look like the following:

company_id    case_id    text_type_code    case_text_seq
MIS                  1              Resolution             22
MIS                  2              Resolution             6
Question by:johnnyg123
1 Comment
LVL 75

Accepted Solution

Aneesh Retnakaran earned 2000 total points
ID: 24815434
SELECT company_id   , case_id  ,  text_type_code ,  MAX( case_text_seq)case_text_seq
from case_Table
GROUP BY  company_id   , case_id  ,  text_type_code

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

627 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