how to Solve 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

General SQL Server Forums

New to SQL Server Programming

how to Solve query??

Author

Topic

Aamit1
Starting Member

1 Post

Posted - 2008-12-11 : 05:09:37

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 position_id so that change position of data??
Reason behind change position is old user viewed there profile by another users.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-11 : 09:50:27

Are you using MS sql server? if not, try posting this in some other forums specific to your db type like www.dbforums.com. This is MS Sql Server forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources