Hi Travis,
That query kind of gives me the desired result. However, if is showing
me 1, 18, 11, 23, 3, 2010-11-14 17:18:17 record and not 2, 11, 10, 3, 6,
2010-12-20 22:17:13, which is when they changed teams. Any thoughts ?
Cheers
Neil
On Thu, Feb 3, 2011 at 10:32 PM, Travis Ard wrote:
> So
Something like this might help you find all of the times where your user_id
switched to a different team_id:
select team_id, user_id, min(last_changed)
from
(select home_team_id as team_id, home_user_id as user_id, last_changed
from data
union all
select away_team_id as team_id, away_user_id as us
Thank you very much to all who responded. I ended up using Shawn's solution,
the others seem good as well.
Thanks again. Have a great weekend.
Richard
[EMAIL PROTECTED] wrote:
Try this:
SELECT c_no
, SUM(1) as total_tx
, SUM(if(`date` >= now() - interval 6 month,1,0))
Richard,
>Can someone help me write a query to tell me the customer numbers
>(C_NO) of those who've had more than 4 transactions but none in
>the last 6 months?
Something like this?
SELECT
c_no,
COUNT(c_no) AS cnt
FROM transactions_table
WHERE NOT EXISTS (
SELECT c_no
This should work:
select c_name, count(t1.id) as t_count from customers c
inner join transactions t1 on c.c_no = t1.c_no
left join transactions t2 on c.c_no = t2.c_no and t2.date > '2005-06-05'
where t2.id is null
group by c.c_no
having t_count > 4;
There may be more efficient way of doing this t
Richard Reina wrote:
Can someone help me write a query to tell me the customer numbers (C_NO) of
those who've had more than 4 transactions but none in the last 6 months?
transactions_table
| ID | C_NO |DATE | AMOUT |
| 2901 | 387 | "2003-10-09" | 23.00 |
Obviously my ta
Try this:
SELECT c_no
, SUM(1) as total_tx
, SUM(if(`date` >= now() - interval 6 month,1,0)) as recent_tx
FROM transactions_table
GROUP BY c_no
HAVING total_tx > 4 and recent_tx = 0;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Rhino" <[EMAIL PROTECTED]> wrote o
3.23.54
Thanks.
Rhino <[EMAIL PROTECTED]> wrote:
- Original Message -
From: "Richard Reina"
To:
Sent: Thursday, January 05, 2006 10:29 AM
Subject: SELECT help.
> Can someone help me write a query to tell me the customer numbers (C_NO)
> of those who've had more than 4 transac
- Original Message -
From: "Richard Reina" <[EMAIL PROTECTED]>
To:
Sent: Thursday, January 05, 2006 10:29 AM
Subject: SELECT help.
Can someone help me write a query to tell me the customer numbers (C_NO)
of those who've had more than 4 transactions but none in the last 6
months?
Gran Giddens writes:
>SELECT table1.title, table2.feature FROM table1,
>table2 WHERE (table1.sku = $table2.sku) AND table1.sku
>in ($sku1, $sku2, $sku3) ORDER BY FIELD(table1.sku,
>$sku1, $sku2, $sku3) ASC
...
>How can I run my query to get 3 results and if the
>feature is missing still return the
You want a LEFT JOIN:
SELECT table1.title, table2.feature
FROM table1
LEFT JOIN table2 USING (sku)
WHERE table1.sku in ($sku1, $sku2, $sku3)
ORDER BY FIELD(table1.sku, $sku1, $sku2, $sku3) ASC
I strongly suggest picking up Paul DuBois' "MySQL":
http://www.kitebird.com/mysql-book/
__
On Thu, 2004-07-01 at 10:03, rmck wrote:
> Hi,
>
> I have a table with ip,port and I want to see the top ten Ip's with the most
> entries?
> Ip's can be in db many times...
>
> Not the first distinct 10... Im stuck...
>
> I have tried:
> mysql> select DISTINCT ip from iptable limit 10;
Woops! Forget I said that, you wanted to order by the most occurrences.
Sorry.
SELECT ip, count(*) FROM iptable GROUP BY ip ORDER BY 2 DESC limit 10;
Heh... I should learn to read one of these days...
-Matt
-Original Message-
From: rmck [mailto:[EMAIL PROTECTED]
Sent: Thursday, July
select count(*) as cnt group by ip order by cnt desc limit 10;
rmck wrote:
Hi,
I have a table with ip,port and I want to see the top ten Ip's with the most entries?
Ip's can be in db many times...
Not the first distinct 10... Im stuck...
I have tried:
mysql> select DISTINCT ip from iptable limit
Hey Rob,
You're looking for a group by to allow mysql to aggregate over the IP's:
SELECT ip, count(*) FROM iptable GROUP BY ip ORDER BY ip DESC limit 10;
-Matt
-Original Message-
From: rmck [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 01, 2004 1:03 PM
To: [EMAIL PROTECTED]
Subject: S
Hi Robert,
the criteria for the record_1 and record_15 is that both are in the same
table, but in different records and to find each one it is necessary to
perform a WHERE clause.
Let's I give you the real example:
My problem is while inserting a new record in my table named
"ScanQuantificat
Andre MATOS wrote:
Hi,
Is it possible to create a Select performing a math formula? For example:
First I need to add two values come from the same table but from different
records. The result will be divided from one number got from another
table. Now, the new result will be added with another
Andre,
have a look at JOIN. This can solve your problem.
Thomas Spahni
On Fri, 30 Apr 2004, Andre MATOS wrote:
> Is it possible to create a Select performing a math formula? For example:
>
> First I need to add two values come from the same table but from different
> records. The result will b
Kris Burford <[EMAIL PROTECTED]> wrote:
> hi
>
> wondering whether someone can set me straight on whether it's possible to
> request a set of records from a single table with multiple conditions.
>
> for instance, a "story" table, containing id, title, text, section and
> published_date. what i
- Original Message -
From: "Mike Mapsnac" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, January 23, 2004 11:42 PM
Subject: Select help
> Hello
>
> I want to select from the table sum of logins for each day.
Here's one way to do it.
SELECT SUBSTRING(last_login, 1, 10) AS d
Hi,
> I want to select from the table sum of logins for each day.
Would this help:
mysql> select date_format(your_date_column, "%Y-%m-%d"), count(*)
-> from your_table
-> group by date_format(your_date_column, "%Y-%m-%d");
Take care,
Aleksandar
--
MySQL General Mailing List
For list
Hi
This is working...
Thank You
Zoltan
- Original Message -
From: "Cabanillas Dulanto, Ulises" <[EMAIL PROTECTED]>
To: "MYSQL Lista" <[EMAIL PROTECTED]>
Sent: Friday, July 18, 2003 10:54 PM
Subject: RE: SELECT help
>
Try with :
SELECT tparts.name,
a.name,
b.name,
tmovement.quantity
FROMtmovement inner join tparts on (tmovement.part_id = tparts.Id)
inner join twarehouse a on (
tmovement.incoming_id = a.Id )
Another option is to have another table with the item name and number
using that as a lookup table.
IMHO it all depends on the data if the select is static (or rarely
changes) an enum would be best. If is changes a lot then I would use
another table to store it in.
-Michael
"I protect you,
I'm new, too, so someone correct me if I'm wrong, but...
if you make it an ENUM field in a table you can store it using the
value in the selection, retrieve it as the same value, and still get
all the advantages of numeric storage.
Todd
On Sunday, July 6, 2003, at 02:38 PM, Dan Cox wrote:
He
Jeff Shapiro wrote:
If you want to be a bit more generic you could do something like this:
# store the desired OS ID into a variable
SELECT @desired_id := os_id FROM os_table WHERE os_name = "win nt";
# now find the solutions that match with the os_id
SELECT o.os_id, o.os_name, s.os_code, s.solut
t;> How about:
>>AND os.os_id & 8 = 8
>>
>> Where 8 is the value that you're looking for.
>>
>> -ms
>>
>>
>>
>>
>> -Original Message-
>> From: Michael Shulman [mailto:[EMAIL PROTECTED]
>> Sent: Wedne
>
> -ms
>
>
>
>
> -Original Message-
> From: Michael Shulman [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, April 02, 2003 11:38 AM
> To: 'John Hoskins'
> Cc: '[EMAIL PROTECTED]'
> Subject: RE: select help
>
> No problem. Use
8 AM
To: 'John Hoskins'
Cc: '[EMAIL PROTECTED]'
Subject: RE: select help
No problem. Use mod(m,n). To get the records where the "8" bit is set, use
and mod(os.os_id,8) = 0;
mysql> use test
Database changed
mysql> create table t (i integer);
Query OK, 0 rows a
gt; select * from t where mod(i,4) = 0;
+--+
| i|
+--+
|4 |
|8 |
+--+
2 rows in set (0.00 sec)
-Original Message-
From: John Hoskins [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 02, 2003 11:34 AM
To: Michael Shulman
Cc: [EMAIL PROTECTED]
Subject: RE: select hel
Not quite that simple, Plese read the last of the original post. I need
all solutions that have the 4th bit on, so 8,15,24,31...255 all have the
4th bit in combination with other bits.
On Wed, 2 Apr 2003, Michael Shulman wrote:
> mysql> select solution
> -> from os_table os, solutions_table
mysql> select solution
-> from os_table os, solutions_table solutions
-> where os.os_id = solutions.os_code
-> and os.os_id = 8;
-ms
-Original Message-
From: John Hoskins [mailto:[EMAIL PROTECTED]
Sent: Wednesday, April 02, 2003 10:41 AM
To: [EMAIL PROTECTED]
Subject: select
On 26-Feb-2003 John Hoskins wrote:
>
> Probably a simple query but, I need to find select a field with data that
> exists in one table but does not exist in a field in another table.
>
> example:
>
> table1.name table2.name
> ---
> bob
I believe you could use CONCAT(firstname, ' ', lastname) LIKE
'%$searchname%'
if you leave the space out of the middle it probably won't work, and you
might want to trim firstname & lastname to make sure no leading or trailing
spaces exist.
Hope this helps,
P
-Original Message-
From: *H
>Hi,
>
>This is my first posting, although i've been signed up to the list for a
>while.
>
>My problem is this.
>
>Table A (5000 rows)
>ID, NAME, SCORE
>
>Table B (1000 rows)
>ID, NAME, SCORE
>
>I want all records from Table A and those from Table B where they match, for
>this i'm using a right jo
Sir, haven't you posted this before? It looks familiar.
You can't apply an aggregate function to an entire table if the
SELECT statement has a GROUP BY clause. The aggregate function will
return totals for the groups, not for the entire table.
Bob Hall
>Can someone help me combine this statem
This problem is not necessary a mysql problem, it might be a problem with
your server or whatever you're using to access your database.
For example, if you're using PHP to access your database, you should ask
the PHP mailinglist :)
(if you are using PHP btw, you migth not be closing your connect
37 matches
Mail list logo
