RE: sql help: delete row where only related to one other row

[roger.maynard]

Take a look at 

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.ht
ml

CASCADE: Delete or update the row from the parent table and
automatically delete or update the matching rows in the child table.
Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two
tables, you should not define several ON UPDATE CASCADE clauses that act
on the same column in the parent table or in the child table.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

Sent: 20 February 2008 12:44
To: mysql@lists.mysql.com
Subject: sql help: delete row where only related to one other row

Say I have two tables:

table_a
--
a_id (primary key)
b_id

table_b
--
b_id (primary key)
name

there is a one to many mapping between rows in table b and rows in
table a.

Say I had an Id of a row in table a an (a_id, say 5).  Now, what I
want to do is delete the row in table_a (easy enough), but I also
want to delete the related row in table_b, if it is ONLY related to
the a_id of 5.

In other words, I want to delete the row from table b, but I don't
want to delete a row from table b that is in use by another row in
table a.

I'm thinking some type of subquery could do this, but I'm not sure.

Can some one tell me how to do this?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL help/problem with timestamped data differences

[Dan Buettner]

Mark, is the 'secs' column the offset from the minimum value of the
timestamp column?

If so, you might try something like this:

SELECT UNIX_TIMESTAMP(MIN(timestamp)) INTO @min_timestamp FROM my_table;

SELECT uid, timestamp,
UNIX_TIMESTAMP(timestamp) - @min_timestamp AS secs
FROM my_table
ORDER BY 1, 2, 3;

HTH,
Dan


On Jan 8, 2008 7:17 PM, mark carson [EMAIL PROTECTED] wrote:

 Hi All

 I have the following data example
 UID   Timestamp
 123456 20071201 12:00:01
 123456 20071201 12:00:06
 987654 20071201 12:00:01
 987654 20071201 12:00:09
 etc

 I need :
 UID   Timestamp secs
 123456 20071201 12:00:01  
 123456 20071201 12:00:06  0005
 987654 20071201 12:00:01  
 987654 20071201 12:00:09  0008

 or similar solution. I am using version 5.0 and willing to use interim
 tables or any SQL based technique.

 Thanks in advance

 Mark

 --
 Mark Carson
 Managing
 Integrated  Product Intelligence CC
 EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED]
 snailmail : P.O. Box 36095 Menlo Park 0102, South Africa
 Cell : +27 83 260 8515


 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION
 intended
 only for use of the addressee. If you are not the addressee, or the person
 responsible for delivering it to the person addressed, you may not copy or
 deliver this to anyone else. If you received this e-mail by mistake,
 please
 do not make use of it, nor disclose it's contents to anyone. Thank you for
 notifying us immediately by return e-mail or telephone. INFORMATION
 PROVIDED
 IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY
 REPRESENTATION
 OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT
 LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR
 A
 PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY
 AND
 THE USE OF THIS DOCUMENT.


 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL help for qty Sold YTD...

[Rhino]

I spent several minutes looking at your question and your data model and
nothing jumped out at me that precluded you from determining the quantity of
the this vendor's items sold via this data model. I might have missed
something though.

I'm a little concerned that your LineItem table appeared to have no primary
key. In my opinion, the primary key of a line item table should be an order
number (saleTranID?) and then a sequence number (1 for the first item on the
order, 2 for the second, etc.) but you (apparently) have no primary key
defined at all and don't have a sequence number either. However, that
shouldn't keep this particular query from running or returning appropriate
rows.

I am also assuming that invID is an inventory ID - my brain kept reading it
as invoice ID but I learned to ignore it ;-) - where an inventory ID
uniquely identifies one product that you sell, e.g. invID 1 might be power
supplies for Sony camcorders while invID 2 might be Palm Tungsten E PDAs.
This is something I would normally call a product ID if I were doing the
naming :-) If, in fact, invID *is* an invoice ID, i.e. something that
uniquely identifies a particular sales transaction then there is something
wrong which might explain why you're not getting any data.

So, assuming I haven't misunderstood anything or simply missed something, I
would be inclined to break the query down into chunks. Execute each chunk on
its own andmake sure that each chunk delivers what you think it should. If
it doesn't, either the query is wrong or the data isn't what you think it
is. Verify that the data you expect is there by doing SELECTs against the
relevant tables; if the data is there, it's got to be your query that is
wrong. Inspect each chunk until you find the culprit(s) in either the SQL or
the data.

Also, for what it's worth, I would strongly suggest that you set up a test
environment with a SMALL quantity of data in each table - 50 rows or less
should be plenty for most situations - and try your queries against that
test environment. That makes the testing process a lot less painful - why
wait for many seconds or even minutes for the query to give you the wrong
answer? - and let's you solve the problem faster. It might sound like a lot
of work but it shouldn't be; just clone the real tables and then copy a
small but representative sample of data from the real tables into the
clones.

You also asked about performance but there is no way anyone can comment on
that without knowing a lot more about what indexes you have and, perhaps,
which engine you are using. But, in my opinion, your first effort should be
directed toward getting the query running correctly, THEN worry about making
it go faster.

Rhino

- Original Message - 
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, November 04, 2005 12:28 AM
Subject: SQL help for qty Sold YTD...


 I cannot figure this one out. I have a Category table with 50,000
 records, an Inventory table with over 2 million records. A Sales table
 with 500,000 records. And a LineItem table with 800,000 records pairing
 the Inventory ID with the Sales Transaction ID and Quantity. I need to
 generate a Quantity sold year to date for a certain vendor. The vendor
 code can be found in the Category table which has a relationship with
 Inventory. I am trying a SQL statement like this:

 select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where
 (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID
 IN (select invID from Inventory where categoryid IN (select categoryid
 from Category where vendcode='AA'))

 this yields null when I know there are sales for that vendor in 2005.
 Simplified schemas for the tables are as follows:
 Category:

++--+--+-+-+
+
 | Field  | Type | Null | Key | Default | Extra
|

++--+--+-+-+
+
 | vendcode   | char(3)  | YES  | MUL | NULL|
|
 | categoryID | int(10) unsigned |  | PRI | NULL|
 auto_increment |

++--+--+-+-+
+

 Inventory:
 +--+---+--+-+-+---+
 | Field| Type  | Null | Key | Default | Extra |
 +--+---+--+-+-+---+
 | categoryID   | int(11)   | YES  | MUL | NULL|   |
 | invID| int(10)   |  | PRI | 0   |   |
 | itemnum  | int(11)   | YES  | MUL | NULL|   |
 +--+---+--+-+-+---+

 Sales:

+--+--+--+-+-++
 | Field| Type | Null | Key | Default | Extra
  |

+--+--+--+-+-++
 | saletranID   | int(10) unsigned |  | PRI | NULL|
 auto_increment |
 | 

Re: SQL help for qty Sold YTD...

[SGreen]

I would first try refactoring your SQL to use INNER JOIN statements 
instead of the comma separated lists you are currently using. I would also 
not use any subqueries. Test this and see if it works for you:

SELECT SUM(li.quantity) as qtysoldytd 
FROM LineItem li
INNER JOIN Sales sa 
on li.saletranID=sa.saletranID 
and YEAR(sa.solddate)=2005
INNER JOIN Inventory inv
on inv.invID = li.invID
INNER JOIN Category cat
on cat.categoryid = inv.categoryid
AND cat.vendcode='AA';


The linkages work like this:
1) LineItem links into Sales through saletranID and YEAR(solddate)
2) Sales links into Inventory through the invID
3) Inventory links to Category through categoryid and vendcode

Because I used INNER JOINs, each link in the chain must exist across all 
tables or the row cannot be added to the final results. 

Because this query contains several joins and your table sizes are not 
insignificant it becomes a candidate for what I call piecewize 
evaluation. Piecewize evaluation is where you take the full query and 
build your desired results in stages. One stage that jumps out at me is 
the conversion of vendcode to a list of invID values. Another stage could 
be isolating just those line items for 2005. I suggest this because 
JOINing two tables (either by explicit declaration as I do or by 
comma-separated lists as you did) is a geometrically expensive operation 
(it's cost to compute grows by multiplying how many rows are participating 
from each table). If we start with two tables M and N and they each have m 
and n rows in them, a JOIN operation takes on the order of m*n cycles to 
compute.  If we can somehow shrink each table participating in the JOIN 
(by pre-selecting certain rows) so that we now have m/4 and n/2 rows to 
JOIN that reduces your overall cost to (m * n)/8. When we are discussing 
products of m*n on the order of 100 million rows or so, reducing 
production time by a factor of 8 is noticable. The situation is even more 
apparent if you add more tables.

Consider if you had tables A, B, and C and they had a,b, and c rows in 
them. If you had to JOIN those three tables to build a query it would take 
a*b*c units of time to complete. If we were only able to reduce each table 
by 10%, that reduces the overall computation to (.9*a)*(.9*b)*(.9*c) = 
.729(abc)

If:
a =  50,000
b = 500,000
c = 800,000 records

The original execution cost is proportional to:
(5 * 50 * 80) = 2 (2.0e16)
after 10% reductions through precomputations:
 2.0e16 * .729 = 1.458e16
---
# of rows combinations NOT fed through the CPU 
to be evaluated as being in the result or not:
2.0e16 - 1.458e16 = 5.42e+15 = 5420

How long do you think it takes even a modern computer to do 
5420 tests? It can make a serious difference.

Piecewize evaluation works VERY WELL in stored procedures (if you are on 
v5.0 or higher) because you can parameterize your queries quite easily and 
you are assured of executing the same query pattern every time you need 
it.

## stage 1 - identifying Line items from 2005

CREATE TEMPORARY TABLE tmpLI (
KEY(invID)
) SELECT li.invID, li.quantity
FROM LineItem li 
INNER JOIN Sales sa 
on li.saletranID=sa.saletranID 
and YEAR(sa.solddate)=2005

## stage 2 - identifying Inventory Items for a certain category
CREATE TEMPORARY TABLE tmpInv (
KEY(invID)
) SELECT DISTINCT invID
FROM Inventory inv
on inv.invID = li.invID
INNER JOIN Category cat
on cat.categoryid = inv.categoryid
AND cat.vendcode='AA';

## stage 3 - compute your desired results
SELECT SUM(li.quantity)
FROM tmpLI li
INNER JOIN tmpInf inv
ON inv.invID = li.invID;

## stage 4 - the database is not your momma. Clean up after yourself...

DROP TEMPORARY TABLE tmpLi;
DROP TEMPORARY TABLE tmpInv;

## end query

I hope that helps (HTH),

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine








[EMAIL PROTECTED] wrote on 11/04/2005 12:28:50 AM:

 I cannot figure this one out. I have a Category table with 50,000
 records, an Inventory table with over 2 million records. A Sales table
 with 500,000 records. And a LineItem table with 800,000 records pairing
 the Inventory ID with the Sales Transaction ID and Quantity. I need to
 generate a Quantity sold year to date for a certain vendor. The vendor
 code can be found in the Category table which has a relationship with
 Inventory. I am trying a SQL statement like this:
 
 select sum(li.quantity) as qtysoldytd from LineItem li, Sales sa where
 (li.saletranID=sa.saletranID and YEAR(sa.solddate)=2005) and li.InvID
 IN (select invID from Inventory where categoryid IN (select categoryid
 from Category where vendcode='AA')) 
 
 this yields null when I know there are sales for that vendor in 2005.
 Simplified schemas for the tables are as follows:
 Category:
 

Re: SQL help: Updating Strange Chrs.

[Daniel Kasak]

m i l e s wrote:


Hi,

I have an odd situation where I was handed just bad data, and while I  
have cleaned it up to the best of my ability one hurdle remains.


I have a situation where I have the following example in a field:  
Canna ÒBengalÒ.


Note the odd chrs Ò in the field ?  I need to get rid of those, my  
question is HOW.


And I have thousands of rows like this and its just ONE field that's  
the stumbling block.


Any suggestions ?

M i l e s.


replace()

eg:

update some_table set some_field = replace( my_field, Ò, O );

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: [EMAIL PROTECTED]
website: http://www.nusconsulting.com.au

RE: SQL help

[Jay Blanchard]

[snip]
Unfortunately the item field has got data with quotes around it. IE
KP-00310.  I
need to clean up the data and have the field contain just KP-00310.
Since I
have
10,000 records, I need a update statement or something to clean that up.

Any ideas ?
[/snip]

http://dev.mysql.com/doc/mysql/en/string-functions.html the manual is
your friend

UPDATE item SET item = REPLACE(item, '', '')

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL help

[Peter Brawley]

Darryl,

Unfortunately the item field has got data with quotes around it.
IE
"KP-00310". I need to clean up the data and have the field contain 
just KP-00310. Since I have 10,000 records, I need a update 
statement or something to clean that up.

To remove all double quotes: UPDATE tablename SET
item=REPLACE(item,'"','');

PB

-


Darryl Hoar wrote:

  Greetings,
I have a table in my database called item.  It has two fields: item and
description.

Unfortunately the item field has got data with quotes around it. IE
"KP-00310".  I
need to clean up the data and have the field contain just KP-00310.  Since I
have
10,000 records, I need a update statement or something to clean that up.

Any ideas ?

thanks,
Darryl



  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.14/79 - Release Date: 8/22/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL help

[Jochem van Dieten]

On 6/26/05, 2wsxdr5 wrote:
 Can someone tell me why this query works...
 SELECT UserKey
 FROM(
   SELECT UserKey, Count(GiftKey) Gifts
   FROM Gift
   Group BY UserKey
 ) GC
 WHERE GC.Gifts = 3

Why this construction and not simply:
SELECT UserKey
FROM Gift
GROUP BY UserKey
HAVING Count(GiftKey) = 3


 And this one doesn't?
 
 SELECT UserKey, UserID,
 FROM User
 WHERE UserKey IN
 (SELECT UserKey
   FROM(
 SELECT UserKey, Count(GiftKey) Gifts
 FROM Gift
 Group BY UserKey
   ) GC
   WHERE GC.Gifts = 3
 )

How do you mean doesn't work? Does it give an unexpected result or an error?


 User  info about the users   UserKey is the key
 Gift   list of gifts each user has on their wish list  foreign key
 is UserKey
 Event  ---gift giving events for users.   foreign key is UserKey
 Emails  email addresses users have sent a message to about their
 wish list. UserKey is the foreign key here too.
 
 The relationship between user and the other 3 tables is a 1 to many.  I
 have the following query that I need to adjust some.
 
 SELECT u.UserKey, UserID,
 Count(distinct g.GiftKey) gifts, Count(distinct ev.EventKey) events,
 Count(distinct e.Email) Emails
 FROM User u NATURAL LEFT JOIN Gift g
 LEFT JOIN Emails e ON e.Userkey = u.UserKey
 LEFT JOIN GiftGivingEvent ev ON ev.UserKey = u.UserKey
 GROUP BY UserID
 
 What I really want is only the users where the gifts count is  3, the
 Event count is  1, the Emails count  is  5 and and only count emails
 if e.Verified is = 1

I think you mean the following:

SELECT *
FROM User INNER JOIN (
  SELECT UserKey, Count(UserKey) AS gifts
  FROM Gift
  GROUP BY UserKey
  HAVING Count(UserKey)  3
) USING (Userkey)
INNER JOIN (
  SELECT UserKey, Count(UserKey) AS events
  FROM Event
  GROUP BY UserKey
  HAVING Count(UserKey)  1
) USING (Userkey)
INNER JOIN (
  SELECT UserKey, Count(UserKey) AS emails
  FROM Emails
  WHERE Verified = 1
  GROUP BY UserKey
  HAVING Count(UserKey)  5
) USING (Userkey)

Jochem

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL help

[Rob Brooks]

correction  in question below, the problem is not in record '7047' but in
the record which starts with the name 'Triad'

also ... I'm using 4.0.20-standard-log

-Original Message-
From: Rob Brooks [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 28, 2005 2:56 PM
To: mysql@lists.mysql.com
Subject: SQL help

Can someone help me with this?

this statement:

select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left
join items_online on items_online.ItemKey = Items.ID where (Name regexp
'ad') and AccountKey = 108 and Items.Active = 1;

gives this:


+---+---


-+--+--+
| Name  | Detail
| ID   | ID   |
+---+---


-+--+--+
| Jade Arch Series  | 3/4 thick beveled jade acrylic arch
on a beveled jade acrylic base
| 7015 |  437 |
| Jade Arrow Series | 3/4 thick beveled jade acrylic arrow
on beveled jade acrylic base.
| 7016 |  438 |
| Queen Jade| Unique and elegant shape makes a
beautiful free standing award.  Glass is jade color.
| 7041 |  463 |
| Octavia Jade  | Beautiful jade glass octagon shaped
award.
| 7043 |  465 |
| Jade Arresting Obelisk| Pristine, monumental style jade glass
award.
| 7045 |  467 |
| Jade Autumn Leaf  | Contemporary design derived from
natural shape.  Cut from jade glass.
| 7047 |  469 |
| Triad | Free standing clear acrylic with a
thick triangular shape.
| 7069 | NULL |
| Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue,
black, or light velour backgrounds.  Retro style clock with new style design
that allows it to hang in vertical or horizontal position.  Large engravable
black brass plate.  Lifetime Guaranteed Quartz Movement | 7073 |  494 |
| Traditional Wood and Glass Clock  | Traditional clock with polished brass
and cherry wood finished accents.  Polished glass upright holds the clock.
Black brass engravable plate.  Lifetime Guaranteed Quartz Movement
| 7087 |  508 |

  ... truncated for brevity  
+---+---


-+--+--+
14 rows in set (0.06 sec)

 
but this statement:

select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left
join items_online on items_online.ItemKey = Items.ID where items_online.ID =
NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1;


gives this:

Empty set (0.00 sec)

The only difference in the 2 statements is the 'where items_online.ID =
NULL' part.
Clearly in the first set, items_online.ID = NULL in record 7047 but when I
look for it specifically, it is not found??


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL help

[Michael Dykman]

Properly, NULL values should be matched with 'foo IS NULL', as opposed
to 'foo = NULL' which, by standard definition, always returns false
regardless of the value of foo

 - michael dykman

On Mon, 2005-02-28 at 16:02, Rob Brooks wrote:
 correction  in question below, the problem is not in record '7047' but in
 the record which starts with the name 'Triad'
 
 also ... I'm using 4.0.20-standard-log
 
 -Original Message-
 From: Rob Brooks [mailto:[EMAIL PROTECTED] 
 Sent: Monday, February 28, 2005 2:56 PM
 To: mysql@lists.mysql.com
 Subject: SQL help
 
 Can someone help me with this?
 
 this statement:
 
 select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left
 join items_online on items_online.ItemKey = Items.ID where (Name regexp
 'ad') and AccountKey = 108 and Items.Active = 1;
 
 gives this:
 
 
 +---+---
 
 
 -+--+--+
 | Name  | Detail
 | ID   | ID   |
 +---+---
 
 
 -+--+--+
 | Jade Arch Series  | 3/4 thick beveled jade acrylic arch
 on a beveled jade acrylic base
 | 7015 |  437 |
 | Jade Arrow Series | 3/4 thick beveled jade acrylic arrow
 on beveled jade acrylic base.
 | 7016 |  438 |
 | Queen Jade| Unique and elegant shape makes a
 beautiful free standing award.  Glass is jade color.
 | 7041 |  463 |
 | Octavia Jade  | Beautiful jade glass octagon shaped
 award.
 | 7043 |  465 |
 | Jade Arresting Obelisk| Pristine, monumental style jade glass
 award.
 | 7045 |  467 |
 | Jade Autumn Leaf  | Contemporary design derived from
 natural shape.  Cut from jade glass.
 | 7047 |  469 |
 | Triad | Free standing clear acrylic with a
 thick triangular shape.
 | 7069 | NULL |
 | Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue,
 black, or light velour backgrounds.  Retro style clock with new style design
 that allows it to hang in vertical or horizontal position.  Large engravable
 black brass plate.  Lifetime Guaranteed Quartz Movement | 7073 |  494 |
 | Traditional Wood and Glass Clock  | Traditional clock with polished brass
 and cherry wood finished accents.  Polished glass upright holds the clock.
 Black brass engravable plate.  Lifetime Guaranteed Quartz Movement
 | 7087 |  508 |
 
   ... truncated for brevity  
 +---+---
 
 
 -+--+--+
 14 rows in set (0.06 sec)
 
  
 but this statement:
 
 select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left
 join items_online on items_online.ItemKey = Items.ID where items_online.ID =
 NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1;
 
 
 gives this:
 
 Empty set (0.00 sec)
 
 The only difference in the 2 statements is the 'where items_online.ID =
 NULL' part.
 Clearly in the first set, items_online.ID = NULL in record 7047 but when I
 look for it specifically, it is not found??
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]
-- 
 - michael dykman
 - [EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL help

[Michael Stassen]

NULL is an unknown value.  Consequently, you cannot compare NULLs the way 
you expect.  Effectively, = NULL is always false.  Instead of

  items_online.ID = NULL
you have to use
  items_online.ID IS NULL
Michael
Rob Brooks wrote:
correction  in question below, the problem is not in record '7047' but in
the record which starts with the name 'Triad'
also ... I'm using 4.0.20-standard-log
-Original Message-
From: Rob Brooks [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 28, 2005 2:56 PM
To: mysql@lists.mysql.com
Subject: SQL help

Can someone help me with this?
this statement:
select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left
join items_online on items_online.ItemKey = Items.ID where (Name regexp
'ad') and AccountKey = 108 and Items.Active = 1;
gives this:
+---+---


-+--+--+
| Name  | Detail
| ID   | ID   |
+---+---


-+--+--+
| Jade Arch Series  | 3/4 thick beveled jade acrylic arch
on a beveled jade acrylic base
| 7015 |  437 |
| Jade Arrow Series | 3/4 thick beveled jade acrylic arrow
on beveled jade acrylic base.
| 7016 |  438 |
| Queen Jade| Unique and elegant shape makes a
beautiful free standing award.  Glass is jade color.
| 7041 |  463 |
| Octavia Jade  | Beautiful jade glass octagon shaped
award.
| 7043 |  465 |
| Jade Arresting Obelisk| Pristine, monumental style jade glass
award.
| 7045 |  467 |
| Jade Autumn Leaf  | Contemporary design derived from
natural shape.  Cut from jade glass.
| 7047 |  469 |
| Triad | Free standing clear acrylic with a
thick triangular shape.
| 7069 | NULL |
| Long Format Shadowbox Style Clock | Walnut frame with red, maroon, blue,
black, or light velour backgrounds.  Retro style clock with new style design
that allows it to hang in vertical or horizontal position.  Large engravable
black brass plate.  Lifetime Guaranteed Quartz Movement | 7073 |  494 |
| Traditional Wood and Glass Clock  | Traditional clock with polished brass
and cherry wood finished accents.  Polished glass upright holds the clock.
Black brass engravable plate.  Lifetime Guaranteed Quartz Movement
| 7087 |  508 |
  ... truncated for brevity  
+---+---


-+--+--+
14 rows in set (0.06 sec)
 
but this statement:

select Items.Name, Items.Detail, Items.ID, items_online.ID from Items left
join items_online on items_online.ItemKey = Items.ID where items_online.ID =
NULL and (Name regexp 'ad') and AccountKey = 108 and Items.Active = 1;
gives this:
Empty set (0.00 sec)
The only difference in the 2 statements is the 'where items_online.ID =
NULL' part.
Clearly in the first set, items_online.ID = NULL in record 7047 but when I
look for it specifically, it is not found??

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL help

[Harald Fuchs]

In article [EMAIL PROTECTED],
Rob Brooks [EMAIL PROTECTED] writes:

 The only difference in the 2 statements is the 'where items_online.ID =
 NULL' part.
 Clearly in the first set, items_online.ID = NULL in record 7047 ...

Nope.  items_online.ID IS NULL for that record, but comparing anything
= NULL ain't true, even for NULL.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL help

[Michael Stassen]

Bob Lockie wrote:
What I really want was
mysql update records set records.prio=2 where records.in=(select 
records.id from records, audit_log, audit_log_records where 
audit_log.tracker_id=audit_log_records.tracker_id and 
records.id=audit_log_records.id and audit_log.operation='D' and 
audit_log.completed is null);
but that gives a syntax error and I don't think I can do a select inside 
an update. :-(
Subqueries require mysql 4.1 or higher.
mysql update records set records.prio=2 where 
audit_log.tracker_id=audit_log_records.tracker_id and 
records.id=audit_log_records.id and audit_log.operation='D' and 
audit_log.completed is null;

ERROR 1109: Unknown table 'audit_log' in where clause
You have to name all the tables you need in the UPDATE clause before you can 
use them in the WHERE clause.  So, you need

  UPDATE records, auditlog, audit_log_records
  SET records.prio=2
  WHERE audit_log.tracker_id=audit_log_records.tracker_id
  AND records.id=audit_log_records.id
  AND audit_log.operation='D'
  AND audit_log.completed is null;
This is a multiple-table update, which is supported starting with mysql 
4.0.4.  Prior to that, you can't do this with one statement.  See the manual 
http://dev.mysql.com/doc/mysql/en/UPDATE.html.

Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL help

[Egor Egorov]

Bob Lockie [EMAIL PROTECTED] wrote:
 What I really want was
 mysql update records set records.prio=2 where records.in=(select 
 records.id from records, audit_log, audit_log_records where 
 audit_log.tracker_id=audit_log_records.tracker_id and 
 records.id=audit_log_records.id and audit_log.operation='D' and 
 audit_log.completed is null);
 but that gives a syntax error and I don't think I can do a select inside 
 an update. :-(
 
 mysql update records set records.prio=2 where 
 audit_log.tracker_id=audit_log_records.tracker_id and 
 records.id=audit_log_records.id and audit_log.operation='D' and 
 audit_log.completed is null;
 
 ERROR 1109: Unknown table 'audit_log' in where clause
 

You must specify 'audit_log' and 'audit_log_records' tables too. 

UPDATE records, audit_log, audit_log_records
SET records.prio=2
WHERE ..



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL-help needed

[Ligaya Turmelle]

I'm still a beginner myself but try something like

SELECT COUNT(YEAR), WINNER1 AS WINNER, WINNER2 AS WINNER, YEAR FROM
CHAMPIONS WHERE CLASS = hd GROUP BY WINNER;

I think this will give you something like:

COUNT(YEAR) WINNER  YEAR
2   carl1957
2   carl1985
1   mattias 1957
1   erik1985

Again I am a beginner and would have to test this to see if it actually
gives me the right info or if I would have to tweek it.

Respectfully,
Ligaya Turmelle
Computer Programmer
Guam International Country Club
495 Battulo Street
Dededo, Guam 96912
Tel: (671) 632-4445
Fax: (671) 632-4440
Reservations: (671) 632-4422 (GICC)

-Original Message-
From: Carl Schéle, IT, Posten [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 19, 2004 6:50 PM
To: [EMAIL PROTECTED]
Subject: SQL-help needed


Hi!



I got a table, champions, looking like this:



idclass winner_1  winner_2 year

-

0  hd carl  mattias  1957

1  hs daniel 1982

2  hd erik  carl 1985

3  js erik   1974



Imagine I want to see how many times each winner appears where class=hd and
which year. In this case the answer would be:



2 carl 1957,1985

1 mattias 1957

1 erik 1985



Please help! Still using old MySQL 3.23.58.








-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL-help needed

[Michael Stassen]

Carl Schéle, IT, Posten wrote:
Hi!

I got a table, champions, looking like this:

idclass winner_1  winner_2 year
-
0  hd carl  mattias  1957
1  hs daniel 1982
2  hd erik  carl 1985
3  js erik   1974
Imagine I want to see how many times each winner appears where
class=hd and which year. In this case the answer would be:
2 carl 1957,1985

1 mattias 1957

1 erik 1985

Please help! Still using old MySQL 3.23.58.
The following is close to what you want:

CREATE TEMPORARY TABLE champs (winner CHAR(10), year INT);
# change the column types to match table champions
INSERT INTO champs SELECT winner_1, year
FROM champions WHERE class='hd';
INSERT INTO champs SELECT winner_2, year
FROM champions WHERE class='hd' AND winner_2 IS NOT NULL;
# some of your winner_2 spots are empty.  If they're empty strings
# instead of NULL, change IS NOT NULL to != ''
SELECT * FROM champs ORDER by winner, year;
+-+--+
| winner  | year |
+-+--+
| carl| 1957 |
| carl| 1985 |
| erik| 1985 |
| mattias | 1957 |
+-+--+
4 rows in set (0.01 sec)
SELECT count(*), winner FROM champs GROUP BY winner;
+--+-+
| count(*) | winner  |
+--+-+
|2 | carl|
|1 | erik|
|1 | mattias |
+--+-+
3 rows in set (0.01 sec)
===

It seems to me that your table design is what makes this difficult.  If 
you changed it to something like the following, where wintype denotes 
winner_1 or winner_2, this would be easier:

 id  class  winner   wintype  year
 -
  1  hd carl   1  1957
  2  hs daniel 1  1982
  3  hd erik   1  1985
  4  js erik   1  1974
  5  hd mattias2  1957
  6  hd carl   2  1985
You could then go straight to the select:

  SELECT * FROM champions WHERE class='hd' ORDER by winner, year;

or

  SELECT count(*), winner FROM champions
  WHERE class='hd' GROUP BY winner;
You could use a variant of the INSERT-SELECTs above to fill the new 
table, if you decide to go that way.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL-HELP

[Jonas Lindén]

Hello, you might want to try select DISTINCT ?

http://www.mysqlfreaks.com/statements/18.php

/Jonas

- Original Message - 
From: Carl Schéle, IT, Posten [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, February 17, 2004 3:49 PM
Subject: SQL-HELP


Hello!

I got a table, champions, looking like this:



id   winner_1 winner_2



0carl mattias

1daniel carl

2erik daniel

3erik johan



What I want is to retrieve the unique names ie:



carl

mattias

daniel

erik

johan



I use MySQL 3.23.58 (which means I can't use sub-selects).



/Carl





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL-HELP

[Jochem van Dieten]

Carl Schéle, IT, Posten wrote:
I got a table, champions, looking like this:

id   winner_1 winner_2

0carl mattias
1daniel carl
2erik daniel
3erik johan
What I want is to retrieve the unique names ie:

carl
mattias
daniel
erik
johan
I use MySQL 3.23.58 (which means I can't use sub-selects).
The smart way: get a database that understands UNION.

The other way:
SELECT DISTINCT
CASE
WHEN c1.id = c1.id THEN c1.winner_1
ELSE c1.winner_2
END AS winner
FROM
champions c1,
champions c2
Jochem

--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help

[Roger Baklund]

* sulewski
[...]
 What I need is all records in table 1 that will
 not link to table 2 such that relid=rid and vid=46

Sounds like a job for LEFT JOIN...?

Join to the rows you do NOT want with a left join, and put as a condition in
the WHERE clause that a joined column IS NULL. Something like this:

SELECT tab1.*
  FROM tab1
  LEFT JOIN tab2 ON
tab2.rid=tab1.id AND
tab2.vid=46
  WHERE
tab2.rid IS NULL

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help

[sulewski]

Roger,

Thank you for the feedback. But unfortunately this doesn't work. The 
problem is that rid will never be null. I'm trying to find the item in 
tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and 
tab2.vid=46 because there is no record in tab2. Not that the record may 
have null values. I did try what you said and it didn't work.

But thanks,

Joe

On Friday, January 16, 2004, at 11:49  AM, Roger Baklund wrote:

* sulewski
[...]
What I need is all records in table 1 that will
not link to table 2 such that relid=rid and vid=46
Sounds like a job for LEFT JOIN...?

Join to the rows you do NOT want with a left join, and put as a 
condition in
the WHERE clause that a joined column IS NULL. Something like this:

SELECT tab1.*
  FROM tab1
  LEFT JOIN tab2 ON
tab2.rid=tab1.id AND
tab2.vid=46
  WHERE
tab2.rid IS NULL
--
Roger


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help

[sulewski]

Roger,

In regards to my last e-mail what would be great is if I can get all 
the records in tab1 then subtract from there all the records that match 
the query tab1.id=tab2.rid and tab2.vid=46. The result would give me 
what I need but alas mysql doesn't support minus.

Joe

On Friday, January 16, 2004, at 11:49  AM, Roger Baklund wrote:

* sulewski
[...]
What I need is all records in table 1 that will
not link to table 2 such that relid=rid and vid=46
Sounds like a job for LEFT JOIN...?

Join to the rows you do NOT want with a left join, and put as a 
condition in
the WHERE clause that a joined column IS NULL. Something like this:

SELECT tab1.*
  FROM tab1
  LEFT JOIN tab2 ON
tab2.rid=tab1.id AND
tab2.vid=46
  WHERE
tab2.rid IS NULL
--
Roger


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help

[gerald_clark]

That is the whole point of a left join.
It joins to a null record when the appropriate right record does not exist.
sulewski wrote:

Roger,

Thank you for the feedback. But unfortunately this doesn't work. The 
problem is that rid will never be null. I'm trying to find the item in 
tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id and 
tab2.vid=46 because there is no record in tab2. Not that the record 
may have null values. I did try what you said and it didn't work.

But thanks,

Joe

On Friday, January 16, 2004, at 11:49  AM, Roger Baklund wrote:

* sulewski
[...]
What I need is all records in table 1 that will
not link to table 2 such that relid=rid and vid=46


Sounds like a job for LEFT JOIN...?

Join to the rows you do NOT want with a left join, and put as a 
condition in
the WHERE clause that a joined column IS NULL. Something like this:

SELECT tab1.*
  FROM tab1
  LEFT JOIN tab2 ON
tab2.rid=tab1.id AND
tab2.vid=46
  WHERE
tab2.rid IS NULL
--
Roger




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help

[sulewski]

Gerald,

Your right. You and Roger hit it on the head. Stupid me miss read 
Roger's original post.

Last night I was banging my head on the left and right joins but I 
didn't understand it until I read Gerald's last note. Plus I didn't 
realize you can put two conditions in the ON clause which is why I 
didn't get Roger's post.

Thank you very much guys. You saved the day.

Joe

On Friday, January 16, 2004, at 12:31  PM, gerald_clark wrote:

That is the whole point of a left join.
It joins to a null record when the appropriate right record does not 
exist.

sulewski wrote:

Roger,

Thank you for the feedback. But unfortunately this doesn't work. The 
problem is that rid will never be null. I'm trying to find the item 
in tab1 where a link cannot be created in tab2 where tab2.rid=tab1.id 
and tab2.vid=46 because there is no record in tab2. Not that the 
record may have null values. I did try what you said and it didn't 
work.

But thanks,

Joe

On Friday, January 16, 2004, at 11:49  AM, Roger Baklund wrote:

* sulewski
[...]
What I need is all records in table 1 that will
not link to table 2 such that relid=rid and vid=46


Sounds like a job for LEFT JOIN...?

Join to the rows you do NOT want with a left join, and put as a 
condition in
the WHERE clause that a joined column IS NULL. Something like this:

SELECT tab1.*
  FROM tab1
  LEFT JOIN tab2 ON
tab2.rid=tab1.id AND
tab2.vid=46
  WHERE
tab2.rid IS NULL
--
Roger




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help...

[Info]

Estoy tomando el sol
.
q

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help...

[Info]

Estoy tomando el sol
.
q

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Help...

[Dathan Vance Pattishall]

---Original Message-
--From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
--Sent: Thursday, July 24, 2003 12:53 PM
--To: [EMAIL PROTECTED]
--Subject: SQL Help...
--
--After some searching around different books/manuals/google I still
can't
--seem to figure out how do to this. What I have is a table with 4 cols
--(task, resource, department, priority) and what I want to do is be
able
--to
--select distinct resources and list what their highest priority is.
--In other words, if a resource is in a high priority task and a low
--priority task, I only want to show the high priority task.

Take a look at GROUP BY HAVING and Count(*) at mysql.com
Something like
SELECT resource, task, priority GROUP BY priority, task having priority
 what you define as task

I have no idea what your column types are and what data is contained so
please excuse this guess.



--
--Thanks for the help!
---Nick
--

--MySQL General Mailing List
--For list archives: http://lists.mysql.com/mysql
--To unsubscribe:
--http://lists.mysql.com/[EMAIL PROTECTED]




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help...

[mos]

At 02:52 PM 7/24/2003, you wrote:
After some searching around different books/manuals/google I still can't
seem to figure out how do to this. What I have is a table with 4 cols
(task, resource, department, priority) and what I want to do is be able to
select distinct resources and list what their highest priority is.
In other words, if a resource is in a high priority task and a low
priority task, I only want to show the high priority task.
Thanks for the help!
-Nick
Nick,
You mean something like this:
select resource, Max(Concat(priority, '=', Resource)) ResourcePriority 
group by Resource

This will work with priority 1 through 9.

Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Help...

[nick]

I looked at the group by option already and I dont think it will do what I
need it to do. I say this because it will only group things in the
priority/task/whatever but that still leaves options for duplicate
resources. Yes, it would get rid of the dup. resources per priority, but
not for the entire table.
I really want to group by the resource, but still have all the info
available for what ever the highest priority task it is in.

-Nick

Btw, all the fields are varchars with the priorities being Hi, Medium,
Low, but I could really care less on what particular priority it is atm.


 ---Original Message-
 --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 --Sent: Thursday, July 24, 2003 12:53 PM
 --To: [EMAIL PROTECTED]
 --Subject: SQL Help...
 --
 --After some searching around different books/manuals/google I still
 can't
 --seem to figure out how do to this. What I have is a table with 4 cols
 --(task, resource, department, priority) and what I want to do is be
 able
 --to
 --select distinct resources and list what their highest priority is.
 --In other words, if a resource is in a high priority task and a low
 --priority task, I only want to show the high priority task.

 Take a look at GROUP BY HAVING and Count(*) at mysql.com
 Something like
 SELECT resource, task, priority GROUP BY priority, task having priority
 what you define as task

 I have no idea what your column types are and what data is contained so
 please excuse this guess.



 --
 --Thanks for the help!
 ---Nick
 --
 
 --MySQL General Mailing List
 --For list archives: http://lists.mysql.com/mysql
 --To unsubscribe:
 --http://lists.mysql.com/[EMAIL PROTECTED]




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Help...

[Cabanillas Dulanto, Ulises]

Try:

select task, resource, department, max(priority) 
from table
group by task, resource, department

Regards,
Ulises

-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Enviado el: Jueves 24 de Julio de 2003 02:53 PM
Para: [EMAIL PROTECTED]
Asunto: SQL Help...


After some searching around different books/manuals/google I still can't
seem to figure out how do to this. What I have is a table with 4 cols
(task, resource, department, priority) and what I want to do is be able to
select distinct resources and list what their highest priority is.
In other words, if a resource is in a high priority task and a low
priority task, I only want to show the high priority task.

Thanks for the help!
-Nick

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL Help...

[nick]

That works great. =D
Knew it shouldn't be that difficult, thanks a bunch.
And it actually works with the Priorities being in text form to (low, med,
hi).

-Nick

 At 02:52 PM 7/24/2003, you wrote:
After some searching around different books/manuals/google I still can't
seem to figure out how do to this. What I have is a table with 4 cols
(task, resource, department, priority) and what I want to do is be able
 to
select distinct resources and list what their highest priority is.
In other words, if a resource is in a high priority task and a low
priority task, I only want to show the high priority task.

Thanks for the help!
-Nick

 Nick,
  You mean something like this:

 select resource, Max(Concat(priority, '=', Resource)) ResourcePriority
 group by Resource

 This will work with priority 1 through 9.

 Mike




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Help...

[Lin Yu]

Nick,  As you stated, your priority field datatype is varchar, with possible
values Hi, Medium and Low, as opposed to being integers. The use of max
function, as suggested by some colleagues without knowing exactly the datatype
would work correctly only on columns of datatype integer. In your case, for
textual columns, lexicographic (dictionary) ordering will be used in computing
function max, and Medium would win the competition, instead of Hi -- which
actually has the lowest ranking in the lexicographic ordering. Perhaps you could
use the  CASE WHEN ... constructs to map your textual priority into numeric
(integer) values (e.g., L - 1, M - 2, H - 3), and then apply the max function
to the integer values to get correct results.

Best regards,

Lin
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 24, 2003 4:41 PM
To: Dathan Vance Pattishall
Cc: [EMAIL PROTECTED]
Subject: RE: SQL Help...

I looked at the group by option already and I dont think it will do what I
need it to do. I say this because it will only group things in the
priority/task/whatever but that still leaves options for duplicate
resources. Yes, it would get rid of the dup. resources per priority, but
not for the entire table.
I really want to group by the resource, but still have all the info
available for what ever the highest priority task it is in.

-Nick

Btw, all the fields are varchars with the priorities being Hi, Medium,
Low, but I could really care less on what particular priority it is atm.


 ---Original Message-
 --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 --Sent: Thursday, July 24, 2003 12:53 PM
 --To: [EMAIL PROTECTED]
 --Subject: SQL Help...
 --
 --After some searching around different books/manuals/google I still
 can't
 --seem to figure out how do to this. What I have is a table with 4 cols
 --(task, resource, department, priority) and what I want to do is be
 able
 --to
 --select distinct resources and list what their highest priority is.
 --In other words, if a resource is in a high priority task and a low
 --priority task, I only want to show the high priority task.

 Take a look at GROUP BY HAVING and Count(*) at mysql.com
 Something like
 SELECT resource, task, priority GROUP BY priority, task having priority
 what you define as task

 I have no idea what your column types are and what data is contained so
 please excuse this guess.



 --
 --Thanks for the help!
 ---Nick
 --
 
 --MySQL General Mailing List
 --For list archives: http://lists.mysql.com/mysql
 --To unsubscribe:
 --http://lists.mysql.com/[EMAIL PROTECTED]




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SQL Help...

[nick]

Yep, saw that and did just as you stated :)

 Nick,  As you stated, your priority field datatype is varchar, with
 possible
 values Hi, Medium and Low, as opposed to being integers. The use of
 max
 function, as suggested by some colleagues without knowing exactly the
 datatype
 would work correctly only on columns of datatype integer. In your case,
 for
 textual columns, lexicographic (dictionary) ordering will be used in
 computing
 function max, and Medium would win the competition, instead of Hi --
 which
 actually has the lowest ranking in the lexicographic ordering. Perhaps you
 could
 use the  CASE WHEN ... constructs to map your textual priority into
 numeric
 (integer) values (e.g., L - 1, M - 2, H - 3), and then apply the max
 function
 to the integer values to get correct results.

 Best regards,
 
 Lin
 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 Sent: Thursday, July 24, 2003 4:41 PM
 To: Dathan Vance Pattishall
 Cc: [EMAIL PROTECTED]
 Subject: RE: SQL Help...

 I looked at the group by option already and I dont think it will do what I
 need it to do. I say this because it will only group things in the
 priority/task/whatever but that still leaves options for duplicate
 resources. Yes, it would get rid of the dup. resources per priority, but
 not for the entire table.
 I really want to group by the resource, but still have all the info
 available for what ever the highest priority task it is in.

 -Nick

 Btw, all the fields are varchars with the priorities being Hi, Medium,
 Low, but I could really care less on what particular priority it is atm.


 ---Original Message-
 --From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 --Sent: Thursday, July 24, 2003 12:53 PM
 --To: [EMAIL PROTECTED]
 --Subject: SQL Help...
 --
 --After some searching around different books/manuals/google I still
 can't
 --seem to figure out how do to this. What I have is a table with 4 cols
 --(task, resource, department, priority) and what I want to do is be
 able
 --to
 --select distinct resources and list what their highest priority is.
 --In other words, if a resource is in a high priority task and a low
 --priority task, I only want to show the high priority task.

 Take a look at GROUP BY HAVING and Count(*) at mysql.com
 Something like
 SELECT resource, task, priority GROUP BY priority, task having priority
 what you define as task

 I have no idea what your column types are and what data is contained so
 please excuse this guess.



 --
 --Thanks for the help!
 ---Nick
 --
 
 --MySQL General Mailing List
 --For list archives: http://lists.mysql.com/mysql
 --To unsubscribe:
 --http://lists.mysql.com/[EMAIL PROTECTED]




 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: sql help

[William R. Mussatto]

You want to look at 'group by acctSrv.accountID' rather than a compound 
select. 

On Mon, 23 Dec 2002, Adam Nowalsky wrote:

 Date: Mon, 23 Dec 2002 09:04:32 -0500
 From: Adam Nowalsky [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: sql help
 
 hi, wonder if the sql gurus can help with this one.  i have two tables
 (simplified), tblAccounts and tblAccountsServices.  tblAccounts has an ID
 (PK) and an accountNumber, and tblAccountsServices has an ID (PK) and
 accountID (FK to tblAccounts.ID).  i want to run a query that gives me a row
 for each record in tblAccountsServices that looks like -
 
 tblAccountsServices.ID, tblAccounts.accountNumber, COUNT of
 tblAccountsServices.ID for this tblAccountsServices.accountID
 
 i'm thinking something like -
 
 select acctSrv.ID, a.accountNumber, select COUNT( ID) from
 tblAccountsServices where accountID = ?? as theCount
 from tblAccountsServices acctSrv, tblAccounts a
 where a.ID = acctSrv.accountID
 
 it's the part in quotes i'm having trouble with, if it's even possible.  i
 feel like i've seen something similar posted before.  of course, if i have
 to, i can use the brute force method of looping through the rowset of
 tblAccountsServices, and if it's a new accountID from the previous record,
 then run another query to get the count then...
 
 btw, i'm running mysql 3.23.53-max-nt...
 
 thanks!
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 

Sincerely,

William Mussatto, Senior Systems Engineer
CyberStrategies, Inc
ph. 909-920-9154 ext. 27


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL Help Needed

[Nick Stuart]

Ok this should be easy so I'm prolly going to screw it up, but here goes =D

Your query should be:
SELECT UserID, SUM(points) FROM History WHERE WeekID = 'whatever' GROUP BY
UserIdI think that should do it. Someone yell if its wrong though.

-Nick

 I have 2 tables

 Users (UserID)

 History (UserID, WeekID, Points)

 When a User record is created a record is inserted into History with
 the current WeekID, so for example data could be :

 Users
 
 
 
 
 

 History
 --
  - 1 - 10
  - 1 - 20
  - 1 - 30
  - 2 - 40

 I want to run a query to return one row for each User row and their
 points for any given week.  In other words somebody asks for all points
 in Week 1 the result should be :

  - 10
  - 20
  - 30
  - 0

 Or, all points for WeekID 2 would give :

  - 0
  - 0
  - 0
  - 40


 I have tried lots of things but I think my brain is just not giving me
 the correct solution.

 Please help!



 -
 Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED] To
 unsubscribe, e-mail
 [EMAIL PROTECTED] Trouble
 unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: SQL Help Needed

[Roger Baklund]

* Dave
 I have 2 tables

 Users (UserID)

 History (UserID, WeekID, Points)

 When a User record is created a record is inserted into History with the
 current WeekID, so for example data could be :

 Users
 
 
 
 
 

 History
 --
  - 1 - 10
  - 1 - 20
  - 1 - 30
  - 2 - 40

 I want to run a query to return one row for each User row and their points
 for any given week.  In other words somebody asks for all points in Week 1
 the result should be :

  - 10
  - 20
  - 30
  - 0

 Or, all points for WeekID 2 would give :

  - 0
  - 0
  - 0
  - 40


 I have tried lots of things but I think my brain is just not giving me the
 correct solution.

I don't know what you have tried or why it did not work for you, but
something like this should work:

SELECT u.UserID, IF(h.Points,h.Points,0) AS Points
  FROM Users AS u
  LEFT JOIN History AS h USING(UserID)
  WHERE ISNULL(WeekID) OR WeekID = 1
  ORDER BY u.UserID

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL Help Needed

[Dave]

Thanks for your responses but it's not that much help I need with my SQL ;-)

None of the 3 suggestions  work.

Please look at the examples I gave.  I need *all* UserIDs regardless of
whether they have a record in History that matches both UserID and WeekID.
In other words some UserIDs will only exist for WeekID = 2, other for 1 and
2 and so on.

Cheers
Dave


- Original Message -
From: Roger Baklund [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Dave [EMAIL PROTECTED]
Sent: Thursday, May 23, 2002 6:21 PM
Subject: RE: SQL Help Needed


 * Dave
  I have 2 tables
 
  Users (UserID)
 
  History (UserID, WeekID, Points)
 
  When a User record is created a record is inserted into History with the
  current WeekID, so for example data could be :
 
  Users
  
  
  
  
  
 
  History
  --
   - 1 - 10
   - 1 - 20
   - 1 - 30
   - 2 - 40
 
  I want to run a query to return one row for each User row and their
points
  for any given week.  In other words somebody asks for all points in Week
1
  the result should be :
 
   - 10
   - 20
   - 30
   - 0
 
  Or, all points for WeekID 2 would give :
 
   - 0
   - 0
   - 0
   - 40
 
 
  I have tried lots of things but I think my brain is just not giving me
the
  correct solution.

 I don't know what you have tried or why it did not work for you, but
 something like this should work:

 SELECT u.UserID, IF(h.Points,h.Points,0) AS Points
   FROM Users AS u
   LEFT JOIN History AS h USING(UserID)
   WHERE ISNULL(WeekID) OR WeekID = 1
   ORDER BY u.UserID

 --
 Roger




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: SQL Help Needed

[Roger Baklund]

* Dave
 Thanks for your responses but it's not that much help I need with
 my SQL ;-)

 None of the 3 suggestions  work.

 Please look at the examples I gave.  I need *all* UserIDs regardless of
 whether they have a record in History that matches both UserID and WeekID.

...and that is what I thought my suggestion would do...

  SELECT u.UserID, IF(h.Points,h.Points,0) AS Points
FROM Users AS u
LEFT JOIN History AS h USING(UserID)
WHERE ISNULL(WeekID) OR WeekID = 1
ORDER BY u.UserID

Maybe you could tell us what error message you got, or in what way the query
'does not work'?

--
Roger


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: sql help examining log table

[Dan Vande More]

I might be wrong, but this may get you going in the right direction:
select count(subject) as count, subject from outgoing where auth='USER'
group by subject order by timestamp desc limit 50

This would tell you each subject ever sent by the user, and how many times
that subject has been sent. Such as:
+---+--+
| count | subject  |
+---+--+
| 12| Subject 1|
| 36| Subject 2|
|  2| Another subject  |
+---+--+

And you can narrow it down if you know the subject such as:
select count(subject) as count, subject from outgoing where auth='USER' and
subject = 'Subject 2' group by subject order by timestamp desc limit 50

This would return
+---+--+
| count | subject  |
+---+--+
| 36| Subject 2|
+---+--+

I think this is right, if not hopefully my logic is.
Regardless, hopefully something can be useful.

Dan
-Original Message-
From: Viraj Alankar [mailto:[EMAIL PROTECTED]] 
Sent: Sunday, March 17, 2002 1:35 PM
To: [EMAIL PROTECTED]
Subject: sql help examining log table

Hello,

We use mysql to store outgoing email headers from our users and do
throttling
on users that appear to be spamming based on some simple queries to this
table. We use the Communigate mail server and this throttling script is a
PERL
program implemented as a content filter. More information is here for those
interested:

http://www.cse.fau.edu/~valankar/

I am trying to figure out what is the best way to do a certain query. My
outgoing log table looks like this:

mysql desc outgoing;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| rpath | varchar(80)  | YES  | | NULL|   |
| auth  | varchar(80)  |  | MUL | |   |
| ip| varchar(80)  | YES  | | NULL|   |
| hfrom | varchar(80)  | YES  | | NULL|   |
| hto   | varchar(80)  | YES  | | NULL|   |
| subject   | varchar(80)  | YES  | | NULL|   |
| messageid | varchar(80)  | YES  | | NULL|   |
| timestamp | timestamp(14)| YES  | | NULL|   |
| rcpts | smallint(5) unsigned | YES  | | 0   |   |
+---+--+--+-+-+---+

What I would like to do is reject a message if the last 50 messages have the
same subject.

In other words, I want to look at the 50 latest entries in this table for a
certain user (identified by the auth field) to find out if all of these
messages have the same subject.

The only way I can think of doing this is basically:

select subject from outgoing where auth='USER' order by timestamp desc limit
50

And then going through each one of these rows in my program to see if they
are
all the same subject. Is there a way I can do this logic in the select query
instead?

Thanks,

Viraj.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: sql help examining log table

[Dan Vande More]

Ditch the Limit 50 in those queries I wrote, they won't work, they only
limit the result set, not the searched set. I don't know what I was
thinking. If you need to searched set to be limited by the last 50 of the
user, I cannot think of anything at this time, perhaps after a few more cups
of coffee:)



-Original Message-
From: Dan Vande More [mailto:[EMAIL PROTECTED]] 
Sent: Sunday, March 17, 2002 1:44 PM
To: [EMAIL PROTECTED]
Subject: RE: sql help examining log table

I might be wrong, but this may get you going in the right direction:
select count(subject) as count, subject from outgoing where auth='USER'
group by subject order by timestamp desc limit 50

This would tell you each subject ever sent by the user, and how many times
that subject has been sent. Such as:
+---+--+
| count | subject  |
+---+--+
| 12| Subject 1|
| 36| Subject 2|
|  2| Another subject  |
+---+--+

And you can narrow it down if you know the subject such as:
select count(subject) as count, subject from outgoing where auth='USER' and
subject = 'Subject 2' group by subject order by timestamp desc limit 50

This would return
+---+--+
| count | subject  |
+---+--+
| 36| Subject 2|
+---+--+

I think this is right, if not hopefully my logic is.
Regardless, hopefully something can be useful.

Dan
-Original Message-
From: Viraj Alankar [mailto:[EMAIL PROTECTED]] 
Sent: Sunday, March 17, 2002 1:35 PM
To: [EMAIL PROTECTED]
Subject: sql help examining log table

Hello,

We use mysql to store outgoing email headers from our users and do
throttling
on users that appear to be spamming based on some simple queries to this
table. We use the Communigate mail server and this throttling script is a
PERL
program implemented as a content filter. More information is here for those
interested:

http://www.cse.fau.edu/~valankar/

I am trying to figure out what is the best way to do a certain query. My
outgoing log table looks like this:

mysql desc outgoing;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| rpath | varchar(80)  | YES  | | NULL|   |
| auth  | varchar(80)  |  | MUL | |   |
| ip| varchar(80)  | YES  | | NULL|   |
| hfrom | varchar(80)  | YES  | | NULL|   |
| hto   | varchar(80)  | YES  | | NULL|   |
| subject   | varchar(80)  | YES  | | NULL|   |
| messageid | varchar(80)  | YES  | | NULL|   |
| timestamp | timestamp(14)| YES  | | NULL|   |
| rcpts | smallint(5) unsigned | YES  | | 0   |   |
+---+--+--+-+-+---+

What I would like to do is reject a message if the last 50 messages have the
same subject.

In other words, I want to look at the 50 latest entries in this table for a
certain user (identified by the auth field) to find out if all of these
messages have the same subject.

The only way I can think of doing this is basically:

select subject from outgoing where auth='USER' order by timestamp desc limit
50

And then going through each one of these rows in my program to see if they
are
all the same subject. Is there a way I can do this logic in the select query
instead?

Thanks,

Viraj.

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: sql help examining log table

[Anvar Hussain K.M.]

Hi Viraj,

You can do it using temporary table.

Create temporary table tmp select  subject from outgoing where auth='USER' 
order by timestamp desc limit 50;
Select count(distinct subject) from tmp group by subject;
drop table tmp;
If the result of the second query is 1 all the last 50 messages have the 
same subject.
It is assumed there are at least 50 rows for auth = 'USER'.

Regards
Anvar.


At 03:34 PM 17/03/2002 -0500, you wrote:
Hello,

We use mysql to store outgoing email headers from our users and do throttling
on users that appear to be spamming based on some simple queries to this
table. We use the Communigate mail server and this throttling script is a PERL
program implemented as a content filter. More information is here for those
interested:

http://www.cse.fau.edu/~valankar/

I am trying to figure out what is the best way to do a certain query. My
outgoing log table looks like this:

mysql desc outgoing;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| rpath | varchar(80)  | YES  | | NULL|   |
| auth  | varchar(80)  |  | MUL | |   |
| ip| varchar(80)  | YES  | | NULL|   |
| hfrom | varchar(80)  | YES  | | NULL|   |
| hto   | varchar(80)  | YES  | | NULL|   |
| subject   | varchar(80)  | YES  | | NULL|   |
| messageid | varchar(80)  | YES  | | NULL|   |
| timestamp | timestamp(14)| YES  | | NULL|   |
| rcpts | smallint(5) unsigned | YES  | | 0   |   |
+---+--+--+-+-+---+

What I would like to do is reject a message if the last 50 messages have the
same subject.

In other words, I want to look at the 50 latest entries in this table for a
certain user (identified by the auth field) to find out if all of these
messages have the same subject.

The only way I can think of doing this is basically:

select subject from outgoing where auth='USER' order by timestamp desc 
limit 50

And then going through each one of these rows in my program to see if they are
all the same subject. Is there a way I can do this logic in the select query
instead?

Thanks,

Viraj.

-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail 
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL Help, Please...

[Tod Harter]

On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
 Hi

 I am trying to write an auction script that uses mysql as its backend. Each
 auction can have upto 25 sub auctions(cells) taking place.

 I'm trying to query the DB to give me a list of all the successfull bids
 for each cell, for each auction...

 SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
 b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
 auctionId,cellId ORDER BY bidTime DESC

 This is further complicated in so much that multiple MAX bids may exist at
 the same value, but, only the earliest should be returned for each cell.

 Which is returning some of the columns correctly, namely auctionid, cellid
 and bid, but it does not return the bidderId correctly.

 Can anybody help?

Your query is simply NOT relationally correct... The database has NO way to 
know WHICH bidder id to return in a given group. Suppose that for a given 
auctionid and cellid there might be 12 different bidders. You are telling the 
database engine to return ONE record for that group of 12 rows, so which 
bidderid will it use? The correct behaviour would be for MySQL to reject the 
query, it simply cannot be properly processed. Unfortunately I've found that 
MySQL doesn't behave correctly in these cases, instead it just returns one of 
the possible bidderid values at random. 

Your query would be technically correct if you used a summary function on 
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned 
columns in a GROUP BY must either by mentioned in the GROUP BY section of the 
query itself, OR they must be the results of a summary function. Any other 
use is not correct for the reason stated above.

In other words, you need to rewrite your application logic. Most likely you 
will need to add the b.bidderId to the GROUP BY and have the program walk 
through the result set and do further sumarization on its own. Alternately 
you might be able to craft an SQL statement that gets you what you want, but 
without correlated subqueries it is going to be difficult or impossible. I've 
had this same sort of problem myself... 

 Cheers

 Carl


 #
 # Table structure for table `sa_auction`
 #

 CREATE TABLE sa_auction (
   id int(11) NOT NULL auto_increment,
   start datetime NOT NULL default '-00-00 00:00:00',
   end datetime NOT NULL default '-00-00 00:00:00',
   state enum('waiting','active','expired') NOT NULL default 'waiting',
   PRIMARY KEY  (id)
 ) TYPE=MyISAM;

 #
 # Dumping data for table `sa_auction`
 #

 INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
 21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
 start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
 'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
 '2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
 sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
 '2002-08-01 11:30:00', 'waiting'); #
 

 #
 # Table structure for table `sa_bid`
 #

 CREATE TABLE sa_bid (
   id int(11) NOT NULL auto_increment,
   auctionId int(11) NOT NULL default '0',
   cellId tinyint(4) NOT NULL default '0',
   bid int(11) NOT NULL default '0',
   bidderId mediumint(9) NOT NULL default '0',
   bidtime timestamp(14) NOT NULL,
   PRIMARY KEY  (id),
   UNIQUE KEY id (id),
   KEY id_2 (id)
 ) TYPE=MyISAM;

 #
 # Dumping data for table `sa_bid`
 #

 INSERT INTO sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES
 (1, 1, 5, 1, 1, 20020128225421); INSERT INTO sa_bid (id, auctionId, cellId,
 bid, bidderId, bidtime) VALUES (2, 1, 5, 2, 2, 20020128225424); INSERT INTO
 sa_bid (id, auctionId, cellId, bid, bidderId, bidtime) VALUES (3, 1, 17,
 15, 2, 20020213214856); INSERT INTO sa_bid (id, auctionId, cellId, bid,
 bidderId, bidtime) VALUES (4, 1, 5, 3, 4, 20020213215649);



 
 This e-mail and any attachments are confidential.  If you are not the
 intended recipient, please notify us immediately by reply e-mail and then
 delete this message from your system. Do not copy this e-mail or any
 attachments, use the contents for any purpose, or disclose the contents to
 any other person: to do so could be a breach of confidence.

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED] Trouble
 unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, 

Re: SQL Help, Please...

[Anvar Hussain K.M.]

Hi,

At 11:21 AM 14/02/2002 -0500, you wrote:
On Thursday 14 February 2002 07:58, Carl Shelbourne wrote:
  Hi
 
  I am trying to write an auction script that uses mysql as its backend. Each
  auction can have upto 25 sub auctions(cells) taking place.
 
  I'm trying to query the DB to give me a list of all the successfull bids
  for each cell, for each auction...
 
  SELECT b.auctionId, b.cellId, MAX(b.bid) as bid, b.bidderId FROM sa_bid as
  b, sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
  auctionId,cellId ORDER BY bidTime DESC
 
  This is further complicated in so much that multiple MAX bids may exist at
  the same value, but, only the earliest should be returned for each cell.
 
  Which is returning some of the columns correctly, namely auctionid, cellid
  and bid, but it does not return the bidderId correctly.
 
  Can anybody help?

Your query is simply NOT relationally correct... The database has NO way to
know WHICH bidder id to return in a given group. Suppose that for a given
auctionid and cellid there might be 12 different bidders. You are telling the
database engine to return ONE record for that group of 12 rows, so which
bidderid will it use? The correct behaviour would be for MySQL to reject the
query, it simply cannot be properly processed. Unfortunately I've found that
MySQL doesn't behave correctly in these cases, instead it just returns one of
the possible bidderid values at random.

This behaviour is well documented in the manual.  There is a very practical
reason to allow this behaviour.  Suppose for efficiency reasons data is 
denormalized
and for example, id,name and some other particulars are all kept in the same
table .  If the database was very strict that all the columns selected 
should be
in the group by expression, one will have to put all these columns (id,name,..)
in the group by clause. Then the db engine will have to take all these 
fields in
the intermediate ordering phase of the query execution.  Surely this will be
inefficient in both time and space.  But with the 'incorrect' behaviour of 
Mysql
one can put all the data columns required to be returned in the select and do
group by only the id. This would make the query to complete very fast compared
to the former and the effect will be even more pronounced with index on id 
field.


Your query would be technically correct if you used a summary function on
bidderid, like MAX(b.bidderId) or somesuch. The rule is that the returned
columns in a GROUP BY must either by mentioned in the GROUP BY section of the
query itself, OR they must be the results of a summary function. Any other
use is not correct for the reason stated above.

In other words, you need to rewrite your application logic. Most likely you
will need to add the b.bidderId to the GROUP BY and have the program walk
through the result set and do further sumarization on its own. Alternately
you might be able to craft an SQL statement that gets you what you want, but
without correlated subqueries it is going to be difficult or impossible. I've
had this same sort of problem myself...
 
  Cheers
 
  Carl

The problem can be solved by using temporary tables.

Create temporary table tmp1
SELECT b.auctionId, b.cellId, MAX(b.bid) as bid FROM sa_bid as b,
  sa_auction AS a WHERE (a.state='active' AND b.auctionId=a.Id) GROUP BY
auctionId,cellId ;

Create temporary table tmp2
Select t1.auctionid, t1.cellid, t1.bid, min(b.bidtime) as bidtime from tmp1 
as t1, sa_bid as b
where (t1.auctionid = b.auctionid and t1.cellid = b.cellid and t1.bid = 
b.bid)
group by t1.auctionid,t1.cellid,t1.bid

Select t2.*, b.bidderid from tmp2 as t2, sa_bid as b
where t2.auctionid = b.auctionid and t2.cellid = b.cellid and t2.bid = 
b.bid and t2.bidtime = b.bidtime

I hope there may be better and simpler ways to achieve the objective.

Surely correlated subquery and derived table features might have been good 
features for such
situations.
Anvar.
 
  #
  # Table structure for table `sa_auction`
  #
 
  CREATE TABLE sa_auction (
id int(11) NOT NULL auto_increment,
start datetime NOT NULL default '-00-00 00:00:00',
end datetime NOT NULL default '-00-00 00:00:00',
state enum('waiting','active','expired') NOT NULL default 'waiting',
PRIMARY KEY  (id)
  ) TYPE=MyISAM;
 
  #
  # Dumping data for table `sa_auction`
  #
 
  INSERT INTO sa_auction (id, start, end, state) VALUES (1, '2002-01-23
  21:42:50', '2002-04-30 11:30:00', 'active'); INSERT INTO sa_auction (id,
  start, end, state) VALUES (2, '2002-01-23 21:42:50', '2002-02-09 06:30:00',
  'expired'); INSERT INTO sa_auction (id, start, end, state) VALUES (3,
  '2002-03-23 21:42:50', '2002-07-05 09:00:00', 'waiting'); INSERT INTO
  sa_auction (id, start, end, state) VALUES (4, '2002-03-23 21:42:50',
  '2002-08-01 11:30:00', 'waiting'); #
  
 
  #
  # Table structure for table `sa_bid`
  #
 
  CREATE TABLE sa_bid (
id int(11) NOT NULL 

Re: SQL help plz

[Paul DuBois]

At 21:36 +0200 1/30/02, P.Agenbag wrote:
Hi
I need help with an sql string:

have a table similar to this:

idkeynamedate
1 123name1  date1
2 123name1  date2
3 111name2  date1
4 111name2  date2
5 123name1  date3
Now, I need sql to report the following result:

name1   date3
name2   date2


thus, report all the names in the table but only the ones with the 
latest date.

The sql MUST use the key to compare the different rows with each 
other and NOT the name, as the name is prone to spelling errors (ok, 
the key as well, but it's easier to make a typo with letters than 
with digits and I already have a couple of entries where the names 
of the entries are different.

Create another table to hold the maximum date associated with each key value
(which I'm calling k rather than key because key is a reserved word):

CREATE TABLE t2 SELECT k, MAX(date) AS date FROM t GROUP BY k;

Then join this table with the original to get the rows with the appropriate
k and date values, printing out the name and date values:

SELECT t.name, t.date FROM t, t2
WHERE t.k = t2.k and t.date = t2.date
ORDER BY name;

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: SQL help plz

[Rick Emery]

mysql describe mytable;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| id| int(11)  | YES  | | NULL|   |
| keyq  | int(11)  | YES  | | NULL|   |
| name  | char(20) | YES  | | NULL|   |
| dateq | date | YES  | | NULL|   |
+---+--+--+-+-+---+
4 rows in set (0.00 sec)

select name,max(dateq) from mytable group by name;

-Original Message-
From: P.Agenbag [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 1:36 PM
To: mysql
Subject: SQL help plz


Hi
I need help with an sql string:

have a table similar to this:

idkeynamedate
1 123name1  date1
2 123name1  date2
3 111name2  date1
4 111name2  date2
5 123name1  date3
Now, I need sql to report the following result:

name1   date3
name2   date2


thus, report all the names in the table but only the ones with the 
latest date.

The sql MUST use the key to compare the different rows with each other 
and NOT the name, as the name is prone to spelling errors (ok, the key 
as well, but it's easier to make a typo with letters than with digits 
and I already have a couple of entries where the names of the entries 
are different.
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: SQL help plz

[Rick Emery]

Sorry, I meant:

mysql select name,max(dateq) from mytable group by keyq;

-Original Message-
From: Rick Emery 
Sent: Wednesday, January 30, 2002 2:14 PM
To: 'P.Agenbag'; mysql
Subject: RE: SQL help plz


mysql describe mytable;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| id| int(11)  | YES  | | NULL|   |
| keyq  | int(11)  | YES  | | NULL|   |
| name  | char(20) | YES  | | NULL|   |
| dateq | date | YES  | | NULL|   |
+---+--+--+-+-+---+
4 rows in set (0.00 sec)

select name,max(dateq) from mytable group by name;

-Original Message-
From: P.Agenbag [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 1:36 PM
To: mysql
Subject: SQL help plz


Hi
I need help with an sql string:

have a table similar to this:

idkeynamedate
1 123name1  date1
2 123name1  date2
3 111name2  date1
4 111name2  date2
5 123name1  date3
Now, I need sql to report the following result:

name1   date3
name2   date2


thus, report all the names in the table but only the ones with the 
latest date.

The sql MUST use the key to compare the different rows with each other 
and NOT the name, as the name is prone to spelling errors (ok, the key 
as well, but it's easier to make a typo with letters than with digits 
and I already have a couple of entries where the names of the entries 
are different.
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: SQL help plz

[Rick Emery]

Further, I'd advise NOT using field names like date and key.  Using
eserved words is never a good prcatice.

-Original Message-
From: Rick Emery 
Sent: Wednesday, January 30, 2002 2:16 PM
To: 'P.Agenbag'; 'mysql'
Subject: RE: SQL help plz


Sorry, I meant:

mysql select name,max(dateq) from mytable group by keyq;

-Original Message-
From: Rick Emery 
Sent: Wednesday, January 30, 2002 2:14 PM
To: 'P.Agenbag'; mysql
Subject: RE: SQL help plz


mysql describe mytable;
+---+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-+---+
| id| int(11)  | YES  | | NULL|   |
| keyq  | int(11)  | YES  | | NULL|   |
| name  | char(20) | YES  | | NULL|   |
| dateq | date | YES  | | NULL|   |
+---+--+--+-+-+---+
4 rows in set (0.00 sec)

select name,max(dateq) from mytable group by name;

-Original Message-
From: P.Agenbag [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 30, 2002 1:36 PM
To: mysql
Subject: SQL help plz


Hi
I need help with an sql string:

have a table similar to this:

idkeynamedate
1 123name1  date1
2 123name1  date2
3 111name2  date1
4 111name2  date2
5 123name1  date3
Now, I need sql to report the following result:

name1   date3
name2   date2


thus, report all the names in the table but only the ones with the 
latest date.

The sql MUST use the key to compare the different rows with each other 
and NOT the name, as the name is prone to spelling errors (ok, the key 
as well, but it's easier to make a typo with letters than with digits 
and I already have a couple of entries where the names of the entries 
are different.
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL help plz

[Steve Severance]

Try this:

select key_col, min(name), max(date_col)
from my_table
group by key_col ;


You could use max(name) instead of min(name) also, although
since the names can be misspelled, I don't see why it would matter
which name is displayed.

s.s.

On Wed, 30 Jan 2002 21:36:04 +0200, you wrote:

Hi
I need help with an sql string:

have a table similar to this:

idkeynamedate
1 123name1  date1
2 123name1  date2
3 111name2  date1
4 111name2  date2
5 123name1  date3
Now, I need sql to report the following result:

name1   date3
name2   date2


thus, report all the names in the table but only the ones with the 
latest date.

The sql MUST use the key to compare the different rows with each other 
and NOT the name, as the name is prone to spelling errors (ok, the key 
as well, but it's easier to make a typo with letters than with digits 
and I already have a couple of entries where the names of the entries 
are different.
 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL help

[Zak Greant]

SELECT * FROM db ORDER BY rand() LIMIT 5

--zak


- Original Message -
From: Webmaster [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, May 08, 2001 9:38 PM
Subject: SQL help


 How do I write SQL in MySQL to randomly select 5 records from a table?


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL help

[Tony Shiu]

i think it is more suitable to do it in programming level in mysql, though I
know there is a function is M$sql server.

if your table schema has a unique id field, before submit a query to DB,
1) select count(*) from it
2) from the above resultset, program to draw whatever number of records you
want.
3) submit the query, select * from it where id in (your random record ids);


- Original Message -
From: Webmaster [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, May 09, 2001 11:38 AM
Subject: SQL help


 How do I write SQL in MySQL to randomly select 5 records from a table?


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL help

[Webmaster]

i dont follow step #3.


At 11:42 PM 5/8/2001, Tony Shiu wrote:
 i think it is more suitable to do it in programming level in mysql, though I
 know there is a function is M$sql server.
 
 if your table schema has a unique id field, before submit a query to DB,
 1) select count(*) from it
 2) from the above resultset, program to draw whatever number of records you
 want.
 3) submit the query, select * from it where id in (your random record ids);
 
 
 - Original Message -
 From: Webmaster [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Wednesday, May 09, 2001 11:38 AM
 Subject: SQL help
 
 
  How do I write SQL in MySQL to randomly select 5 records from a table?
 
 
  -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
 
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail
  [EMAIL PROTECTED]
  Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL help

[Tony Shiu]

for 3)
if you have a set of random values e.g. 1,3, 6, 9, 4
then your sql will be
select * from your-table
where id in (1, 3, 6, 9, 4);

FYI:
if you are using ver 3.23.X, you may use 
SELECT * FROM your-table ORDER BY rand() LIMIT 5
contributed from: Zak Greant [EMAIL PROTECTED]

rand() seems using primary key of your table.

mine works in both old and new versions.


- Original Message - 
From: Webmaster [EMAIL PROTECTED]
To: Tony Shiu [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, May 09, 2001 12:27 PM
Subject: Re: SQL help


 i dont follow step #3.
 
 
 At 11:42 PM 5/8/2001, Tony Shiu wrote:
  i think it is more suitable to do it in programming level in mysql,
 though I
  know there is a function is M$sql server.
  
  if your table schema has a unique id field, before submit a query to
 DB,
  1) select count(*) from it
  2) from the above resultset, program to draw whatever number of
 records you
  want.
  3) submit the query, select * from it where id in (your random record
 ids);
  
  
  - Original Message -
  From: Webmaster [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Wednesday, May 09, 2001 11:38 AM
  Subject: SQL help
  
  
   How do I write SQL in MySQL to randomly select 5 records from a
 table?
  
  
  
 -
   Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
  
   To request this thread, e-mail [EMAIL PROTECTED]
   To unsubscribe, e-mail
   [EMAIL PROTECTED]
   Trouble unsubscribing? Try:
 http://lists.mysql.com/php/unsubscribe.php
  
  
  -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
  
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail
 [EMAIL PROTECTED]
  Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL help

[j.urban]

You might be able to do something like this instead:

  SELECT *, id*0+RAND() AS r FROM table ORDER BY r LIMIT 5;

Check the list archives, this issue comes up quite often.


On Wed, 9 May 2001, Webmaster wrote:

 i dont follow step #3.
 
 
 At 11:42 PM 5/8/2001, Tony Shiu wrote:
  i think it is more suitable to do it in programming level in mysql, though I
  know there is a function is M$sql server.
  
  if your table schema has a unique id field, before submit a query to DB,
  1) select count(*) from it
  2) from the above resultset, program to draw whatever number of records you
  want.
  3) submit the query, select * from it where id in (your random record ids);
  
  
  - Original Message -
  From: Webmaster [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Wednesday, May 09, 2001 11:38 AM
  Subject: SQL help
  
  
   How do I write SQL in MySQL to randomly select 5 records from a table?
  



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: SQL HELP

[Cindy]

"j.urban" writes:
 select field1,field2,DATE_FORMAT(datefield,"%M %d, %Y"),field4,fieldn from
 table;

He didn't want to have to explicitly list all 40 other fields, though.
Can't he do something like

SELECT *.DATE_FORMAT(datefield, "%M %d, %Y") FROM table;

?  That gives him one extra field, and he can just ignore the first
datefield.

--Cindy

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php