select the top record in each group

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select the top record in each group

Author

Topic

sarorelasoul
Starting Member

29 Posts

Posted - 2009-07-08 : 16:21:01

Hi,

I have a query where I need to select the first record for each student's petition(a student can have more than one petition).
Here is the query:
select x.gdw_id,x.petitiondate,x.student_acad_pgm_cd as program_cd,x.term_cd,x.acad_year
from(
select a.gdw_id,a.term_cd,b.petitiondate,
CASE WHEN RIGHT(a.term_cd, 1) >= 8 THEN substring(a.term_cd, 2, 4) + '-' + CAST(CAST(substring(a.term_cd, 2, 4) AS int) + 1 AS varchar)
ELSE CAST(CAST(substring(a.term_cd, 2, 4) AS int) - 1 AS varchar) + '-' + substring(a.term_cd, 2, 4) END AS acad_year,
case when right(term_cd,1) = '8' then cast(substring(term_cd,2,4)+'-08-23' as datetime)
when right(term_cd,1) = '6' then cast(substring(term_cd,2,4)+'-06-10' as datetime)
when right(term_cd,1) = '5' then cast(substring(term_cd,2,4)+'-05-15' as datetime)
else cast(substring(term_cd,2,4)+'-01-15'as datetime) end as Date,
student_acad_pgm_cd,student_acad_pgm_name,b.explanation
from [GDW].[dbo].[GDW_STUDENT_AH_TERM] a right join Petition.dbo.petitioninfo b
on GDW_ID = b.icard
where b.petitionStatus = 'active' and (b.petitiontype = 'Re-entry' or b.petitiontype = 'Change curriculum')and
b.status = 'Approved and notified' and student_acad_pgm_cd like '10K%' and gdw_id is not null
)x,gdw.dbo.gdw_pers y left outer join gdw.dbo.gdw_race_eth_cd as e on y.race_eth_cd = e.race_eth_cd where x.petitiondate<date and x.gdw_id=y.gdw_id
group by x.student_acad_pgm_cd,x.term_cd,x.acad_year,x.gdw_id,x.petitiondate
order by x.gdw_id,x.petitiondate

The result is currently:
850000211 2002-05-02 16:30:56.000 10KS0325PHD 120028 2002-2003
850000211 2002-05-02 16:30:56.000 10KS0325PHD 120031 2002-2003
850000211 2002-05-02 16:30:56.000 10KS0325PHD 120036 2002-2003
950430489 2004-04-09 09:08:24.000 10KS0328MS 120046 2003-2004
950430489 2004-04-09 09:08:24.000 10KS0328MS 120048 2004-2005
950430489 2004-04-09 09:08:24.000 10KS0329PHD 120058 2005-2006
950430489 2004-04-09 09:08:24.000 10KS0329PHD 120061 2005-2006
950430489 2004-04-09 09:08:24.000 10KS0329PHD 120068 2006-2007

I need the result to be:
850000211 2002-05-02 16:30:56.000 10KS0325PHD 120028 2002-2003
950430489 2004-04-09 09:08:24.000 10KS0328MS 120046 2003-2004
950430489 2004-04-09 09:08:24.000 10KS0329PHD 120058 2005-2006

I tried using the top keyword but it seems I'm putting it in the wrong place, it eliminated the result to only one record.

Any help?

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-08 : 16:41:55

how do you define

"first record for each student's petition"

?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sarorelasoul
Starting Member

29 Posts

Posted - 2009-07-08 : 22:33:41

I mean each student submits a petition or more then he is registered in a program for a term or more, I need the first term after the petetition.
so for student whose id = 850000211, I need the query to return the first record of his records only, for the other student who has has 2 petitions each with a different date , I need the query to return the first record in this petition as follows
950430489 2004-04-09 09:08:24.000 10KS0328MS 120046 2003-2004
950430489 2004-04-09 09:08:24.000 10KS0329PHD 120058 2005-2006
in another way or each group(id and petitiondate) I need the first record only to be returned.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-09 : 04:21:03

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:12:52

[code]
select x.gdw_id,x.petitiondate,x.student_acad_pgm_cd as program_cd,x.term_cd,x.acad_year INTO #Temp
from(
select a.gdw_id,a.term_cd,b.petitiondate,
CASE WHEN RIGHT(a.term_cd, 1) >= 8 THEN substring(a.term_cd, 2, 4) + '-' + CAST(CAST(substring(a.term_cd, 2, 4) AS int) + 1 AS varchar)
ELSE CAST(CAST(substring(a.term_cd, 2, 4) AS int) - 1 AS varchar) + '-' + substring(a.term_cd, 2, 4) END AS acad_year,
case when right(term_cd,1) = '8' then cast(substring(term_cd,2,4)+'-08-23' as datetime)
when right(term_cd,1) = '6' then cast(substring(term_cd,2,4)+'-06-10' as datetime)
when right(term_cd,1) = '5' then cast(substring(term_cd,2,4)+'-05-15' as datetime)
else cast(substring(term_cd,2,4)+'-01-15'as datetime) end as Date,
student_acad_pgm_cd,student_acad_pgm_name,b.explanation
from [GDW].[dbo].[GDW_STUDENT_AH_TERM] a right join Petition.dbo.petitioninfo b
on GDW_ID = b.icard
where b.petitionStatus = 'active' and (b.petitiontype = 'Re-entry' or b.petitiontype = 'Change curriculum')and
b.status = 'Approved and notified' and student_acad_pgm_cd like '10K%' and gdw_id is not null
)x,gdw.dbo.gdw_pers y left outer join gdw.dbo.gdw_race_eth_cd as e on y.race_eth_cd = e.race_eth_cd where x.petitiondate<date and x.gdw_id=y.gdw_id
group by x.student_acad_pgm_cd,x.term_cd,x.acad_year,x.gdw_id,x.petitiondate

SELECT t.*
FROM #Temp t
INNER JOIN (SELECT gdw_id,petitiondate,MIN(term_cd) AS first
FROM #Temp
GROUP BY gdw_id,petitiondate)t1
ON t1.gdw_id=t.gdw_id
AND t1.petitiondate=t.petitiondate
AND t1.first=t.term_cd
order by t.gdw_id,t.petitiondate

DROP TABLE #Temp
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources