Hi,
This thing puzzles me for quite some time and I wasn't successful in
finding a clear answer anywhere - I would be grateful for some
help.
Here is a db example:
table_1
id
some_field_01
[...]
some_field_20
table_2
itemid (table_1_id)
value
Let's say that the table_2 is used to store some pr
userId long
picture MeduimBlob
datePosted DateTime
A userId can have many pictures posted. I want to write a
query that returns a distinct userId along with the most
recent picture posted. Can someone suggest an elegant and
fast query to accomplish this?
Latest pic for user N:
SELECT userID,
See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of
a User
select userId, picture, MAX(datePosted) from A order by datePosted;
In Response To:
Hello everyone,
I have a table A:
userId long
picture MeduimBlob
datePosted DateTime
A userId can have many pictures
See Thread at: http://www.techienuggets.com/Detail?tx=32975 Posted on behalf of
a User
Hello everyone,
I have a table A:
userId long
picture MeduimBlob
datePosted DateTime
A userId can have many pictures posted. I want to write a query that returns a
distinct userId along with the most recent
On Fri, Apr 11, 2008 at 4:01 PM, Victor Danilchenko
<[EMAIL PROTECTED]> wrote:
> Oooh, this looks evil. It seems like such a simple thing. I guess
> creating max(log_date) as a field, and then joining on it, is a solution --
> but my actual query (not the abridged version) is already half a
I just thought of something else... could the same be accomplished
using stored routines? I could find no way in MySQL to create stored
routines which could be used with the 'group by' queries though.
If this were possible, it should then be also possible to define a
'LAST' stored routine, o
On Fri, Apr 11, 2008 at 1:01 PM, Victor Danilchenko
<[EMAIL PROTECTED]> wrote:
> Oooh, this looks evil. It seems like such a simple thing. I guess
> creating max(log_date) as a field, and then joining on it, is a solution --
> but my actual query (not the abridged version) is already half a
Oooh, this looks evil. It seems like such a simple thing. I guess
creating max(log_date) as a field, and then joining on it, is a solution
-- but my actual query (not the abridged version) is already half a page
long.
I think at this point, unless someone else suggests a better solution,
th
On Fri, Apr 11, 2008 at 11:46 AM, Victor Danilchenko
<[EMAIL PROTECTED]> wrote:
> GROUP BY seems like an obvious choice; 'GROUP BY username', to be
> exact. However, this seems to produce not the last row's values, but ones
> from a random row in the group.
Under most databases your query i
Hi all,
I trying to run a query where, after doing a UNION on two different
SELECTs, I need to sort the result by username and log_date fields, and
then grab the last entry for each username ('last' as determined by the
ordering of the log_date field, which is a datetime).
GROUP BY
Ah, that would work.
Looks like I was making the problem too complex in my mind, thanks for your
help.
Adam Bishop
-Original Message-
From: Dan Nelson [mailto:[EMAIL PROTECTED]
Sent: 22 January 2007 07:07
To: Adam Bishop
Cc: mysql@lists.mysql.com
Subject: Re: SQL Query Question
In the
In the last episode (Jan 22), Adam Bishop said:
> If I have a dataset as below:
>
> Name, Age, Word
>
> Bob, 13, bill
> Joe, 13, oxo
> Alex, 14, thing
> Jim, 14, blob
> Phil, 14, whatsit
> Ben, 15, doodah
> Rodney, 15, thingy
>
> I want to select the first block where
If I have a dataset as below:
Name, Age, Word
Bob, 13, bill
Joe, 13, oxo
Alex, 14, thing
Jim, 14, blob
Phil, 14, whatsit
Ben, 15, doodah
Rodney, 15, thingy
I want to select the first block where the age is equal, i.e. return in the
case of the above set,
On 22-Dec-2004 Michael J. Pawlowsky wrote:
> Im trying to come up with a more efficient method to do this.
> I have a table where people enter some info into the table.
>
> I would like to allow the users to be able to see where they stand
> rank
> wise with everyone else.
> Right now I bas
Try this:
CREATE TEMPORARY TABLE tmpRankings (
Rank int auto_increment,
entries int,
user_id int
)
INSERT tmpRankings (points, user_id)
SELECT count(1), user_id
FROM sometablenamehere
GROUP BY user_id
ORDER BY entries DESC;
This way the tmpRankings table contains an orde
I’m trying to come up with a more efficient method to do this.
I have a table where people enter some info into the table.
The more entries they add the more “points” they get.
(1 point per entry).
I would like to allow the users to be able to see where they stand rank
wise with everyone else.
Rig
Right. If the employee ID in either the rep_no or entered_by columns does
not have a corresponding row in the global_employee table, then the regular
join won't match that row. In that case, as you found, you need a LEFT
JOIN, which guarantees you get the rows from the table on the left, and
Thanks a lot Michael.
A regular join did not seem to work. But when I tried a LEFT JOIN it worked.
A cut down example of it is the following.
SELECT global_lead.id, rep_no, es.fname as sales_name, em.fname as
marketing_name
FROM global_lead
LEFT JOIN global_employee es ON global_lead.rep_no = es.i
You need to join the employee table twice, once for each id lookup, like this:
SELECT es.name AS sales_name, em.name AS marketing_name, leads.id
FROM leads JOIN employee es ON leads.salesid = es.id
JOIN employee em ON leads.marketingid = em.id;
Michael
Michael J. Pawlowsky wrote:
I
Im not sure if this is possible or not.
I have a Sales leads table.
Part of the table has 2 employee_ids.
1. The Sales person the lead is assigned to.
2. The Marketing person that generated the lead.
Then there is a employee table that has ids and names.
When generating a report for leads I would
If you do any math on your column, no index on the column can be used. If
possible, you should always try to write your condition so that the
calculations are done on the value(s) to compare to, not on the column. So,
assuming you have no rows with future timestamps, something like this should
The you will need to use the second format.
DATE_FORMAT(queue_time, '%Y%m%d') = CURRENT_DATE()
-Original Message-
From: Dirk Bremer (NISC)
To: [EMAIL PROTECTED]
Sent: 4/16/04 4:09 PM
Subject: Re: SQL Query Question
- Original Message -
From: "Victor Pendleton&quo
On Fri, 2004-04-16 at 14:09, Dirk Bremer (NISC) wrote:
> - Original Message -
> From: "Victor Pendleton" <[EMAIL PROTECTED]>
> To: "'Dirk Bremer (NISC) '" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Friday, April 16, 2004 15
- Original Message -
From: "Victor Pendleton" <[EMAIL PROTECTED]>
To: "'Dirk Bremer (NISC) '" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, April 16, 2004 15:57
Subject: RE: SQL Query Question
> If your data is stored in the
27;%Y%m%d') = CURRENT_DATE() + 0
...no index usage though
-Original Message-
From: Dirk Bremer (NISC)
To: [EMAIL PROTECTED]
Sent: 4/16/04 3:25 PM
Subject: Re: SQL Query Question
- Original Message -
From: "Victor Pendleton" <[EMAIL PROTECTED]>
To: "
- Original Message -
From: "Victor Pendleton" <[EMAIL PROTECTED]>
To: "'Dirk Bremer (NISC) '" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, April 16, 2004 15:06
Subject: RE: SQL Query Question
> WHERE queue_time = Now()
WHERE queue_time = Now() + 0
Are you wanting just the date or the datetime?
-Original Message-
From: Dirk Bremer (NISC)
To: [EMAIL PROTECTED]
Sent: 4/16/04 2:54 PM
Subject: SQL Query Question
I have a simple table where one of the columns is named queue_time and
is
defined as a timestamp
bject: SQL Query Question
I have a simple table where one of the columns is named queue_time and is
defined as a timestamp-type. I would like to query this table for all rows
where the queue_time equals the current date. I an a newbie and have been
wrestling with the docs for a solution. You he
I have a simple table where one of the columns is named queue_time and is
defined as a timestamp-type. I would like to query this table for all rows
where the queue_time equals the current date. I an a newbie and have been
wrestling with the docs for a solution. You help will be appreciated.
Dirk
I think I figured out the time problem. If I make s2 in the or s1 and
remove any instances of s2 it works very fast with the 'or'.
Joe
On Tuesday, January 20, 2004, at 09:50 AM, sulewski wrote:
Hello,
For my final solution I decided to use the inner join method. The
query is created dynamica
Hello,
For my final solution I decided to use the inner join method. The query
is created dynamically based upon a user interface component that
allows people to build queries using parenthesis, ands and or's. Plus
there is another field that I didn't include in the original question
so as to
Michael Satterwhite wrote:
On Monday 19 January 2004 16:30, Jochem van Dieten wrote:
Michael Satterwhite said:
On Monday 19 January 2004 15:38, Jochem van Dieten wrote:
So let's make it 2 fields:
SELECT
t1.*
FROM
table1 t1,
table2 t2 INNER JOIN table2 t3
ON (t2.rdid = t3.rdid AND t2.vid =
On Monday 19 January 2004 16:30, Jochem van Dieten wrote:
> Michael Satterwhite said:
> > On Monday 19 January 2004 15:38, Jochem van Dieten wrote:
> >> So let's make it 2 fields:
> >>
> >> SELECT
> >> t1.*
> >> FROM
> >> table1 t1,
> >> table2 t2 INNER JOIN table2 t3
> >>ON (t2.rdid = t3
Michael Satterwhite said:
> On Monday 19 January 2004 15:38, Jochem van Dieten wrote:
>> So let's make it 2 fields:
>>
>> SELECT
>> t1.*
>> FROM
>> table1 t1,
>> table2 t2 INNER JOIN table2 t3
>>ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554)
>> WHERE
>> t1.rdid = t2.rdid
>>
>>
On Monday 19 January 2004 15:38, Jochem van Dieten wrote:
> So let's make it 2 fields:
>
> SELECT
> t1.*
> FROM
> table1 t1,
> table2 t2 INNER JOIN table2 t3
>ON (t2.rdid = t3.rdid AND t2.vid = 46 AND t3.vid = 554)
> WHERE
> t1.rdid = t2.rdid
>
> Add GROUP BY/DISTINCT per your requireme
Lincoln Milner said:
> Or, if I'm not mistaken, you could do something like:
> SELECT t1.*
> FROM table1 t1, table2 t2
> WHERE t1.id = t2.rdid
>AND t2.vid IN (46, 554)
> ;
>
> That should work
No. You are back to square one where there should only be one record
in t2 with a vid of either 46
Jochem,
I believe this works. This is also easy to build dynamically. The query
is going to be generated based upon some user input. Thank you very
much,
Joe
On Monday, January 19, 2004, at 04:38 PM, Jochem van Dieten wrote:
Michael Satterwhite said:
On Monday 19 January 2004 13:17, sulewski
ssage-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Monday, January 19, 2004 4:39 PM
To: [EMAIL PROTECTED]
Subject: Re: SQL Query Question
Michael Satterwhite said:
> On Monday 19 January 2004 13:17, sulewski wrote:
>> Okay, I think I'm missing something obvious.
Michael Satterwhite said:
> On Monday 19 January 2004 13:17, sulewski wrote:
>> Okay, I think I'm missing something obvious. I have two tables
>>
>> Table 1 Table 2
>> ___ _
>> ID rdid vid
On Monday 19 January 2004 13:17, sulewski wrote:
> Okay, I think I'm missing something obvious. I have two tables
>
> Table 1 Table 2
> ___ _
> ID rdid vid
> ___ _
ment
and
maybe I can help somemore.
I think you get what I want to do. So how do I do it? :)
- Original Message -
From: "sulewski" <[EMAIL PROTECTED]>
To: "Jamie Murray" <[EMAIL PROTECTED]>
Sent: Monday, January 19, 2004 4:41 PM
Subject: Re: SQL Query Ques
t: Monday, January 19, 2004 3:47 PM
Subject: SQL Query Question
Okay, I think I'm missing something obvious. I have two tables
Table 1 Table 2
___ _
ID rdid vid
___
Okay, I think I'm missing something obvious. I have two tables
Table 1 Table 2
___ _
ID rdid vid
___ _
ID in table 1 links to rdid in table 2. This is
leo-
From: Paal Eriksen
To: [EMAIL PROTECTED]
Sent: Tuesday, November 11, 2003 7:11 PM
Subject: SQL query question
SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as
Location
FROM
firmal INNER JOIN (
person INNER JOIN lokasjon
ON pe
So close, Thanks you very much Andy. I tried one similar to your suggestion, but
didn't get quite the result i expected.
Cheers
Paal
Ny versjon av Yahoo! Messenger
Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom
] with the primary key of the person table.
Andy
> -Original Message-
> From: Paal Eriksen [mailto:[EMAIL PROTECTED]
> Sent: 11 November 2003 12:11
> To: [EMAIL PROTECTED]
> Subject: SQL query question
>
>
> Hi, i have the following query:
>
> SELECT person.na
Hi, i have the following query:
SELECT person.name as Name, firmal.beskrivelse as Businessline, lokasjon.navn as
Location
FROM
firmal INNER JOIN (
person INNER JOIN lokasjon
ON person.lokid = lokasjon.lokid)
ON firmal.firmalid = person.firmalid
which will give me a list of Name, Businessline,
.("URL")%>
MORE HTML STUFF HERE LIKE and and
<%=URLDESC%>
Etc
Etc
That would be proper programming form...
Did that make sense?
-Original Message-
From: Rolf C [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 19, 2003 4:57 PM
To: [EMAIL PROTECTED]
Subject: SQL query question
He
Well, from what limited info I have, it looks like your image tag is not
closed properly.
Regards,
Mike Hillyer
www.vbmysql.com
> -Original Message-
> From: Rolf C [mailto:[EMAIL PROTECTED]
> Sent: Thursday, June 19, 2003 2:57 PM
> To: [EMAIL PROTECTED]
> Subject: SQL
Hello all,
I am a totally newby to MYSQL but here i go.
I want to create an ASP page that shows an image (screendump of game) a game
name a game description and an url.
I created a database with the following table:
filename, urldesc, desc
Now i have to create an SQL query that will put this
ECTED]>; "MySQL" <[EMAIL PROTECTED]>
Sent: 28 March 2002 19:16
Subject: Re: SQL query question - using LIKE
> DL -
>
> Points well taken -
> I am using php and doing simple validation
> EX - if ($fname !="") {
> add fname string to search variabl
%"';
>> if($loan_officer)$query.= ', AND Loan_officer LIKE
>> "%'.$loan_officer.'%"';
>> etc.
>>
>> $mysql_result = mysql_query($query, $mysql_link);
>>
>> HTH
>>
>> Peter
>>
>> -----------
>
eter
>
> ---
> Excellence in internet and open source software
> ---
> Sunmaia
> www.sunmaia.net
> [EMAIL PROTECTED]
> tel. 0121-242-1473
> --
Excellence in internet and open source software
---
Sunmaia
www.sunmaia.net
[EMAIL PROTECTED]
tel. 0121-242-1473
---
> -Original Message-----
> From: Mark Stringham [mailto:[EMAIL PROTECTED]]
> Se
I have a simple search form that allows the user to search a contact db
based on criteria that they choose.
Search by -
first name - text box
last name - text box
region - drop down
loan officer - drop down
I want the user to be able to receive results if they choose all possible
criteria or jus
It's not possible to do in one step in MySQL.
--
Steve Werby
President, Befriend Internet Services LLC
http://www.befriend.com/
"roger westin" <[EMAIL PROTECTED]> wrote:
So a question
I have two tables. And i want to... (i just show you)
Table 1.
OwnerChar(30)Uniqe
FileChar
Hi there,
So a question
I have two tables. And i want to... (i just show you)
Table 1.
OwnerChar(30)Uniqe
FileChar(80)
OIDint(not in use yet)
Table 2.
IDintuniqe and so on
OwnerChar
NameChar
adress
etc
57 matches
Mail list logo
