How to randomly select one value per group-by column?
Dear list, Suppose a table with articles that persons have ordered: create table t (pers_id int(6), odate date, art_id int(8)) I want to select one randomly choosen odate for every pers_id. I can easily select the minimum or the maximum odate: select pers_id, min(odate) from t group by pers_id What would like is something like: select pers_id, random_select(odate) from t group by pers_id Does anyone know how to do this? Cheers, Paul -- Paul B van den Berg, Manager InterAction database, http://www.iadb.nl Dept of Social Pharmacy, Pharmacoepidemiology and Pharmacotherapeutics -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get machine independent unique id??
On 6/1/06, Neeraj <[EMAIL PROTECTED]> wrote: > > Is there any way to get a unique ids across all databases (by any way) , > because records from all the databases are finally get stored on a central > database. And the ids of transaction (a record) must remain unique in > central database. > The easiest way, without any replication setup, might be to give all your client databases a unique database_id. Then in your central database database_id.client_transaction_id is guaranteed unique. Regards, Paul -- Paul B van den Berg, Manager InterAction database, http://www.iadb.nl Dept of Social Pharmacy, Pharmacoepidemiology and Pharmacotherapeutics Groningen University Institute for Drug Exploration, tel +31 50361 A Deusinglaan 1, 9713AV Groningen, Netherlands, fax +31 503632772 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a tricky/impossible query...
On Thu, 14 Apr 2005 11:57:50 +0100
"Andrew Braithwaite" <[EMAIL PROTECTED]> wrote:
> Thanks for the idea,
>
> Unfortunately I can't do that as the ranges involved are unknown and
> will be from 1 to several billion at lease. I can't have another table
> that needs to be augmented each time my ranges change.
>
> Any other ideas?
>
> Cheers,
>
> Andrew
I Think you really need the second table, but you can fill it as needed:
my $x=1700;
my $y=2200;
$dbh->do( q{ insert into wibble set x=$x, y=$y });
for ($i=$x; $i <=$y ; $i++) {
$dbh->do( q{ insert ignore into z set z = $i });
}
Regards, Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a tricky/impossible query...
Hi,
In SQL you need to define the data that you want to work with:
create table z ( z int(5) not null primary key);
insert into z values
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22);
If you need more values you could use a simple perl looping construct:
for ($i=0; $i <= $max; $i++) {
$dbh->do( q{ insert into z set z = $i });
}
Once you have the table filled, it's easy to explode the x/y ranges by seq:
select seq, z
from wibble, z
where z between x and y
Then the rows with seq=1 are:
| seq | z |
+-++
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 1 | 8 |
| 1 | 9 |
| 1 | 10 |
The rest is as you wanted.
Regards, Paul
On 14/4/05 1:11 am, "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote:
> Here's what I need to do...
>
> create table wibble(
> seq int(3) auto_increment primary key,
> x int(5),
> y int(5)
> );
>
> insert into wibble set x=5, y=10;
> insert into wibble set x=1, y=3;
> insert into wibble set x=17, y=22;
>
> mysql> select * from wibble;
> +-+--+--+
> | seq | x| y|
> +-+--+--+
> | 1 |5 | 10 |
> | 2 |1 |3 |
> | 3 | 17 | 22 |
> +-+--+--+
> 3 rows in set (0.09 sec)
>
> So I want to run a query to explode the x/y ranges by seq.
>
> The required output is:
>
> mysql> select some clever things from wibble where some clever stuff happens
> here;
> +-+--+
> | seq | z|
> +-+--+
> | 1 |1 |
> | 1 |2 |
> | 1 |3 |
> | 1 |4 |
> | 1 |5 |
> | 2 |1 |
> | 2 |2 |
> | 2 |3 |
> | 3 | 17 |
> | 3 | 18 |
> | 3 | 19 |
> | 3 | 20 |
> | 3 | 21 |
> | 3 | 22 |
> +-+--+
> 14 rows in set (0.17 sec)
>
> Can anyone help me to achieve this result?
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting Duplicate Records
To select records only once try something like select * from your_table group by field1, field2, .. Regards, Paul Kim Mackey wrote: > Group, > > I have been working on a project for a while now trying to figure out > how to remove duplicate records from a single table using a query. To > complicate matters, what constitutes a duplicate record is a match on > several fields, but not all fields. I have been successful in > matching all duplicates based on the fields I'm interested in, but > this only returns the duplicate records. I want a query that will > return all records from the table, but only once if certain fields are > the same. > > The table structure is basically: > User Code > Date > Last Name > First Name > Address > City > State > ZIP > and then some other fields > > I don't want to use the values in the other fields to determine if > the record duplicates another. I'm not concerned about which one of > the duplicate records I keep because I will make a backup copy of this > table before removing the duplicates, so I will still have all the > data from the records deleted. Later I will redesign the tables for > this database and link back in the data from the other fields. > > So I need a method that will output one and only one record from the > table in which the data in the above mentioned fields are the same. > Again, even if the data in the remaining fields are different I just > want one of the records, and I don't care which one. > > Thanks for your help > Kim Mackey > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql SELECT IF()
On 18 Jun 2002 at 16:36, Walter D. Funk wrote: > Hi everybody! > > I need to count the ACTIVE users of my Mysql users table; where 'active' is > a flag showing this status, > using the following query it returns all the user in the table, and I need > only those who have tha flag active set to 'Y' > > I know this could be done by using a where clause, but in this case it is > not possible, because this is actually only a part of the query and I do not > want to limit the result set with a where clause, > > this is the query I am using, I tried other combinations , but it did not > work :-( > > SELECT IF( STRCMP(active,'Y') = 0 , COUNT(userId) , 0 ) as activeUsers FROM > usersTbl > > > any help will be appreciated, > thanx > Try: SELECT COUNT(IF( STRCMP(active,'Y') = 0 , userId, null)) as activeUsers FROM usersTbl Regards, Paul - 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
