Max date


,


How do you find the maximum date of a column Say;


d_id | d_date
0 | 11/26/2006
1 | 01/02/2007
2 | 02/02/2007
Posted On: Monday 26th of November 2012 01:25:42 AM Total Views:  201
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Selecting a value with the max occurences

I am having some problems forming a query. Basically I have a database table which stores the weather conditions and one column holds the wind direction taken every 10 mins. I place a number in the column to signify the direction, e.g. North wind = 0, North west = 2 etc..... Anyway what I want to do is run a query for an entire day or month and pick out the most prevalent wind direction. I have tried using count(*) and Top 1 queries but I havent managed to get it working as I want. Basically I want the query to return the number which corresponds to the direction which has the most occurences in the table. If I have the column as follows for example: Wind Direction 1 2 4 2 2 2 2 5 4 then I want it to return 2 as it it occured the most times over the time period specified. Id be very grateful for any help. Many
VIEWS ON THIS POST

56

Posted on:

Monday 29th October 2012
View Replies!

How to increase Group_concat_max_len in linux..?

Dear Members, I tried to increase my MYSQL group_concat_max_len in linux machine, but for the same i customized the 'my.cnf' file in windows and increased the max_allowed_packet size and group_concat_max_len size it works fine. But for the same i tried to do in linux machine, but the changes i made is not working, even after increasing the size it shows the default size of 1024. Could you please locate me which file will mysql look for in linux machine for the server settings. for group_concat_max_len and max_allowed_packet size...
VIEWS ON THIS POST

112

Posted on:

Monday 29th October 2012
View Replies!

Display the nth max entry

How to display nth highest record in a table for example Say how to display 4th highest (salary) record from customer table
VIEWS ON THIS POST

50

Posted on:

Monday 29th October 2012
View Replies!

Mysql with xampp - max_allowed_packet problems

Trying to do a db restore from a .sql dump... via cmd console in Vista. Keep getting Code: ERROR 1153 (08S01) at line 307: Got a packet bigger than 'max_allowed_packet' bytes Tried upping the max_allowed_packet values in my.cnf ... as high as 1024M. No change. Read in the my.cnf file itself that server config can be managed using same file in the /data director... copied it there w/large values for max_allowed_packet. Still no change. Read at mysql site about a my.ini file, so tried upping the max_allowed_packet value there also. No change. So questions are these...Could it be that I've actually just got a really bad line in my db dump file If so, any ideas how I can remove it The file is so large (400+mb) it crashes my text editor.Is there something else that could be causing these max packet size errors Also tried doing an import in phpmyadmin in firefox via localhost... it seemed to go for about an hour then FF finally crashed. Not my day.
VIEWS ON THIS POST

113

Posted on:

Monday 29th October 2012
View Replies!

Obtaining records from temporary table with max()

ill be copying and pasting this from another forum Quote: ok im real stumped here. im going to be stripping the tables down to its basic idea (naming convention and etc) so that i can try to better explain my dilemma. here are the tables.. article ---------------------- -aid -parent_user_id article_review ---------------------- -arid -parent_aid -parent_user_id -rating users ---------------------- -user_id -username -firstname the first approach: i've got 2 tables that ill be querying for. What i need is to grab all existing records from the article table + username and firstname from the users table by joining "article.parent_user_id = users.user_id" original sql: " SELECT a.aid, a.parent_user_id, u.username, u.firstname FROM article AS a, users AS u WHERE a.parent_user_id = u.user_id " Works out ok and everything.. but now i want to also obtain any existing data within article_review that can be found with joining "article_review.parent_aid = article.aid". Even if none is found, returning a NULL is ok. So my second approach is to use left join. please pay particular attention to the aggregate function; max(). second approach sql: " SELECT a.aid, a.parent_user_id, u.username, u.firstname, r.parent_user_id r.rating FROM users AS u, article AS a LEFT JOIN ( SELECT sub_r.arid, sub_r.parent_aid, sub_r.parent_user_id max(sub_r.rating) AS rating FROM article_rating as sub_r GROUP BY sub_r.parent_aid ) AS r ON a.aid = r.parent_aid " since article_review.parent_aid is not unique (because there can be more than one reviews for each article), i need to only return the highest rating article_review along with the parent_user_id. The second approach returns the highest rating but does not return the correct corresponding parent_user_id. Anyone have any idea why it just wont return the corresponding parent_user_id from max(rating) Any help would be greatly appreciated.
VIEWS ON THIS POST

89

Posted on:

Monday 29th October 2012
View Replies!

What does 'maximum key length' mean

ALL, In the MySQL manual I read these words "...The maximum key length is 1000 bytes...". Could anyone explain this concept by an example
VIEWS ON THIS POST

66

Posted on:

Monday 29th October 2012
View Replies!

SELECT one record per GROUP with max timestamp

MySQL 5.0.x I have a table like this: table1 ------ id fid timestamp title body I want a query that gets one record for each FID in the table, and I want that record to be the one with the highest timestamp. Trying this: Code: SELECT MAX(timestamp), fid, title, body FROM table1 GROUP BY fid This is returning the correct MAX(timestamp) - the highest timestamp per group, however the other fields are not from the record with the MAX timestamp, they are from (I think) the record with the lowest ID. How can I return the entire record of with the highest timestamp per FID
VIEWS ON THIS POST

170

Posted on:

Monday 29th October 2012
View Replies!

Latest from forum (Groupwise max?)

I have been reading both http://jan.kneschke.de/projects/mysql/groupwise-max/ and http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html but I am unable to adapt it to the query I have. I want to display the most recent threads from a forum. If the thread has no posts/replies, I just want the thread authorid, but if the thread has replies, I want the authorid of the _latest_ post in the thread. Currently I get the authorid of the first reply in a thread no matter how many subsequent replies there are. Current code: Code: SELECT arctic_forums_threads.forum_thread_id, arctic_forums_threads.forum_id, arctic_forums.forum_name, arctic_forums_threads.forum_thread_title, IF(arctic_forums_posts.forum_post_author IS NOT NULL, arctic_forums_posts.forum_post_author, arctic_forums_threads.forum_thread_author) AS post_author FROM arctic_forums_threads LEFT JOIN arctic_forums_posts ON arctic_forums_threads.forum_thread_id = arctic_forums_posts.forum_thread_id JOIN arctic_forums ON arctic_forums_threads.forum_id = arctic_forums.forum_id WHERE arctic_forums.forum_access = 0 GROUP BY arctic_forums_threads.forum_thread_id ORDER BY arctic_forums_threads.forum_thread_action_last DESC LIMIT 8 So I need some way of saying "IF NOT NULL post_author, SELECT post_author WHERE MAX(post_id) AND threadid = threads.thread_id"
VIEWS ON THIS POST

43

Posted on:

Monday 29th October 2012
View Replies!

Mysql table max size

what is mysql max table size in win/unix (i can not find out its link in mysql.com if someone please send me it link)
VIEWS ON THIS POST

133

Posted on:

Monday 29th October 2012
View Replies!

Aggregate aggregate? max(count(field))

Hi , I'm working on a poll script and I'm having difficulty getting the syntax right on this query. What I want to do is determine which poll_item was voted for the most. I start out with: Code: SELECT i.item_name, count(v.vote_value) FROM poll_items i LEFT JOIN poll_votes v USING (item_id, poll_id) WHERE i.poll_id=2 GROUP BY i.item_name ORDER BY i.item_id; This gives me a 2 column table with all the items for a particular poll and their scores. I want to select the row from this table that contains the max score. I keep getting 'Invalid use of group function' when I try to return a row. I am successful returning the max(count(v.vote_value)) without its associated item_name, but that's not useful . TIA, Jen
VIEWS ON THIS POST

60

Posted on:

Monday 29th October 2012
View Replies!

Mysqld-max uses 99.9% cpu and 1 gig of memory...

I have a mysql server with a couple hundred-thousand entires that constantly maxes out our server at peak times to the point where the database locks up. It uses up 99.9% cpu and over a gig of memory on our Dual Core 64 Athlon 2 Gig DDR machine. Any suggestions
VIEWS ON THIS POST

59

Posted on:

Monday 29th October 2012
View Replies!

maximum open cursors exceeded

hi, i got this exception when insert row: maximum open cursors exceeded throws at Trigger Anyone know how to solve!!! Please advise me.
VIEWS ON THIS POST

55

Posted on:

Saturday 10th November 2012
View Replies!

Min/max query that will return values of multiple columns across a row

I've tried looking through the archives for a solution to this problem, but not getting exactly what I need. I'm sure someone else must have also come across this problem before. Suppose I have table GROCERIES like this: DAT NAME PURCHASE QUANTITY Jan. 3, 2007 Ann apple 2 Jan. 5, 2007 Ann orange 3 Feb. 4, 2007 Ann bread 1 Feb. 9, 2007 Ann juice 1 Jan. 8, 2010 Ann milk 1 Jan. 10, 2010 Ann orange 3 Jan. 1, 2007 Bob apple 5 Jan. 2, 2007 Bob banana 2 Mar. 2, 2007 Bob bread 1 How do I get it to return the values of PURCHASE and QUANTITY for the earliest date of each month of each year for each person i.e. I want this: DAT NAME PURCHASE QUANTITY Jan. 3, 2007 Ann apple 2 Feb. 4, 2007 Ann bread 1 Jan. 8, 2010 Ann milk 1 Jan. 1, 2007 Bob apple 5 Mar. 2, 2007 Bob bread 1 I was wondering if there's a more efficient query than this one (one that doesn't involve a join): select DAT, NAME, PURCHASE, QUANTITY from GROCERIES x JOIN (select min(DAT) as DAT, NAME from GROCERIES group by to_char(DAT, 'RR'), to_char(DAT, 'MM'), name) y ON x.DAT=y.DAT and x.NAME=y.NAME
VIEWS ON THIS POST

95

Posted on:

Sunday 11th November 2012
View Replies!

Table - create columns based on max value

Hi. I have a table with a structure like below P|X|Y ----- a|1|2 b|4|1 c|2|3 What I want to do is to create a table like this: P|X1|X2|X3|X4|Y1|Y2|Y3 ---------------------- a|1|0|0|0|1|1|0 b|1|1|1|1|1|0|0 c|1|1|0|0|1|1|1 For example, if the max number of column X at the first table is 4, I want to create 4 columns X1, X2, X3, X4 and fill them with 0/1 Thank you.
VIEWS ON THIS POST

59

Posted on:

Monday 12th November 2012
View Replies!

how to select max score from different tables

i have few tables , tableA,tableB, tableC have a column named totalScore. how can i select the max totalscore from those 3 tables together
VIEWS ON THIS POST

77

Posted on:

Monday 12th November 2012
View Replies!

Calaculation with maximum value

I have a View on my SQL 2005 DB. I want to create a column that calculates something and returns a value, but has a maximum value of 4. The calculation is this: Calculate how long a member of staff has been workiing for the company (based on start date), and then for each year they've worked, add a point to their record, up to a maximum of 4 points. I can do the first part (i.e. how long they've been working for the company), and could add a point for each year, but don't know how to specify a a maximum of 4. The SQL I have to calculate years served is this: Code: (SELECT SUM(DATEDIFF(yy, EmpStart, (CASE WHEN DATEDIFF(day, getDate(), EmpEnd) > 0 THEN getDate() WHEN EmpEnd IS NULL THEN getDate() ELSE EmpEnd END))) AS Expr1 FROM dbo.Employment AS Employment_3 WHERE (dbo.STAFF.AppNo = AppNo) GROUP BY AppNo) AS YearsServed I'm guessing it's using CASE, WHEN & THEN but am not sure how to express it. Can anyone help
VIEWS ON THIS POST

76

Posted on:

Tuesday 20th November 2012
View Replies!

Date + sql max???

Ive tried the following sqlstatement to get the greatest date from a column in my table- but doesnt seem to be working- what am i doing wrong Does the sqlmax function support dates\t Code: sub sqlCmd2( ) '==SQLSTATMENT================================ SET objRs2 = SERVER.createObject( "ADODB.recordSet" ) dim sqlStatment sqlStatment = sqlStatment + "SELECT MAX(Gross-val) FROM sldetail objRs2.OPEN sqlStatment,objConn,1,3 '=============================================' end sub Im calling the sql statement as Code: Call sqlCmd2( ) Response.write(objrs2("Gross-val")) any suggestions\t
VIEWS ON THIS POST

94

Posted on:

Tuesday 20th November 2012
View Replies!

Update using max

Hi Im trying to write an update query but i cant get the unique id out so i need to update using the max service date. Below currently gets all the properties, but i only want to update the one with the max service date as this will be the most recent one, how do i write an update with a max date Code: UPDATE dbo_tbl_service SET dbo_tbl_service.SERVICEDATE = "123" WHERE (((dbo_tbl_service.PROPREF)="1") AND ((dbo_tbl_service.CONTRACT)="dum"))
VIEWS ON THIS POST

68

Posted on:

Monday 26th November 2012
View Replies!

Is there a smarter way to get maximum

get a+b on the maximum record: mySQL="SELECT a,b FROM tbl WHERE b=(SELECT Max(b) FROM tbl)"
VIEWS ON THIS POST

28

Posted on:

Monday 26th November 2012
View Replies!

From max id show filename

Im using a Access Database. I get the max ID number, next from teh ID number on the same page i need the cell of data from the collumn FileName in the same table called Videos. Is it possible to do this without going through to another page and doing a response.request of that ID number and doin a select stament of Select Filename From Videos Where Id = (id number from Last page) this is wot i got so far Code: sqlStatment = sqlStatment + "SELECT MAX(id) as test1 " sqlStatment = sqlStatment + "FROM Videos " and a simple response.write to display the ID number which works and show the latest ID number
VIEWS ON THIS POST

31

Posted on:

Monday 26th November 2012
View Replies!