Query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query

Author

Topic

RoniR
Starting Member

16 Posts

Posted - 2009-09-15 : 17:09:02

i have a table guys that i use to store stories
basically what i want is to retrieve the story_id,story_title.story_date from stories

and to load them in this form

story_Date
(example September 16,2009)
story_title
( example titleeeeeeeeeeeeeeeeeeeee 10:59 <- time the story was published (story_date is of type datetime)
.
.
.
.
then Story_date (example September 15,2009)
and all stories related to this date
.
.
then Story_date (might be September 10,2009)
and all stories related to this date
.
.
.
then Story_date (example September 8,2009)
and all stories related to this date

can i get my data in this format from sql so i can use it directly to load it in my pages

basically i could have got the top 4 dates from my table but i had a problem since i am saving the time with my date
select top 4 story_date from stories
returns
2009-15-09 10:45:34
2009-15-09 10:31:34
2009-15-09 10:20:34
2009-15-09 09:45:34

which is wrong coz i am looking the latest 4 days not the latest 4 entries

i hope some1 can help me out down here
i guess i need to use a stored procedure since i am gona do paging to the results
load a new date,keep 3 dates from previous query and remove the previous top date etc etc

please help me out guys

thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-15 : 21:37:26

[code]
declare @thedate datetime

select @thedate = min(story_date)
from (
select top 4
story_date = dateadd(day, datediff(day, 0, story_date), 0)
from stories
group by dateadd(day, datediff(day, 0, story_date), 0)
order by story_date desc
) d

select story_date
from stories
where story_date >= @thedate
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-15 : 21:38:58


select story_date
from   (
            select story_date, day = row_number() over (partition by dateadd(day, datediff(day, 0, story_date), 0)
                                                        order by story_date desc)
            from   stories
       ) s
where  s.day <= 4

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources