How to pick the last row of group by query result in sql server 2005

Posted on 2010-09-10
Last Modified: 2012-05-10
select villagecode,enrollmentdate,familyid,enr_urn, maincardserialno,samcardserialno,samcsn,enrolled
from villagemaster  where enrolled=1 group by villagecode,enrollmentdate,familyid,enr_urn,
maincardserialno,samcardserialno,samcsn,enrolled order by 1,2,3,4

Here how to pick only the last record of every villagecode with its greatest enrollmentdate  and other parameters.
Question by:searchsanjaysharma
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 92

Accepted Solution

Patrick Matthews earned 125 total points
ID: 33651698
Try this:

select v1.villagecode, v1.enrollmentdate, v1.familyid, v1.enr_urn, v1.maincardserialno, 
    v1.samcardserialno, v1.samcsn, v1.enrolled
from villagemaster v1 INNER JOIN
    (SELECT v2.villagecode, MAX(v2.enrollmentdate) AS enrollmentdate
    FROM villagemaster v2
    WHERE v2.enrolled = 1
    GROUP BY v2.villagecode) z ON v1.villagecode = z.villagecode AND v1.enrollmentdate = z.enrollmentdate
where v1.enrolled = 1 
group by v1.villagecode, v1.enrollmentdate, v1.familyid, v1.enr_urn, v1.maincardserialno, 
    v1.samcardserialno, v1.samcsn, v1.enrolled 
order by v1.villagecode, v1.enrollmentdate, v1.familyid, v1.enr_urn

Open in new window

LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33651722

Since you have SQL Server 2005, what may help you is the row_number() windowing function and over() analytical clause.

Here is a reference on how this works:
Analytical SQL : Where do you rank?

How you apply this to your scenario is to determine the field(s) which group the rows in question.  Sounds like it is "villagecode".  This becomes the PARTITION BY portion of over() clause.  Then specify how the rows should be ranked (sorted).  "greatest enrollmentdate" equates to "enrollmentdate desc" as the ORDER BY portion of over().

You can additionally specify secondary or tertiary order by field(s) to handle for example when you have multiple rows with same enrollmentdate if you want specific row chosen based on some other value.

Putting that all together, you will end up with:

row_number() over(partition by villagecode order by enrollmentdate desc) as rn

Then you can select outer query where rn = 1.
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33651733
Sorry Patrick, I was typing.  The derived aggregate table solution should work great also ; however, I think it relieves the need for the outer group by as does the row_number() solution by the way, searchsanjaysharma.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 52

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 63 total points
ID: 33651757
try this

select * from (
select villagecode,enrollmentdate,familyid,enr_urn, maincardserialno,samcardserialno,samcsn,enrolled
row_number() over (partition by villagecode order by enrollmentdate desc,familyid,enr_urn, maincardserialno) rn
from villagemaster  
where enrolled=1
) x where rn=1

sorry if it is re-post...
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 total points
ID: 33651778

No worries :)

In truth, I still haven't gotten completely comfortable with the new additions to T-SQL that came along in SQL 2005, such as PIVOT/UNPIVOT, ROW_NUMBER, PARTITION/OVER, common tabe expressions, etc.

So, you'll still see me kicking it old-school.  I just comfort myself by saying, "at least the dinosaurs stuck on SQL 2000 can still use my solutions".


LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33651814
*laughing* No I know what you mean.  I still find I used the derived table approach myself a lot because it is more portable to other variants of SQL other than MS SQL and additionally within the T-SQL family regardless of version.  If I had seen your posting, I would have probably not posted further unless to show an alternative.  I worked on SQL 2005 all day long, so sometimes that pops to happens to be what pops to mind. *smile*

Assisted Solution

parthmalhan earned 62 total points
ID: 33652262
If i got oyur question correctly,then the following will surely work:

Try the following:

select villagecode,max(enrollmentdate),familyid,enr_urn,
from villagemaster  where enrolled=1 group by villagecode,familyid,enr_urn,
 maincardserialno,samcardserialno,samcsn,enrolled order by 1,2,3,4
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33652860
Note: That will work ONLY if every other field is the same (i.e., each row is unique only by the entrollmentdate); otherwise, you will simply get a list of all rows for each villagecode and enrollmentdate combination.

Author Closing Comment

ID: 33655763

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 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