query

RoniR · September 15th, 2009, 05:12 PM

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

Old Pedant · September 15th, 2009, 06:18 PM

What database are you using? The answer depends *HEAVILY* on the type of database.

Getting the date-only from a datetime value is trivial in Access and MySQL but a pain in SQL Server.

It *looks* like you are using MySQL, given the format you listed your datetime values in, but...