Solved

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

Posted on 2010-09-10
9
530 Views
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.
0
Comment
Question by:searchsanjaysharma
9 Comments
 
LVL 92

Accepted Solution

by:
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

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 33651722
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
 
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.
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt 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...
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 total points
ID: 33651778
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
 
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*
0
 
LVL 4

Assisted Solution

by:parthmalhan
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,
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
 
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.
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 33655763
ok
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now