Say I have a table with any number of car brands, with belonging street dates.
Is it possible to do the following type of sort in a single query?
1. First I want the newest model of all car brands, but only one of
each brand! And they should be ordered by year
2. Then I want the cars that weren'
performance.
if each select takes 2 seconds, will the union of them take 2+2=4
seconds, or will it be quicker?
2009/4/26 zhu dingze :
> select brand, max(year) from cars group by year order by year
> union
> select brand, year from cars order by year
>
> 2009/4/25 Olav Mørkrid
>>
Dear MySQL forum.
I have performance problems when using "left join x" combined with
"where x.y is null", in particularily when combining three tables this
way.
Please contact me by e-mail if you are familiar with these issues and
know how to eliminate slow queries.
I would really appreciate you
hello
i would like to search a table column for a range of non-ascii
characters, or a particular non-ascii character. how can i do this?
the column can be any string like "hello, world" or "norwegian
characters æøå here".
refer to these pseudo examples:
SELECT * FROM TABLE WHERE COLUMN CONTAINS
hello
i have a table "vote" which has the columns "voter" and "candidate". i
would like to make a list of the most popular candidates *except*
those who are on my favorite list. using a sub-select, it's easy:
my favorites:
select candidate from vote where voter = '$me';
most popular:
select cand
On 27/02/2008, Phil <[EMAIL PROTECTED]> wrote:
> I'm confused as to why you need the subselect at all?
>
> As it's all the same table why can't you just use
>
> select candidate,count(*) as total from vote where voter <> '$me' group by
>
(4 votes)
On 27/02/2008, Phil <[EMAIL PROTECTED]> wrote:
> Ok then, so
>
> select candidate,count(*) as total from vote where (voter <> '$me' and
> vote =1) group by candidate order by total
> desc;
>
> On Wed, Feb 27, 2008 at 9:37 AM, Olav Mørkrid <
hello
i have a message table like this:
created datetime
sender int
recipient int
message text
to get all my messages i would do:
select * from message where sender = $MYID or recipient = $MYID
but how do i make a query that returns the rows of only the NEWEST
messages between myself and my co
just to clarify, i want the
1. the newest message between myself and person A
2. the newest message between myself and person B
3. the newest message between myself and person C
4. the newest message between myself and person D
... and so on
--
MySQL General Mailing List
For list archives: http
On 28/04/2008, Martijn Tonies <[EMAIL PROTECTED]> wrote:
> > 1. the newest message between myself and person A
> > 2. the newest message between myself and person B
> > 3. the newest message between myself and person C
> > 4. the newest message between myself and person D
> Still, define "new
On 28/04/2008, Martijn Tonies <[EMAIL PROTECTED]> wrote:
> select *
> from ( select * from msgs order by msgs.created desc ) t
> where sender = 1 or recipient = 1
> group by sender, recipient
not quite right. first comes all of MY newest messages, then comes all
of THEIR newest messages. for
Exactly.
2008/4/28, Martijn Tonies <[EMAIL PROTECTED]>:
>
> > > select *
> > > from ( select * from msgs order by msgs.created desc ) t
> > > where sender = 1 or recipient = 1
> > > group by sender, recipient
> >
> > not quite right. first comes all of MY newest messages, then comes all
> > of
using one single sql statement, how do i update the auto_increment id
column of a row to have the new highest id in the table?
in other words: how do i make a row seem like it was just inserted?
i know how to do it with two statements, but i want to do it with one
to ensure nothing goes wrong:
thanks olexandr
my posting had a misprint. the select should be on "mytable" not
"user", so when i use your suggestion, i get an error:
mysql> update test set id = (select max(id) + 1 from test) where id = '$myid';
ERROR 1093 (HY000): You can't specify target table 'test' for update
in FROM clau
what i want to do is to take an old row from maybe three weeks ago,
and make its id appear as if it was the newest inserted row in the
table. therefore last_insert_id() cannot be used.
i could introduce a timestamp column to achieve my goals, but for
certain reasons i would like to update the id
baron
your suggestion does the trick indeed. i take a deep bow!
thanks also for mentioning the related issues.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
wait, let's make it even more interesting :)
what if you want to update more than one row, and each row should have
a successive new id. is that possible in one statement?
i tried just removing the where statement in barons suggestion, which
fails as i guess the select is computed only once prio
i agree with the logic that mysql treats things as sets. my problem
can easily be solved by treating one row at a time. thanks again!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
steve
i'm happy to hear your optimism, handling billions of rows sounds
amazing. but i'd like to be fully assured.
a frequent use of the table will perform selects that show:
a) people you have seen
b) people you haven't seen yet
an average user will quite quickly build a list of thousands of
hi, i have some questions about the command line of mysql for WIN32:
- does mysql have any macro system that lets you shorten often-used
commands, like expanding "n5 mytable" into "select * from mytable
order by id desc limit 5"
- how can tab completion be enabled (it does not work by default, e
hi
i am experimenting with sql for getting lists of friends.
select friend.*
from user
left join link on user.id=link.id and user.id = $MYID
right join user friend on link.friend_id = friend.id
where user.name is null;
on my local windows machine running mysql 5 it works fine, howev
some more info on this:
putting "user AS friend" improves the query, but does not eliminate the problem.
also, here are the version numbers for each sql server:
mysql Ver 12.22 Distrib 4.0.22, for portbld-freebsd4.9 (i386)
mysql.exe Ver 14.12 Distrib 5.0.24, for Win32 (ia32)
the query even r
hello
is it possible to make aliases for types, so that for instance:
"uint" means "int unsigned not null default 0"
it makes table definitions unreadable having to write those long
definitions all the time.
thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
hello
does anyone know what is returned when you do a where column without
further parameters?
SELECT * FROM TABLE WHERE COLUMN;
for integer columns it seems to return non-zero columns, but for other
types of columns the results seemed unpredictable.
--
MySQL General Mailing List
For list arch
if a table column is supposed to contain text in iso-8859-1, but utf-8
encoding have snuck in on a few rows by mistake, how are these rows
converted into iso-8859-1?
what i am looking for is something like this:
update mytable set mycolumn = utf8toiso88591(mycolumn) where id
between 500 and 600;
yep, thru php it's easy: just do a utf8_decode($value). but is it
possible directly from the mysql command line.
On 31/08/2007, Ken Peng <[EMAIL PROTECTED]> wrote:
>
> On Thu, 30 Aug 2007 11:41:14 +0200, "Olav Mørkrid"
> <[EMAIL PROTECTED]> said:
> > if
26 matches
Mail list logo
