GROUP BY Function And Getting Rid Of NULL Results

IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere!

The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....

View Replies !

---

Null In Replace Function
when i try REPLACE(field, 'F',N) i have NULL in all records where present "F" and dont present. Help me please replace "F" to NULL value in SELECT!

View Replies !

---

Aggregation Function For Use The Non-null Value
I expect the answer is simple/obvious, but I'm in a situation where I need data from different tables to show up in different columns in the final output. So I intend to use a union to get the values into the right columns, then use "group by" to collapse the multiple rows and have each of the rows that collapse together for a group contribute a value for one column of the final single-row result for that group. A simple picture:
groupval1 groupval2 collapsecol1 collapsecol2
------------------------------------------------
foo bar myval1 NULL
foo bar NULL myval2
bazz boff myval3 NULL
bazz boff NULL myval4
turns into
foo bar myval1 myval2
bazz boff myval3 myval4
after the "group by" on the first two columns.

View Replies !

---

Getting NULL For Results
SELECT DATEDIFF('date_hired,'date_fired') from users

and i get all NULL for results

i think i should be getting number of days apart from the two dates


date_hired and date_fired are both date columns

so whats wrong with this

View Replies !

---

Group Function
I have a table salary, and when I select it, it looks like this ....

View Replies !

---

Group By With Rollup Replace Null Value
I have serched the net for several hours and I can´t figure out if this should work or not. If you read MySQL docs you can find

Quote: Because the NULL values in the super-aggregate rows are placed into the result set at such a late stage in query processing, you cannot test them as NULL values within the query itself

and I guess we should trust the docs but then there are several comments that shows that you can use IFNULL or even better COALESCE to do the job. I have also found several other pages that shows the same thing.

When I try it, it doesn´t work. Can anyone confirm or deny if it´s possible?

View Replies !

---

Group By To Include Null Values
I have a query that is half working, maybe I am going around it the wrong way.

The idea is that it should list all types t1.type and show how many types there are in another database including where there are none.

The result ot the query below is the subset, not the full range with the zeroes...

I have had a good look around and added "where t2.type is not null" have found not null works a bit it shows only one additional zero results:

select
t1.id,
t1.type,
t1.description,
count(t2.id)
from
specialist_type as t1
left join specialist_inclusion as t2
on t1.id = t2.type
where
t2.type is not null
group by
t2.type

View Replies !

---

Do Not Show Results Where Value Is Null ?
I've got the following query which returns a search result, but I want to exclude results where 'intro' is a null value, ie where 'intro' is not null. What's the correct syntax for that ? Thanks in advance...

PHP

SELECT id, title, intro, body, modified, created
FROM content
WHERE body LIKE '%searchString%'
ORDER BY modified DESC

View Replies !

---

Display Results Even When NULL
Code:
SELECT
*
FROM
calendar_types,
calendar_allowances
WHERE
calendar_allowances.user = 'user'
AND
calendar_types.id = calendar_allow.type

View Replies !

---

Count Function & Group By
Getting an error saying I cannot Group on JState2

Is this a version issue? What is an alternate?
(want to show only results having greater than 2 of the same JState 's)

SELECT COUNT(JState) as JState2, JID, StateTerm
FROM JPosts2, State
WHERE State.StateAbbr=JPosts2.JState AND (`JClass` = 'Mechanics')
GROUP BY JState2 HAVING JState2 >4
ORDER BY JState

View Replies !

---

Invalid Use Of Group Function & SUM() And MAX()
I have the following Sql Statement... I'd like to find the sum of hrs worked by an employee in the month, the required number of hrs, and the sum of the difference between the two, the worked hrs are calculated by subtracting the max vtranstime - min vtranstime and the required hrs are calculated by subtracting max to1 - max from1....

View Replies !

---

Make GROUP_CONCAT Not Group Null Values
I have a complex SELECT statement that uses GROUP_CONCAT to group like values -- which has been drastically over simplified below. From what I can surmise, GROUP_CONCAT is treating NULL as a value -- which I don't want. Is there a way to make it ignore null values to produce the 'desired output' below?

TABLE: test
+------------+------------+
| col_1 | col_2 |
+------------+------------+
| NULL | a |
+------------+------------+
| NULL | b |
+------------+------------+
| 1 | c |
+------------+------------+
| 1 | d |
+------------+------------+

Current Output
+------------+------------+
| col_1 | col_2 |
+------------+------------+
| NULL | a|b |
+------------+------------+
| 1 | c|d |
+------------+------------+

Desired Output
+------------+------------+
| col_1 | col_2 |
+------------+------------+
| NULL | a |
+------------+------------+
| NULL | b |
+------------+------------+
| 1 | c|d |
+------------+------------+

View Replies !

---

Error:invalid Use Of Group Function
version:mysql 4.0.18 for win

hi,all dear:)

when execute sql-statement,
error raise:

update customer,cu_order set customer_point=0
where customer_keyid=order_customerid and
DATE_SUB(now(),INTERVAL 1 YEAR)=DATE_FORMAT(max(order_time),"%Y-%m-%d 23:59:59")

error:invalid use of group function

i beg someone tells me why?how resolve?

thx

View Replies !

---

Invalid Use Of Group Function - Max(`mainid`)+1
I am trying to insert a incremented number into the menu table. I will have an autoincremented number as well as the mainid number. When I tested the code that is below, the above error happened. Can someone please tell me the correct way of doing this? I am getting really frustrated with this. Nothing wants to work for me.

require "config.php";
$insert06 = "INSERT INTO `menu` (`mainid`)
                VALUES (max(`mainid`)+1)"; // error line
if (mysql_query ($insert06)) {
    print "Mainid added.";
    $query2 = mysql_query("SELECT mainid FROM menu") or die ("Could not query because: ".mysql_error());
    while($row2 = mysql_fetch_array($query2)) {
        echo $row2['mainid']." = Mainid";
    }
} else {
    print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $insert06.</p>";
}
mysql_close();

View Replies !

---

Group By Results In One Row
Take the following data

Code:

date | type
2005-04-02 | soft
2005-04-02 | soft
2005-04-02 | click
2005-04-02 | click
2005-04-03 | soft
2005-04-03 | soft
2005-04-03 | click
2005-04-03 | click

Code:

select count(*) as total, type, date from tbl group by type, date

which would give me

Code:

total | type | date
2 soft 2005-04-02
2 click 2005-04-02
2 soft 2005-04-03
2 click 2005-04-03

I was wondering if there was a way,perhaps with a join, to get the data into the same row?

Code:

totalsoft | totalclick | date
2 2 2005-04-02

View Replies !

---

Group Records? Only List Users With Only NULL Values
I have this result-table (running on 4.0.24), how do I collect all o_user_id which does not have NOT NULL value in reciept_id?

(I do not want the 745-user in result, as it contains a value for reciept_id)

+-----------+------------+-----------+-----------+-----------+---------+
| o_user_id | reciept_id | r_user_id | d_dist_id | r_dist_id | item_id |
+-----------+------------+-----------+-----------+-----------+---------+
| 745 | 18 | 745 | 1 | 1 | 1 |
| 745 | NULL | NULL | 2 | NULL | 1 |
+-----------+------------+-----------+-----------+-----------+---------+

View Replies !

---

Loading Data Results In NULL Rows??
I am using LOAD DATA very simply but it is resulting in all the rows being NULL and I can't understand why.

Here is what I'm doing:
LOAD DATA INFILE '/var/www/Data/testfile.csv'
INTO TABLE trial.testdb
FIELDS TERMINATED BY '¿'
LINES TERMINATED BY '
'
IGNORE 2 LINES;

The command runs successful but when I check the data, the first column which is a date field, comes up at 0000-00-00 and all other columns are loaded as NULL.

Any clues on why this is happening? The # of columns in the file match up with what's in my table.

View Replies !

---

Sorting GROUP BY Results
I'm using mySQL version 3.23.58.
Is there a way to sort the results when using 'GROUP BY' in a statement? I'm using a mySQL database for photo information and I display an index of users who have photos on a PHP page. The statement would be similar to:

SELECT imageid, title, username FROM photos GROUP BY username ORDER BY username

However, I have an additional field named 'indexphoto' that is either set for 0 or 1 so users can choose a photo they want displayed on the index. The problem is, not everyone has set the 'indexphoto', so I can't simply add 'WHERE indexphoto=1' to the statement. I need to have the results display one photoid for each user, and if that photo has the 'indexphoto' field set, to display that photo.

View Replies !

---

GROUP BY Orders Results Differently
I'm experiencing an issue with I have no doubt about is because I don't fully understand how the GROUP BY statement works.

I have a table like so:

categoryId | subCategoryId | id | otherfields....
-------------------------------
1 1 1
1 1 2
2 1 3
2 2 4
3 1 5

When I select the newest of each category like so:

SELECT * FROM table GROUP BY categoryId DESC

I recieve:
categoryId | subCategoryId | id | otherfields....
------------------------------------------------------
3 1 5
2 2 4
1 1 2

Exactly how I want it... the newest row of each category (the one with the highest id) is returned.

But when I try this, to select the newest one of the subcategories:

SELECT * FROM table WHERE categoryId='1' GROUP BY subCategoryId DESC

I recieve:
categoryId | subCategoryId | id | otherfields....
------------------------------------------------------
1 1 1

but I want:
categoryId | subCategoryId | id | otherfields....
------------------------------------------------------
1 1 2

View Replies !

---

Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories.

What I want the query to return is this:

Date | ShopperID | VATable Amount | Non VATable Amount|
2006-3-1 | 802135 | 146.95 | 54.00 |

Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above.

I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product:

+------------+----------+-----------------+---------------------+
| Date | ShopperID| VATable Amount | Non VATable Amount |
+------------+----------+------------+---------------+
| 2006-09-04 | 805284 | 0 | 64.00 |
| 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a
| 2006-09-04 | 805287 | 3.25 | 0 | customer has
| 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products
| 2006-09-04 | 805287 | 4.95 | 0 | - I need total
| 2006-09-04 | 805327 | 0 | 53.95 | under each
| 2006-09-04 | 805335 | 0 | 58.95 | ShopperID
| 2006-09-04 | 805414 | 0 | 64.95 |
| 2006-09-04 | 805414 | 3.25 | 0 |
| 2006-09-04 | 805414 | 0 | 64.00 |
| 2006-09-04 | 805414 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.95 |
| 2006-09-04 | 805423 | 0 | 69.00 |
| 2006-09-04 | 805423 | 0 | 64.00 |
| 2006-09-04 | 805423 | 3.25 | 0 |
+------------+-----------+----------------+--------------------+

Heres the query currently getting these results (without the GROUP BY ShopperID).

SELECT Date,orders.ShopperID,
CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?('
THEN items_ordered.price -- If size 7 or above add price
WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag'
THEN items_ordered.price -- If it is an accessory add VAT
ELSE 0
END AS 'VATable Amount',

CASE
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?('
THEN items_ordered.price -- If size 1 - 6 add price to other column
WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?('
THEN items_ordered.price -- If size kids 12/13 add price to other column
ELSE 0
END AS 'Non VATable Amount'
FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= &#55614;&#57158;-1-30') AND (Date <=

&#55614;&#57158;-9-31');

View Replies !

---

Invalid Use Of Group Function (was "Baffled By Query Error")
trying to figure out why I keep getting this error with the following query:

SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins
FROM account a LEFT JOIN calls c ON c.account_id = a.id
WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2
AND SUM(c.agent_product_time) >= '500' GROUP BY c.account_id ORDER BY mins

ERROR: #1111 - Invalid use of group function

View Replies !

---

MySQL 4.0.17 Data Scrambles, Not-null Fields Become Null, Etc.
I'm having the most bizarre problem with PHP/MySQL that I've ever
faced and it's an urgent matter (of course) to try to fix it ASAP.

We have one record inserted into a table with 75 columns, whereby half
of the columns are not-null, but for some bizarre reason, the fields
are null. The rest of the columns that are null are completely
scrambled (e.g. "first_name" is in "address2", "fax" is in "url", "url"
is in "last_name", etc.)

This just recently occurred and seems to have no history otherwise. It
occurred sometime on Saturday when the record was inserted but logs
indicate no unusual activity at that time.

Has anyone seen this before and found a way to prevent it from
occurring again?

View Replies !

---

Incorrect Integer Value: 'NULL' For Column Even If NULL Is Allowed
mysql> desc my_table;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| id | varchar(19) | NO | PRI | | |
| value1 | int(11) | YES | | NULL | |
| value2 | int(11) | YES | | NULL | |
+------------+---------------------+------+-----+---------+-------+

I want to import data from a file that looks like this:
9bcd6f7b47a30fb8_01;NULL;NULL

With following command:
mysql> load data infile 'filename' into table my_table fields terminated by ';';

And get following error:
ERROR 1366 (HY000): Incorrect integer value: 'NULL' for column value1'' at row 1

So the system denies me importing NULL values into a field where NULL is allowed.

View Replies !

---

Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records

select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null

but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records

select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null

View Replies !

---

Getting Null Values From A TEXT NOT NULL Field
I have created a table with a field labeled Description which is a TEXT field which does not allow null values. It's defined with

Description TEXT NOT NULL,

However, when empty strings are stored in the Description field, I get NULLs back when querying the database. I'm writing an ASP application which connects to the MySQL database through ADODB. The MySQL version is 5.0.45. The engine is InnoDB.

View Replies !

---

Is There Any MySql Function Which Is Equalent To Oracle Last_Value Function
I am converting Oracle view to MySql where I am having the follwoing syntax in Oracle
last_value(col1 ignore nulls) over (partition by col2 order by col2, col3 rows between unbounded preceding and 1 preceding)

which I need to convert to MySql.

View Replies !

---

Full Yearly Weekly Group By Group Report
I have table called tblTransactions:

TblTransactions
Group | Months | Year | Transaction
A | 3 | 2007 | 45
B | 2 | 2007 | 89
A | 7 | 2007 | 50
A | 8 | 2007 | 34
B | 4 | 2007 | 55
A |12 | 2007 | 10
A | 1 | 2008 | 88
B | 3 | 2008 | 34
B | 5 | 2008 | 70
A | 5 | 2008 | 45
A | 9 | 2008 | 88
B | 7 | 2008 | 99
A |10 | 2008 | 77
A |11 | 2008 | 99

How is the T-SQL to make the result like this (the result will display all of the week (FULL), the target is I can compare week by week between years and group.

A | 1 | 2007 | 0
B | 1 | 2007 | 0
A | 2 | 2007 | 0
B | 2 | 2007 | 89
A | 3 | 2007 | 45
B | 3 | 2007 | 0
A | 4 | 2007 | 0
B | 4 | 2007 | 55
A | 5 | 2007 | 0
B | 5 | 2007 | 0
A | 6 | 2007 | 0
B | 6 | 2007 | 0
A | 7 | 2007 | 50
B | 7 | 2007 | 0
A | 8 | 2007 | 34
B | 8 | 2007 | 0
A | 9 | 2007 | 0
B | 9 | 2007 | 0
A | 10| 2007 | 0
B | 10| 2007 | 0
A | 11| 2007 | 0
B | 11| 2007 | 0
A | 12| 2007 | 10
B | 12| 2007 | 0
A | 1 | 2008 | 88
B | 1 | 2008 | 0
A | 2 | 2008 | 0
B | 2 | 2008 | 0
A | 3 | 2008 | 0
B | 3 | 2008 | 34
A | 4 | 2008 | 0
B | 4 | 2008 | 0
A | 5 | 2008 | 45
B | 5 | 2008 | 70
A | 6 | 2008 | 0
B | 6 | 2008 | 0
A | 7 | 2008 | 0
B | 7 | 2008 | 99
A | 8 | 2008 | 0
B | 8 | 2008 | 0
A | 9 | 2008 | 88
B | 9 | 2008 | 0
A |10 | 2008 | 77
B |10 | 2008 | 0
A |11 | 2008 | 99
B |11 | 2008 | 0
A |12 | 2008 | 0
B |12 | 2008 | 0

View Replies !

---

CREATE TABLE :: Null And Not Null
I recently created a new table in a database and wanted some fields to accept nulls (NULL) and others to not accept nulls (NOT NULL). The table was built correctly, however, when I went to edit a row in the Query Window of MySQL Browser it allowed me to enter in the row when the NOT NULL fields had no values attached to them - this should fail and stop me from entering a row.

Here is the script for the table I created;

CREATE TABLE SLG (
SLG_ID int(11) NOT NULL auto_increment,
SLG_CODE CHAR(1) NOT NULL,
SLG_DESCRIPTION VARCHAR(255) NOT NULL,
SLG_DATE_CREATED DATETIME NOT NULL,
SLG_DATE_MODIFIED DATETIME NOT NULL,
PRIMARY KEY (SLG_ID)
);

When I apply the changes in this window it just enters NULLS into all the other fields. Is this a Query Browser issue. I tried doing the same with an INSERT statement and received the same result.

Also - related to creating a table - is it possible to create global Defaults on a database and then reference those in the DEFAULT statement of the CREATE TABLE command. MS SQL has a system function that can be executed after a table is created to bind a global default to a specific column.

View Replies !

---

Insert Null To Not Null Column
I have build a replication environment in my environment. the master is MySQL 5.0.24a-standard-log and the slave is 5.1.30-log. the configuration is not the same between them.

I have found a replicaiton error in the slave today says: .....

View Replies !

---

Group By With Order BY, Or INSERT INTO (SELECT * GROUP BY)?
I seem to have a few duplicates entries in a very large database.

My database has two identical tables. One is written to on a regular basis (every few seconds) and the other is used for queries, and updated from the write table on a daily basis.

I'm trying to get rid of duplicate entries for the queries, and I thought the most effective way to do this would be to run the query as
Expand|Select|Wrap|Line Numbers

View Replies !

---

GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example,

I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name.
Sample:
------------------------------
id, name, dtadded
------------------------------
1 name1 2007-10-15 00:00:00
2 name2 2007-10-15 00:00:00
3 name1 2007-10-16 00:00:00
------------------------------

I need to receive 2 results (for each name)
3 - name1 - 2007-10-16 00:00:00
2 - name2 - 2007-10-15 00:00:00

If I use SQL code:

SELECT images.id, images.dtadded, images.name, count(name)
FROM images
GROUP BY `name`
ORDER BY dtadded DESC

I get results
1 - name1 - 2007-10-15 00:00:00
2 - name2 - 2007-10-15 00:00:00

It groups records with first row in database, but I need last row in table for each name.

Question: How can I order results in side group to get needed results as described above?

View Replies !

---

NULL And NOT NULL Settings
I have a MySQL table that stores name and age of a person. The name field needs to have a value; the age field is optional.I'm quite confused now with the NULL and NOT NULL settings... It seems that neither one does what I want (rejecting an INSERT query where the name field is empty)...So what exactly do I have to do?

View Replies !

---

VARCHAR Null/Not Null
I'm having a problem with a table that is already created - I want insert attempts to FAIL when the column for 'firstname' is empty (a VARCHAR field). Currently, the column is set to Null=YES and a Default of 'NULL'. This allows records to be inserted with no 'firstname' value (NULL appears in the column). If I change it in phpmyadmin 'structure' to Null=NO and Default of [empty field] then it allows records to be inserted with no 'firstname', and 'firstname' shows empty in the column

View Replies !

---

PK Field Not Null Is Being Null
I m newbie. I made a MySQL database table and in the primary key field I set the data type VAR CHAR.I also set it not null .BUT still I can insert blank values in this PRIMARY KEY field. If I just blank my primary key field by this query- Insert Tablename Values ('','','','','',''); (if my table has 6 fields).HOW IS IT POSSIBLE. How can PK field allow blank value when I set not null. HOW TO SOLVE IT.

2. I use a MySQL GUI Tools from http://dev.mysql.com/downloads/. In Table viewer of Query browser, each time I insert the first field, it sets the datatype to INTEGER,NOT NULL, AUTO INCREMENT. But I change it to VARCHAR as needed. Do I have to/should use Integer type in PK field and can Varchar be incremented.

3.Can I set user privilege to a particular row (on a particular PK); HOW?

4. Is it correct/incorrect that the name of the foreign key has to be the same as the name of the corresponding PK. I saw that in MySQL I can easily change the FK name to anything else and then just make a reference of FK to the PK.

5. How can I make a one-to-one relationship in MySQL;

View Replies !

---

Setting Null To Not Null How?
i have a mysql table.. (duh!)

a field in it is set to null and i want to unset it to make it not null.

how do i do that ? by default it takes null values (its weird.. default is null while null column is a no.. so it cant take null values!)

View Replies !

---

Top Record From Each Group In GROUP BY
I never could figure out a good way to do this.
I am doing a group by statement, and want the top record from each group to be returned only. My query is to find the current win/loss streak for each team.
Here is the sql:
SELECT
id,
team_name,
result,
MIN(date) AS startdate,
MAX(date) AS enddate,
COUNT(*) AS games
FROM (
SELECT
t.id,
t.team_name,
sched.date,
CASE sched.draw
WHEN 1 THEN 'draw'
ELSE CASE sched.winner_id
WHEN t.id THEN 'win'
ELSE 'loss'
END
END AS result,
(
SELECT COUNT(*)
FROM fantasy_schedule sched2
WHERE ( CASE sched2.draw
WHEN 1 THEN 'draw'
ELSE CASE sched2.winner_id
WHEN t.id THEN 'win'
ELSE 'loss'
END
END ) <> result
AND sched2.date >= &#55614;&#57157;-01-01' AND sched2.date < &#55614;&#57158;-01-01'
AND sched2.date < sched.date
AND sched2.fantasy_league_id = 6
AND (sched2.fantasy_team1_id = t.id OR sched2.fantasy_team2_id = t.id)
) AS rungroup
FROM fantasy_schedule sched
JOIN fantasy_team t ON t.id = sched.fantasy_team1_id OR t.id = sched.fantasy_team2_id
WHERE sched.last_update < NOW()
AND sched.date >= &#55614;&#57157;-01-01' AND sched.date < &#55614;&#57158;-01-01'
AND sched.fantasy_league_id = 6
) bighonker
GROUP BY id, team_name, result, rungroup
ORDER BY enddate DESC
And a subset of what is returned:


+----+----------------------------+--------+------------+------------+-------+
| id | team_name | result | startdate | enddate | games |
+----+----------------------------+--------+------------+------------+-------+
| 1 | The Macho King | loss | 2005-12-17 | 2005-12-17 | 1 |
| 1 | The Macho King | win | 2005-12-11 | 2005-12-11 | 1 |
| 1 | The Macho King | loss | 2005-11-24 | 2005-12-04 | 2 |
| 1 | The Macho King | win | 2005-11-20 | 2005-11-20 | 1 |
| 1 | The Macho King | loss | 2005-11-13 | 2005-11-13 | 1 |
| 1 | The Macho King | win | 2005-10-30 | 2005-11-06 | 2 |
| 1 | The Macho King | loss | 2005-10-16 | 2005-10-23 | 2 |
| 1 | The Macho King | win | 2005-10-09 | 2005-10-09 | 1 |
| 1 | The Macho King | loss | 2005-10-02 | 2005-10-02 | 1 |
| 1 | The Macho King | win | 2005-09-08 | 2005-09-25 | 3 |
| 2 | General Grievous | loss | 2005-12-17 | 2005-12-17 | 1 |
| 2 | General Grievous | win | 2005-12-11 | 2005-12-11 | 1 |
| 2 | General Grievous | loss | 2005-11-24 | 2005-12-04 | 2 |
| 2 | General Grievous | win | 2005-11-13 | 2005-11-20 | 2 |
| 2 | General Grievous | loss | 2005-11-06 | 2005-11-06 | 1 |
| 2 | General Grievous | win | 2005-10-23 | 2005-10-30 | 2 |
| 2 | General Grievous | loss | 2005-10-16 | 2005-10-16 | 1 |
| 2 | General Grievous | win | 2005-10-02 | 2005-10-09 | 2 |
| 2 | General Grievous | loss | 2005-09-08 | 2005-09-25 | 3 |
| 3 | Ultimate Jarin | win | 2005-11-06 | 2005-12-24 | 8 |
| 3 | Ultimate Jarin | loss | 2005-10-23 | 2005-10-30 | 2 |
| 3 | Ultimate Jarin | win | 2005-10-09 | 2005-10-16 | 2 |
| 3 | Ultimate Jarin | loss | 2005-09-25 | 2005-10-02 | 2 |
| 3 | Ultimate Jarin | win | 2005-09-08 | 2005-09-18 | 2 |
| 4 | The Hulkamaniacs | loss | 2005-12-11 | 2005-12-11 | 1 |
| 4 | The Hulkamaniacs | win | 2005-12-04 | 2005-12-04 | 1 |
| 4 | The Hulkamaniacs | loss | 2005-11-24 | 2005-11-24 | 1 |
| 4 | The Hulkamaniacs | win | 2005-11-06 | 2005-11-20 | 3 |
| 4 | The Hulkamaniacs | loss | 2005-10-30 | 2005-10-30 | 1 |
| 4 | The Hulkamaniacs | win | 2005-10-09 | 2005-10-23 | 3 |
| 4 | The Hulkamaniacs | loss | 2005-09-18 | 2005-10-02 | 3 |
| 4 | The Hulkamaniacs | win | 2005-09-08 | 2005-09-08 | 1 |
| 5 | WHO WANTS TO KISS HOMER? | win | 2005-12-04 | 2005-12-04 | 1 |
| 5 | WHO WANTS TO KISS HOMER? | loss | 2005-11-20 | 2005-11-24 | 2 |
| 5 | WHO WANTS TO KISS HOMER? | win | 2005-11-13 | 2005-11-13 | 1 |
The top record in each group is the current streak. I want only that top record for each teamr returned. I could simply 'order by enddate DESC' and in my php script loop until I have one record per team, but is there any way I can only return the top result for each team in the query itself?

Here is the corresponding table schema:



+-------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------------------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| fantasy_league_id | int(10) unsigned | | MUL | 0 | |
| week_num | tinyint(3) unsigned | | MUL | 0 | |
| fantasy_team1_id | int(10) unsigned | | MUL | 0 | |
| fantasy_team2_id | int(10) unsigned | | MUL | 0 | |
| winner_id | int(10) unsigned | YES | | 0 | |
| date | date | | | 0000-00-00 | |
| last_update | datetime | | | 0000-00-00 00:00:00 | |
| draw | tinyint(3) unsigned | | | 0 | |
+-------------------+---------------------+------+-----+---------------------+----------------+

fantasy_team
+---------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| fantasy_league_id | int(10) unsigned | | MUL | 0 | |
| user_id | mediumint(8) unsigned | | MUL | 0 | |
| team_name | char(40) | | | | |
| fantasy_division_id | int(10) unsigned | YES | MUL | NULL |
+---------------------+-----------------------+------+-----+---------+----------------+


View Replies !

---

Display Results Within Results
1) I have already did a search for: "Results within results" on this site, in PHP & MySQL forums ( I think) properly...and one search resulted in over 100 pages etc.

From the below structure of my DB, I would like to get the code from the below URL working on my existing data I have, but I am having trouble and I am just getting flustered....

I would eventually like to have the user "select" first a $State and then $County from a "drop down" for now, and eventually a "map" but the "drop down" for these (2) will be a must have....but I am just trying to use this function first.

At present, I am unable to even get proper printed results, and I know it has to do with something on the variable call end that is screwing me up.

2) This is what I am using (learning) from:

3) This is my mySQL db structure: ....

View Replies !

---

LIKE And IS NOT NULL
Are the LIKE and IS NOT NULL options available in MySQL - these 2 options
are availble in Access and MS SQL.

LIKE is for searching using wildcards - e.g WHERE Name Like 'AD%'.

View Replies !

---

Getting The Null
I have two tables to compare
Code:
Schedule of Subject(table 1) previous subject taken(table 2)
Semister ID Subj ID | Semister ID Subj ID
1 1 | 1 1
1 2 | 1 2
1 3 | 1 3
1 4 | null null
1 5 | null null



My PK for this should be Semister ID.

My table 2 has only 3 records, so obviously if I compare
table2 from table1 (which has 6 records), table 2 should show the null value because it has no record for the following semister and subjects.

View Replies !

---

NULL/NOT NULL
I'm drawing a blank when it comes to using NULL/NOT NULL in my tables... can someone refresh my memory about this option in laemen terms. Also how or why does the field with this option have "NULL" entered in the field as the default value?

View Replies !

---

NULL Or Zero
`timestampLastFailedLogin` int(11),
If there is no value(field was never accessed or used) should I leave it with NULL value
or should I give it 0 as default value as follows?

`timestampLastFailedLogin` int(11) UNSIGNED NOT NULL DEFAULT '0'

View Replies !

---

WHERE A OR B OR C IS NOT NULL
I am trying to sort my recordset so that records will only display if certain columns contain information. I am unsure of the syntax on this. My first two columns will always be populated with data, but I only want to display the record if any one of the rest of the columns contain data. I know that .Code:

SELECT *
WHERE a OR b OR c IS NOT NULL

is not quite right.

View Replies !

---

Using NULL Vs 0
I have a DB i MySQL that uses many int(1) type fields. I store there some time value 0 or NULL.
My question is which solution is faster from DB point of view?
Should I store NULL for all those fields or is "0" better solution?
Which one takes less space and time to process?

View Replies !

---

NULL And NOT NULL
I understand the meaning of NULL, but I don't understand its usage. For example, a field declared to be NOT NULL, but has a NULL value. Or when a field is declared to be NULL, and yet values can still be placed into it. I don't understand the logic behind this.

View Replies !

---

IS NOT NULL
I tried below mentioned sql statement

mysql> SHOW COLUMNS FROM Country WHERE `Default` IS NOT NULL;


and suprinsgly it even list down those record as well where coloumn 'default' is NULL

Note : In the preceding statement, the column name (Default) must be given as a quoted identifier because it is a reserved word.


Any specific reason why it produced record having value as NULL, i tried somewhat similar to my own table , and it works fine for my other table.

View Replies !

---

WHERE Id NOT IN (NULL)
CREATE TABLE t (id INTEGER PRIMARY KEY);
INSERT INTO t SET id=1;
SELECT * FROM t WHERE id NOT IN (2); # one row
SELECT * FROM t WHERE id NOT IN (NULL); # empty set

This is a big surprise, since in my mind 1 is not in (NULL).

Here's another strange difference:

SELECT (1 IN (NULL)); # (NULL)
SELECT (1 IN (2)); # (0)

View Replies !

---

Null OR Value
how can I write a query that will return results that are either null or a specified value e.g.

e.g something like

SELECT * FROM Table 1 where ID = null OR 5

View Replies !

---

Null Vs Not Null
I create a field in a table as a "Not Null" would this prevent a new record to be created unless that field is properly filled?
I'm playing around with a test website and I tried leaving blank a "Not null" field but the record was created anyway with that field empty.
Maybe I'm not understanding the Null vs Notnull stuff correctly?
If so, how would one restrict the creation of new records unless a not null field contains valid (or data at all) data?

View Replies !

---

What Should Be NULL Is Zero
I am collecting user feedback via an HTML form and sending it to a DB via a PHP form handler. My problem is that values that should be NULL are showing up in the DB as zero.

When a user skips a question, I want the value for that field to be NULL. In the form, I set the variables used to capture the user's choices to default to NULL (not enclosed in quotes). All the PHP handler does is gather the submitted values and send them off to the database. When I echo the variables from the form handler, they are NULL. In the database, the fields are defined as INT(1) with a NULL default value. But when the values (which should be NULL) are submitted to the DB via the form handler, the resulting fields are zero.

(I have also tried setting the form's default values to "" and '' with the same result.)

What can I do to make these values NULL?

View Replies !

---