A complicated select (for me at least)

[McNugget]

This *may not* be the same type of question.. but I want to select all the news from the last *5 days posted in* (so.. maybe one's 3 days ago and the next is 5 days ago.. and so on) and INNER JOIN THEM so that news items in similar days are listed under one heading..

the only idea that came to mind was:
SELECT * from news as newsdate INNER JOIN news as newsdata on dayofyear(newsdate.date) = dayofyear(newsdata.date)

however.. that obviously doesn't work.. and I have no idea what the real commands would be..

 

[LittleSmudge]

Okay, back-off a bit

What are your table names and the important fields in them?
I can then build it up from scratch.


G LS

 

[McNugget]

okay.. here's the *HUGE PLAN*..

table news.. is "ta2knews" the important field for finding the last 5 used dates is "date" and that's what i want it to be JOINED ON.. and for the outputted news data.. "body" basically..

so like.. i want this:

- Day 1
-- News Item 1
-- News Item 2
- Day 2
-- News Item 3
-- News Item 4
- Day 3.. and so on for 5 days.. but like.. the spacing between days might differ..

 

[McNugget]

The only real problem *I* have is that the DATE field is both date/time.. and I don't know how to make it only count the date part, otherwise I suppose I'd be perfectly fine.

 

[gol4]

This will give you this
date1 newsitem1
date1 newsitem2
date2 newsitem3
date2 newsitem4
date3 newsitem5

etc

select ta2knews.newsbody, date(ta2knews.[date]) from ta2knews
where [date] >= date() - 5
group by date(ta2knews.[date])

my need to clean up a bit but should give you an idea