?
Solved

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

Posted on 2010-09-10
9
Medium Priority
?
556 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 58

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
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…

762 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