Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-09-10
9
Medium Priority
?
562 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
[X]
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
9 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 375 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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 750 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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 750 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 60

Assisted Solution

by:HainKurt
HainKurt earned 189 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
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 375 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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 750 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 186 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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 750 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

Independent Software Vendors: 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!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

636 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