Retrieve records - SQL Server Forums

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

Retrieve records

Author

Topic

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2010-12-12 : 10:26:17

Dear All,
Can u pls help on this issue
Issue is : I have to take Hr based updated records
For Example:

Id Re_Date Total_Energy

1 09-12-2010 9:20:23 1454
2 09-12-2010 9:30:26 9454
3 09-12-2010 9:35:10 4454
4 09-12-2010 9:45:55 5454

5 09-12-2010 10:20:00 6454
6 09-12-2010 10:30:23 7454
7 09-12-2010 10:45:10 9454

8 09-12-2010 11:10:23 7454
9 09-12-2010 11:35:10 9454

Hence i need to retrive the output as Hr based updated recotds as follows

Id Re_Date Total_Energy
4 09-12-2010 9:45:55 5454
7 09-12-2010 10:45:10 9454
9 09-12-2010 11:35:10 9454

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-12 : 12:34:00

[code]

Create Table #foo (ID int not null, Re_Date datetime not null, Total_Energy int not null)
GO
Insert Into #foo

SELECT 1, '09-12-2010 9:20:23' ,1454 UNION
SELECT 2, '09-12-2010 9:30:26' ,9454 UNION
SELECT 3, '09-12-2010 9:35:10' ,4454 UNION
SELECT 4, '09-12-2010 9:45:55' ,5454 UNION

SELECT 5, '09-12-2010 10:20:00', 6454 UNION
SELECT 6, '09-12-2010 10:30:23', 7454 UNION
SELECT 7, '09-12-2010 10:45:10', 9454 UNION

SELECT 8, '09-12-2010 11:10:23', 7454 UNION
SELECT 9, '09-12-2010 11:35:10', 9454

GO

Select a.*
FROM #foo a inner join
(Select MAX(RE_DATE) as LastRE_Date
FROM #foo
GROUP BY DATEPART(HOUR,RE_DATE)) b
on a.RE_DATE = b.LastRE_Date
GO

Drop Table #foo

[/code]

One option, but you could use a CTE also
[code]
With b as
(Select MAX(RE_DATE) as LastRE_Date
FROM #foo
GROUP BY DATEPART(HOUR,RE_DATE))

Select a.*
FROM #foo a inner join b
on a.RE_DATE = b.LastRE_Date
[/code]

Poor planning on your part does not constitute an emergency on my part.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-12 : 14:22:43

Also, if your data would span more than one day, instead of grouping by datepart(hour,re_date) you should group by something that takes into account the date also - for example, the hour converted to a number, like this:

datediff(hour,0,re_date)

Another way of doing this would be to use the row_number function

with a as
( select
   row_number() over (partition by datediff(hh,0,re_date) order by re_date desc) as N,
   *
from #foo
)
select * from a where n=1

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2010-12-23 : 16:16:06

Hi sunitabeck,

Thanks a lot for yr help..it exactly solved my issue..

Thanks & regards
Ravi

quote:
Originally posted by sunitabeck

Also, if your data would span more than one day, instead of grouping by datepart(hour,re_date) you should group by something that takes into account the date also - for example, the hour converted to a number, like this:
datediff(hour,0,re_date)
Another way of doing this would be to use the row_number function
with a as
( select
row_number() over (partition by datediff(hh,0,re_date) order by re_date desc) as N,
*
from #foo
)
select * from a where n=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources