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
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
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))
- Original Message -
From: Richard Reina [EMAIL PROTECTED]
To: mysql@lists.mysql.com
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
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 transactions
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 on
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
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
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
FROM
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/
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
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:
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
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
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;
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 be
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
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
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 would
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
- 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 day,
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 )
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
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:
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,
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
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
: select help
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
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 affected (0.18 sec)
mysql insert into t values (1
: 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 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
-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 mod(m,n). To get the records where the 8 bit is set, use
and mod(os.os_id,8) = 0
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,
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:
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 join. However,
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
36 matches
Mail list logo
