MYSQL :: First And Last Time In Each Date By Empl (was "Help Me With This Select Statement")

Best 4 Students From Each State (was "help With Select Statement In SQL")
I've got a database that contains names of registered students with the states they come from. I intend to select 4 each from every state that has the highest total scores. I use PHP and MYSQL database.

NAME STATE TOTAL Course
Simon Alabama 56 Computer Science
Mark Alabama 85 Electrical Engineering
John Washington 45 Computer Science
Linda Florida 99 Statistics
Fred Florida 23 Mehcanical Engineering

This is just an illustration of how the database looks like.

the sql statement should select the best 4 students from each state based on thier total scores.

REQ: MySQL 4.0 Equivilent Of "DateDiff("m", Date, Now())=1"
I have a query that works in the rest of the SQL world

"SELECT invoice.*, invoice.Date
FROM invoice
WHERE (DateDiff("m", Date, Now())=1);"

which will give me all of last month's invoices.

However it doesn't work with MySQL 4.0.

While

"SELECT
Invoice.*
FROM
invoice
WHERE
(Month(Invoice.Date) = ((Month(Now()))-1))"

Is a close substituent, it will blowup in January.

Anyone have an equivalent expression for MySQL 4.0?

SELECT Statement Using WHERE That Involves Value From "next" Row?
Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

5
6
1
2
1
3

Algorithm Of "select" Statement
As I have to access MySQL database in Pocket PC, I'm developing a
evc++ program to read and retrieve data from MySQL table.

Where can I get some detail information about how "select" and "where"
statements work in the format, index and data files of MySQL database?

Impossible "select Statement"
I have a table with 3 simple fields all 2 varchar 1 double.

distributor,manufactutrerssku,cost

I have several diffent distributors listed in my table and some of them carry the same manufacturers products so I am trying to find out which skus are available at more than one distributor and where the cost is best. This is simple enought to do in php but on a select line I am lost. Can this be done or is it impossible?

Single Query For No Of Hits "Today", "This Month", "This Year"
Is it possible to construct a single query that fetches the no of pageviews "today", "this month", "this year" and "total since start"?

The table is very simple, one field for Date (YYYY-MM-DD) and one for the no of pageviews this day.

If not, would it be much slower/faster to divide the query into 4 different queries?

"Select * FROM" Vs "Select Field1,field2 FROM"
No matter how many fields I want to retrieve from the table I always use "Select * FROM Table" instead of "Select field1,field2 FROM Table".

Are there any disadvantages of always using "Select * FROM Table"?

Maximum Execution Time Of 300 Seconds Exceeded (was "MYSQL Error")
I have a huge database 400mb+ in size which i have exported into a .sql file. I tried to run this export script into a new database and it runs fine until three quarters into the scripts execution and the following error appears:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<br / >
<b>Fatal error</b>: Maximum execution time of 300 seconds exceeded in <b' at li ne 1
I believe this error may relate to "delayed_insert_timeout = 300". How do I increase this limit in mysql - maybe it may solve the problem but then again it may not .

Any advice is most gratefully appreciated - As you can imagine if the script runs fine for 3/4 of it and then an error appears, stating the error is on line 1, it will take me days to go through the whole script (hundreds of thousands of rows) to find the error as I assume the error does not reside on line 1 as this line would have been executed.

I am running the script on mysql 5, and via source /path/****.sql on linux.

Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this:

WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00"))

Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like:
Day Min Max
2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59
2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68
where at this point i`m getting min=40 and max=68

What Is Execution Time Of A Query Based On? (was "a Mysql Question")
when selecting data from the database, does the time taken to retrieve it vary from 56k connections to T3 connections? or does it all depends ont he general server speed/amount of connections on the db?

im not sure if ive explained that in the best way for people to understand
but im sure someone will get what i mean

Adding A "date Added/date Modified" Column?
I want to have a column in my table that is automatically populated with the current date/time when record is inserted? Hopefully there is an easy way to do this that I don't know about.

Help With "load Data Infile" Statement
I'm having trouble with the load data statement I've written. It's not returning an error, but no data is being imported.

This is what I'm running:

load data infile 'filename.csv' into table table.table1 fields terminated by ',' lines terminated by '/r/n' IGNORE 1 LINES;

My csv file looks like this:

numberhitstypecodesitecountrydate
958375482033ABCDFGBR 20060410
384574905042ABCDFGBR 20060409
029485736353ABCDFGBR 20060409
049585785744ABCDFGBR 20060409
345039458392ABCDFGBR 20060409
049586489542ABCDFGBR 20060409

Syntax For "One Or Zero" Characters In LIKE Statement?
For the longest time I've been using the underscore in LIKE statements to denote one or zero of any character, but it seems I was mistaken as it means "one of any character", but cannot be 0 characters.

So basically I want something like this
SELECT * FROM table WHERE name LIKE '%foo_bar%'

Only I would like to find either 0 or 1 characters between foo and bar. Can anybody give me the syntax for this please?

How Do I Change The Tables Date Format Into "M/D/Y" ?
i have created a table with "date" as one of the field. i have noticed that "Y/M/D" is the default format for date fields.

how do i change the tables date format into "M/D/Y"?

Warning 1292 "Incorrect Date Value:"
I have been trying to find information about this issue in the
documentation and have not been able to.

I built a web-based app (PHP) that allows users to upload the contents
of a CSV file into database tables.

My users have reported that most of the time, everything is fine, but
some records do not show up. Although I can see the records by doing
a SELECT with the mysql interface, I cannot delete these records in
question. When I try to delete them 0 rows are affected and I get
this warning:

+---------+------
+----------------------------------------------------------------+
| Level | Code |
Message |
+---------+------
+----------------------------------------------------------------+
| Warning | 1292 | Incorrect date value: '1987' for column
'Sample_Date' at row 1 |
+---------+------
+----------------------------------------------------------------+

I haven't been able to find out what this warning means. I don't know
how to proceed here.

Searching For Numbers - "close To" Or "approximate"
I would like to know how to search a table for records where a particular field is "close to" a particular number.

For instance, let's say I have a table containing students and their most recent test scores. I'd like to see students who scored 7/10. That's easy enough (select * from table where score = 7). I'd also like to run another query to identify other students whose scores are not 7 but "close to" 7. Ie students who scored 6 or 8.

As an added bonus, I'd like to be able to do a related search that shows students who scored 7, then shows the other students ordered by how "close" they are to 7. Ie students who scored 6 or 8 are "closest" matches, and students who scored 1 are "furthest". I hope this makes sense!

Is there any pre-existing MySQL command that will do this? Or will I have to run separate queries for each value?

Server "date Created"
When I open MySQL Control Center, it shows "server properties" window
when the server is selected on the left pane.

One of the property is "date created". I have not found this variable in server
and status variables .... Does anyone know how to read this variable using
MySQL SQL query or MySQL API (not file system, etc. ) ?

NOW() Or Date("Ymd") To A Table Column
I recently started using MySql for my clients Web Pages. (Newbie) Iâ€™ve tried every trick possible to insert the DATE automatically without the user having to enter the date they fill the form. I have used Java, PHP and SQL instructions to fill the table column and nothing happens. Maybe is something with the DATE format column? All other table columns get the information is entered by the person filing the form. Is there something Iâ€™m doing wrong?

I have verifies that the DATE variable info is collected through PHP and Java, but the instruction to INSERT INTO does not takes the DATE info into the column.The field stills empty.

Server "date Created"
When I open MySQL Control Center, it shows "server properties" window
when the server is selected on the left pane.
One of the property is "date created". I have not found this variable in server
and status variables .... Does anyone know how to read this variable using
MySQL SQL query or MySQL API (not file system, etc. ) ?

Need A "Register,select Date,pay
Does anyone know of an already complete member registration; calendar selection and payment that is completely free .. Open source or what ever.. Php or mysql ????

How To Modify The "select" Function In The Source-code-version?
I have a source-code-version of MySQL at hand,and I want to modify the "select" function to optimize it.

The question is:how can I start? I wonder whether "select.cc" is the right file I can edit.

Select "german, French" Only With "german"
in a table I have this entry in the column "language":

"GERMAN, FRENCH"

how do i have to make/modify my mysql-syntax so that i can select the entry abowe only with the word: "GERMAN"? Is this possible or do i have to make a data manipulation first?

Can You Select And Concat Multiple Items Separated By "," ?
The select I have finds multiple values for a field. I want to take that field and create ONE field separated by commas.

Is that possible? I could do it in PHP but it will be easier programmatically if I can do it here.

For example:

HTML
Select field1 where x=2;
val1
val2
val3
val4
I want it to be like:

HTML
Select field1 where x=2;
val1, val2, val3, val4
where "val1, val2, val3, val4" is ONE field I can then use.

I am actually using this as a subquery so it's not as easy as php's explode/implode.

Dates In The Past 3 Weeks (was "SELECT Query Help")
I'm trying to select data from a table that has been submitted in the past 3 weeks. I am not having any success.

"Unable To Select Requested Database."
i currenty run a game. Well just adding flies now and sorting it out etc..... but when i go to like online.php or anyother flie that the DB runs off.. i get "Unable to select requested database."

Value "000" Stored As "0" In Text Field?
I've managed to import my data from a CSV using SQLyog Enterprise, however, I have a problem with the table storing the text value "000", MySQL truncates it to "0", unfortunately, I need the value to be stored as "000", does anyone know of any way that I can force the field to store all the characters, rather than treating it as a number? Surely a text field should treat any numbers stored within it as text?

What Is The Meaning Of The Letters "AB" In "MySQL AB"?
What does "AB" stand for in "MySQL AB".

Return A "true" Or "false"
i wanna do a "select", where if the script finds any matches, i dont wanna know about them. Instead, i just want it to return a a value: true (1) ou false (0).

ie.:

$sth = $dbh->prepare("SELECT * FROM maps WHERE name='algo' LIMIT 1");
$sth->execute();

if (match is true) {
print "True Match. A match was found";
}
else {
print "False Match. No matches were found.";
}

"Group By" Forgets "Order By"
I'm trying to build a simple RSS reader for a client and am running into some serious problems with the GROUP BY in MySQL. Basically I have two tables: one that holds all the entries and one that holds all the feeds. Then what I can do is say:
[MYSQL]SELECT * FROM entries WHERE feed_id='13' ORDER BY date DESC[/MYSQL]
That will select all the entries that belong to feed #13. This is fine, but what I'm doing now is building a library that will display the feed_id with the latest entry with that id. So I would think it would be something like the following:
[MYSQL]SELECT * FROM entries GROUP BY feed_id ORDER BY date DESC[/MYSQL]
That should get me the single latest entry from each feed_id. However it's forgetting the ORDER BY clause all together and seemingly ordering it by the auto-incrememnt value in the entries field.

Select Where Four Of The Ten Fields Are Same (was "query")
I have a table with ten fields. Now I want to select * from table where four of the ten fields are same. How can I do the query?

MySQL "SELECT" Speed
I've read from MySQL's page about optimization, but still have two specific questions:

1) Let's say I do something like "SELECT * FROM table WHERE col1=val1". And, in this specific case, I know that any time col1=val1, that col2 will always equal val2. So, would it be any faster (or slower) to do: "SELECT * FROM table WHERE col1=val1 AND col2=val2"?

2) Does the order of the argument in the WHERE part affect speed? Ex: "WHERE col_index=val1 AND col=val2" versus "WHERE col=val2 AND col_index=val1"?

"select Into Outfile " Issue
I am running MYSQL 5.0 ON WINXP pro.

I have the following procedure:

Select From Table Where Fieldvalue != ""
Does anyone know the MySQL Query for selecting all fields from a table where the fieldvalues do NOT equal nothing?

Select Value Separated By Comma ","
how to select the value which separated by ',' or comma?

About "Select Count(*)" Always Returns 0.
my mysql version is:

Ver 14.7 Distrib 4.1.14, for pc-linux-gnu (i686) using EditLine wrapper

OS: uname -a
Linux backup_srv1 2.4.29 #4 SMP Thu Mar 24 10:31:26 CST 2005 i686 unknown unknown GNU/Linux

Desc:

My statement is
"SELECT COUNT(1) FROM t_card_info_035 WHERE Fcommodity_id = 0x1111 AND Fseller_uin =111111 AND Fdeal_id = 12345;"

my client is a daemon, when it started, it can return the correct result, (such as 4 ) but after two or three days later, it always returns 0. I had gdb into it and find that the result in the result row is 0. but when I restart my daemon program, it will be ok again. This made me crazy... it is a mysql c API bug or something else?

My daemon client is programmed by c++ , and use the c API to connect mysql. I had check that there is nothing wrong with my code, and it has no memory leak or somthing else.

"Select Least" Condition?
I have a MySQL table that has a float field...(apr)
What I am trying to do, is select the three rows that contain the least amount in the apr field, but am not too sure on what query/condition to use...

Could somebody please point me in the right direction?

Could I use something like:

SELECT * FROM table_name ORDER BY apr ASC LIMIT 3
D

How To Select "Next" Record
I've written an application around a database with about 15K records. I need to analyze 1 record at a time, but then skip to the next record based on the index order. Currently, I do this with the following statement:

SELECT * FROM Database ORDER BY IndexVariable1, IndexVariable2 LIMIT 25 ,1

This gets the 25th row of the database. When I want to get the next row, I change the 25 to 26 and repeat the same SELECT satement above.

The problem is that this is very slow. It takes about a half-second per select (unless the read was recently cached and is repeated).

I suspect tht I'm using a brute force method to read the next record, and that a quicker, more elegant method is possible.

Is there a "SELECT NEXT" statement that will execute more quickly than my ugly cludge? Or can anyone offer a better approach that I can try?

"select Match X Against Y" To %
I know this is impossible to do correct since relevance is not calculated as this in the first place, but is there some resonable convertion between the number you get when you
SELECT MATCH (<fulltext index>) AGAINST (<string>) AS relevance...
and percentage value?

Select "MyValue" FROM Tbl? (php)
I would like to do is have a certain value/variable returned in my search results. Like

$myValue="hello";

sql="SELECT column1,'".$myValue."' FROM myTable LIMIT 1"

so it would return something like

column1Info - hello

If you are wondering why I would need to do something like this, I'm trying to use a select in an insert statement to update two columns of a table, however one of the values inserted is a variable. I can do this:

INSERT INTO EB_books (catagoryID,bookName) select catagoryID,CURDATE() from EB_catagories LIMIT 1

But instead of the CURDATE() I would like that to be a value/varialbe in there.

Speed Up "SELECT *"
I'm really at a loss here, so i hope someone can help me.

I'm writing an application in VB and i want to search through a database on the server.
I have a table called TEST_TABLE, it has three fields:

id, name, log

"id" is a BIGINT and the primary key
"name" is a VARCHAR(128)
"log" is a MEDIUMTEXT

The table has 5000 entries.

When i perform a "SELECT * FROM TEST_TABLE" it takes almost 10 seconds to complete.

Is there any way to speed this up?

I already tried creating an index, but it doesn't seem to be possible to create an index that holds all the fields.

Where A=1 And Either B=2 Or C=2 (was "sql Statement Help")
SELECT name FROM table WHERE A=1 AND B=2 OR C=2

I would like to get the name when A=1 and either B=2 or C=2.

Is There A "contains" Sql Statement?
I'm trying to run a query where i'm testing if one of the fields contains a phrase, s/thing like this:

select *
from myTable
where companyID='5'
and company_role contains 'admin'

how could i go about translating that "contains" statement to some real sql?

"in" Statement
i have written a programm in java using an oracle database. Now i want to use it at home with a mysql database. The problem is that mysql doesn't support the keyword in . would be nice if somebody could help me.

SELECT * FROM PARTNER p WHERE p.PID in
(select PID FROM LIEFERT where AID ='"+getAid()+"') ";

It's possible that there are more rows than one in the Liefert-table.

REGEXP To Find "foo-bar" And "foo Bar"
Does somebody know how to solve this:

select * form geo,places where geo.name = places.name

Where geo.name could be New York
and geo.places could be New-York

Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost:

SELECT *,count(*)
FROM cancons c, musics m, discos d, r_discos_cancons rdc
WHERE c.c_id_music = m.m_id
AND rdc.rdc_id_disc = d.d_id
AND d.d_id_music = m.m_id
AND m.m_id = 24
GROUP BY c_id

note:
cancons (ca) = songs (en)
discos (ca) = cd's (en)
music (ca) = musician (en)

don't worry for the WHERE part. i need it because of the foreign keys.
this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think.

I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.

Select User's Templates Or Default Templates (was "How To Do This?")
I don't know if this is possible.

I have an email_template table, with id, user_id, template_id, subject, message.

There are default templates, marked with user_id = NULL. If a user customizes a template, that is copied with user_id = whatever their ID is.

So say there are 3 templates. Say user 1 has customized template 2. Then when the script lists the templates, it'll list template 1, the default, their customization of template 2, and the default template 3.

How can I do this? Basically, select all templates with user_id = null, unless there's one with user_id = whatever the user's id is, then select that one.

I had it working with some group by clause but I'm not sure why it worked. It might have been unintentional. but the query was

select * from email_template where user_id = 1 or user_id is null group by template_id order by template_id;
It always returned the template with a user id if it existed, but I don't know why.

"AS" In A Select
Probably not understand this fully but for some reason when I do the following query below:

SELECT u.uid, u.username, u.password, m.uid AS moderator_uid, a.uid AS admin_uid FROM vid_users u, vid_moderators m, vid_admins a WHERE u.uid= ཆ' LIMIT 1

When I get the results, moderator_uid and admin_uid always is 1. Basically I want the query to look into 3 tables and all of them have a field called uid. I used the AS so it would look clearer on which uid I get. WHen the uid=4 moderator_uid and admin_uid is suppose to equal 4 also but why does it keep coming out as 1?

First And Last Time In Each Date By Empl (was "Help Me With This Select Statement")

I have the following table which has the following fields:logid, empid, transdate,transtimeI want to select the first and last transtime in a specific date for every employee, how can i do that?

I have the following table which has the following fields:

logid, empid, transdate,transtime

I want to select the first and last transtime in a specific date for every employee, how can i do that?