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

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.
searchsanjaysharmaAsked:
Who is Participating?
 
Patrick MatthewsCommented:
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

0
 
Kevin CrossChief Technology OfficerCommented:
searchsanjaysharma,

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.
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
HainKurtSr. System AnalystCommented:
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...
0
 
Patrick MatthewsCommented:
mwvisa1,

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".

:)

Patrick
0
 
Kevin CrossChief Technology OfficerCommented:
*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*
0
 
Parth MalhanPrincipal EngineerCommented:
If i got oyur question correctly,then the following will surely work:

Try the following:

select villagecode,max(enrollmentdate),familyid,enr_urn,
maincardserialno,samcardserialno,samcsn,enrolled
from villagemaster  where enrolled=1 group by villagecode,familyid,enr_urn,
 maincardserialno,samcardserialno,samcsn,enrolled order by 1,2,3,4
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
searchsanjaysharmaAuthor Commented:
ok
0
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.

All Courses

From novice to tech pro — start learning today.