category table
id name date duration
1 aaa 12/12/07 1
2 bbb 10/10/08 1
.........

user_table

id name date
1 aaa 12/12/07
2 bbb 10/10/08
.......

rating table
id user_id name rating date

1 7 aaa 4 12/12/07
2 8 bbb 3 10/10/08

write now i am displaying these user in order of rating ...i.e high rating first display...
if rating match then with date i.e minimum date....


so it display like...

aaa 4 12/12/07
bbb 3 10/10/08
ccc 3 11/10/08
ddd 2 11/09/08
.....


i want show after 1 week


ddd 2 11/09/08
aaa 4 12/12/07
bbb 3 10/10/08
ccc 3 11/10/08 .....

again after 1 week

ccc 3 11/10/08
ddd 2 11/09/08
aaa 4 12/12/07
bbb 3 10/10/08


How to do this???

you've posted the exact same question in both the mysql and oracle forums

i'm going to assume that your first post was a mistake, so i've removed the oracle post

by the way, your question is not clear at all

please try to explain in words what the query should do

I am creating 3 tables category, user_table, rating_table

I am creating this for my collage project... On my website first page
write now i am displaying data .... using these 3 tables..
who has maximum rating , if user rating match check which user has older date..

And on display 2nd page I want to show

Last data take the first position after 1 week ....

e.g.
name rating date
aaa 4 12/12/07
bbb 3 10/10/08
ccc 3 11/10/08
ddd 2 11/09/08
.....


i want show after 1 week


ddd 2 11/09/08
aaa 4 12/12/07
bbb 3 10/10/08
ccc 3 11/10/08 ..... here ddd take place of aaa

again after 1 week

ccc 3 11/10/08
ddd 2 11/09/08
aaa 4 12/12/07
bbb 3 10/10/08 here ccc takes place of ddd

how to do that??

sorry, i have no idea what you are doing

Table-> cat_users

id cat_id user_id
81 7 12
160 7 22
193 7 26
213 7 29
223 7 30

Table-> categories

id parent_id cat
7 NULL Stive
11 0 Akshay
15 NULL Shooting
24 NULL Fighting
52 NULL Puzzle

Table-> Users
id valid name date_added
1 1 Holly NULL
3 1 Tom 2007-11-01
7 1 Stive 2007-11-02
10 1 Elite 2007-11-02
11 1 Akshay 2007-11-02


Table-> ratings

id user_id rating date_added
1 7 4 2008-07-18
2 7 3 2008-07-18
3 3 4 2008-07-18
4 5 3 2008-07-18
5 10 2 2008-07-18
6 6 4 2008-07-18
7 1922 5 2008-07-18
15 1322 3 2008-08-02
16 2055 3 2008-08-07
81 74 4 2008-08-25
160 169 5 2008-08-25


$sql1 = "select users.*, round(avg(rating)) as rating
, date_format(date_added,'%Y-%m-%d') as `date`
from users
left join ratings on (ratings.user_id = users.id)
inner join cat_users on cat_users.user_id = users.id
where
valid = 1
and users.id in (
select user_id
from cat_users
where cat_id = $cat_id
)
and users.date_added <= now()
group by users.id
order by date_added desc, rating desc, name
";
$max = GetSQL("select count(*) from ($sql1) counter");


I am not giving all database it's very large
so got output like ....

id user_id rating date_created
123 1 4 2008-12-09
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25
723 1 5 2008-08-25
15 1 3 2008-08-25
16 1 3 2008-08-25
81 1 4 2008-08-25
160 1 5 2008-08-25

after that i want to display...after 1 week
like...

id user_id rating date_created
160 1 5 2008-08-25 .....change
123 1 4 2008-12-09 last data take 1st position
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25
723 1 5 2008-08-25
15 1 3 2008-08-25
16 1 3 2008-08-25
81 1 4 2008-08-25

again after 1 week ...
id user_id rating date_created
81 1 4 2008-08-25 .....change
160 1 5 2008-08-25 last data take 1st position
123 1 4 2008-12-09
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25
723 1 5 2008-08-25
15 1 3 2008-08-25
16 1 3 2008-08-25


How to do that??
can i add column like postion_id so that change position of data??
Reason behind change position is old user viewed there profile by another users.

I have table

id user_id rating date_created change_id
123 1 4 2008-12-09
211 1 3 2008-12-08
33 1 4 2008-11-30
45 1 3 2008-10-31
57 1 2 2008-10-31
666 1 4 2008-08-25

how to modify like this...

id user_id rating date_created change_id
123 1 4 2008-12-09 1
211 1 3 2008-12-08 2
33 1 4 2008-11-30 3
45 1 3 2008-10-31 4
57 1 2 2008-10-31 5
666 1 4 2008-08-25 6

how to put data for change_id with increment value in column??

"last data take 1st position"

i am sorry, i have no idea what you're doing

Hi...
i am looking for How store data with increment value in column??
so i got answer here http://forums.oracle.com/forums/mess...167957#3167957

i am trying a lot ....
But not able to covert in mysql syntax...

Plz help me??

i want to update the change_id of selected data

here is query

select games.*,change_id, round(avg(rating)) as rating
, date_format(date_added,'%Y-%m-%d') as `date`
from games
left join ratings on (ratings.game_id = games.id)
inner join cat_games on cat_games.game_id = games.id
where
valid = 1
and games.id in (
select game_id
from cat_games
where cat_id = 7
)
and games.date_added <= now()
group by games.id
order by date_added desc, rating desc, name

it gives me data ...
so i want to update change_id like


update games set change_id=change_id+1 where (select games.*,change_id, round(avg(rating)) as rating
, date_format(date_added,'%Y-%m-%d') as `date`
from games
left join ratings on (ratings.game_id = games.id)
inner join cat_games on cat_games.game_id = games.id
where
valid = 1
and games.id in (
select game_id
from cat_games
where cat_id = 7
)
and games.date_added <= now()
group by games.id
order by date_added desc, rating desc, name )

how to change_id of this particular selected data??

you will have to specify correct SQL

right now, you have this, which is invalid --the WHERE clause requires a condition, e.g. WHERE foo = 'something'

you've got WHERE table