select pt_id,Papername,Papercost,p_Updateddate,Realcost, R_updatedDate from dbo.UpdatedPaperCosts12
where R_updatedDate<=getdate() and R_updatedDate in (select max(R_updatedDate) from dbo.UpdatedPaperCosts12 group by R_updatedDate )
group by pt_id,papername,Papercost,p_Updateddate,Realcost,R_updatedDate
which returns data like
Ptid papername Papercost p_Updateddate Realcost R_updatedDate A 250gsm_silk_sra3 NULL NULL 0.325 2012-11-08 12:34:15.820 A 250gsm_silk_sra3 NULL NULL 0.325 2012-11-08 12:34:50.857 A 250gsm_silk_sra3 0.037 2011-12-07 10:34:39.583 0.10 2011-12-07 10:34:39.583 B 160gsm_matt_sra3 NULL NULL 0.231 2012-11-08 12:27:33.390 B 160gsm_matt_sra3 NULL NULL 0.231 2012-11-08 12:29:41.773 B 160gsm_matt_sra3 NULL NULL 0.231 2012-11-08 12:30:38.080 B 160gsm_matt_sra3 NULL NULL 0.231 2012-11-08 12:32:58.950 B 160gsm_matt_sra3 NULL NULL 0.2345 2012-11-08 12:38:11.297
and so on.............................................................
i want only First Top Row of each group ie.. Top Row of A and Top row of B Group
How can i do this?
Narasappa C J
ravi.jadiyannavar@gmail.com
Pls Mark This Post As Answer If it Helps U..
Thanku
;with cte as
(
select
ID,
GroupID,
Qty,
rank() over(partition by GroupID order by ID) as myrank
from @mytable
)
select ID, GroupID, Qty--,myrank
from cte
where myrank = 1
==
use rank for this my above query will help you.,..
If this will help you please mark as answer
My Blog ROHIT KUMAR SRIVASTAVA
Marked as answer by narasappa on Nov 09, 2012 05:58 AM
select pt_id,Papername,Papercost,p_Updateddate,Realcost, R_updatedDate from dbo.UpdatedPaperCosts12
where R_updatedDate<=getdate() and R_updatedDate in (select max(R_updatedDate) from dbo.UpdatedPaperCosts12 group by pt_id
select top 1 pt_id,Papername,Papercost,p_Updateddate,Realcost, R_updatedDate
from dbo.UpdatedPaperCosts12
where R_updatedDate<=getdate() and
R_updatedDate in (select max(R_updatedDate) from dbo.UpdatedPaperCosts12 group by R_updatedDate )
and pt_id in (A,B,C,D)
group by pt_id,papername,Papercost,p_Updateddate,Realcost,R_updatedDate
with set1 as
(select pt_id,Papername,Papercost,p_Updateddate,Realcost, R_updatedDate,row_number() over (partition by pt_id order by R_updatedDate) rn
from dbo.UpdatedPaperCosts12
where R_updatedDate<=getdate() and R_updatedDate in (select max(R_updatedDate) from dbo.UpdatedPaperCosts12 group by R_updatedDate )
group by pt_id,papername,Papercost,p_Updateddate,Realcost,R_updatedDate)
select pt_id,Papername,Papercost,p_Updateddate,Realcost, R_updatedDate
from set1
where rn=1;
select pt_id,Papername,Papercost,Updateddate,Realcost, updatedDate
from (
select pt_id,Papername,Papercost,p_Updateddate,Realcost, R_updatedDate
, ROW_NUMBER() over (partition by pt_id order by R_updatedDate) as rownum
from dbo.UpdatedPaperCosts12
where R_updatedDate<=getdate()
and R_updatedDate in (select max(R_updatedDate) from dbo.UpdatedPaperCosts12 group by R_updatedDate )
group by pt_id,papername,Papercost,p_Updateddate,Realcost,R_updatedDate
) t where rownum=1
narasappa
Participant
960 Points
458 Posts
problem with Query
Nov 09, 2012 04:53 AM|LINK
Hi all i hav one query
which returns data like
i want only First Top Row of each group ie.. Top Row of A and Top row of B Group
How can i do this?
ravi.jadiyannavar@gmail.com
Pls Mark This Post As Answer If it Helps U..
Thanku
Prince Sriva...
Contributor
2290 Points
419 Posts
Re: problem with Query
Nov 09, 2012 05:03 AM|LINK
Hi
declare @mytable table (ID int, GroupID int, Qty int)
insert into @mytable values
(1, 1, 100),
(2, 1, 200),
(3, 1, 300),
(4, 2, 98),
(5, 2, 198),
(6, 3, 175),
(7, 3, 275),
(8, 3, 375),
(9, 4, 215)
;with cte as
(
select
ID,
GroupID,
Qty,
rank() over(partition by GroupID order by ID) as myrank
from @mytable
)
select ID, GroupID, Qty--,myrank
from cte
where myrank = 1
==
use rank for this my above query will help you.,..
My Blog
ROHIT KUMAR SRIVASTAVA
Sirama
Member
285 Points
68 Posts
Re: problem with Query
Nov 09, 2012 05:04 AM|LINK
Try the below one:
Datta H Shed...
Member
228 Points
104 Posts
Re: problem with Query
Nov 09, 2012 05:19 AM|LINK
Try something like below:
wmec
Contributor
6219 Points
3217 Posts
Re: problem with Query
Nov 09, 2012 05:39 AM|LINK
Try
with set1 as
(select pt_id,Papername,Papercost,p_Updateddate,Realcost, R_updatedDate,row_number() over (partition by pt_id order by R_updatedDate) rn
from dbo.UpdatedPaperCosts12
where R_updatedDate<=getdate() and R_updatedDate in (select max(R_updatedDate) from dbo.UpdatedPaperCosts12 group by R_updatedDate )
group by pt_id,papername,Papercost,p_Updateddate,Realcost,R_updatedDate)
select pt_id,Papername,Papercost,p_Updateddate,Realcost, R_updatedDate
from set1
where rn=1;
HuaMin Chen
sandeepmitta...
Contributor
6779 Points
1058 Posts
Re: problem with Query
Nov 09, 2012 05:51 AM|LINK
Sandeep Mittal | My Blog - IT Developer Zone