开发者

closest value and last value problem

I have a data table having fields(date,company,data_id,rank etc)..as problem is related to these fields that's why showing these fields only. suppose table is:

data_id   |   company  |    date      |   rank

1         |   google   |    23/10/2010|   1

2         |   yahoo    |    23/10/2010|   4

3         |   msn      |    23/10/2010|   8

4         |   google   |    27/10/2010|   3

5         |   yahoo    |    27/10/2010|   1

6         |   msn      |    27/10/2010|   6

7         |   google   |    29/10/2010|   1

8         |   yahoo    |    29/10/2010|   4

9         |   msn  开发者_如何学JAVA    |    29/10/2010|   3

...and so on

PROBLEM 1:

there are many users-suppose there are user1,user2,user3. All have their [my_company] in session.

Now, I have to display only those entries which are made last(can be done by any user on any date) as per company.

Example: my_company[user1-yahoo,user2-google,user3-msn]

user's [my_company] only display his company's value,nothing else..but only value entered last(on date-here 29/10/2010).

Data is added for any company by any user on any date.now as this process will continue, entries will grow.HOW CAN I FIND WHICH DATE IS LAST(specific to a company)?

PROBLEM 2:

how to find closest date to a specific date?


  ... where `company_name` = 'companyName' order by `date` desc limit 1

and

 ... between mydate - INTERVAL and mydate + INTERVAL

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html


when you create session variable sort data by date so you will have order in my_company

once you have ordered list you can figure out which date belongs to which date

or

while adding data to my_company add id

you can find closest date by

SELECT date FROM table ORDER BY abs(now() - date) LIMIT 1

http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_abs

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜