Please help with a query 1

[skiflyer]

I claim ignorance of Friday morning...

I have three tables like so.

Table1
pid: primary key
product: one of a list of names (prod1, prod2, prod3)
filename: free text

Table2
sid: primary key
stage: one of a list of stages (ib, cr, pr)

Table3
pid: foreign key
sid: foreign key
timestamp:

Now, I want to grab all the products which are currently in a certain state. Say all the prod1's in cr, and I'm just being an idiot trying to make this query. I can of course easily get all the products that were -ever- in a certain state.

SELECT table1.pid, table1.name
FROM table1, table2, table3
WHERE table1.pid = table3.pid 
  AND table2.sid = table3.sid 
  AND table2.stage =  'cr'

But, I want to also say, MAX(table3.timestamp) somewhere, ugh, this isn't hard, but like I said it's early friday

Thanks for any guidance.

 

[skiflyer]

So here's where I am now, this gives me a list of all the products and one of the columns returned is the state. Guess this is ok, then I can just loop through them in my program and grab the ones I want... perhaps this is where I'm going to be annoyed that I don't have sub-queries in my version of mySQL yet?

SELECT table2.state, table3.sid, table1.pid, table1.name, MAX(table3.timestamp)
FROM table1, table2, table3
WHERE table1.pid = table3.pid 
  AND table2.sid = table3.sid
GROUP BY table3.pid

 

[ceco]

SELECT table2.state, table3.sid, table1.pid, table1.name, MAX(table3.timestamp) AS max_timestamp
FROM table1, table2, table3
WHERE table1.pid = table3.pid
AND table2.sid = table3.sid
GROUP BY table3.pid HAVING max_timestamp = CURDATE

the condition in HAVING you should define it better for your needs because I don't realy understand what should be done ;-( sorry, but I think this is what you need

 

[skiflyer]

Ah, so close, my second query actually already took care of the timestamp problem, so what I want is

SELECT table2.state AS [red]curstate[/red], table3.sid, table1.pid, table1.name, MAX(table3.timestamp)
FROM table1, table2, table3
WHERE table1.pid = table3.pid
AND table2.sid = table3.sid
GROUP BY table3.pid HAVING [red]curstate = 'cr'[/red]

My two approaches must've been confusing, but you got me so close that I have to give you a star , thanks!

-Rob

 

[swampBoogie]

Yes, you need support for (correlated) subselect/derived tables. Using group by (especially with the Mysql implementation) gives the wrong result.

The alternative is to use a temorary table.

 

[skiflyer]

I disagree swampBoogie, I've implemented the above query (mine with the HAVING applied to the current state) and after a handful of tests everything comes up correct, am I missing something?

-Rob

 

[swampBoogie]

I assume you can have the following data

table1

pid product
1 p1
2 p2

table2

sid stage
1 ib
2 cr
3 pr

Table3
pid sid timestamp
1 1 <1>
1 2 <2>
1 3 <3>
2 1 <4>
2 2 <5>

and that you want to get p2 only.


With the query

SELECT table2.state AS curstate, 
       table3.sid, 
       table1.pid, 
       table1.name, 
       MAX(table3.timestamp)
  FROM table1, table2, table3
 WHERE table1.pid = table3.pid
   AND table2.sid = table3.sid
 GROUP BY table3.pid 
 HAVING curstate = 'cr'

there is no certainty that the values table3.sid and MAX(table3.timestamp) are in the same record.

Look at this link which describes how to do it with a temp table.

http://www.mysql.com/documentation/...utorial.html#example-Maximum-column-group-row

 

[skiflyer]

But that's not how aggregate operators work in SQL. They operate on items in the GROUP BY clause, not the entire table. So I've already done a group by to group things by their pid's, now I'm grabbing the one record in each group which has the maximum timestamp in that group.

-Rob

 

[swampBoogie]

Aggregate functions operates on columns not records. Mysql's implementation of group by is erroneous as it allows the type of queries that you are using here.

 

[skiflyer]

Yes, it operates on columns within a group... I don't see how this leads you to your conclusion that group by is erroneously implemented. let me step through the ideas and think outloud.

We'll use your above example.

SELECT pid, timestamp
FROM table3

returns
1, <1>
1, <2>
1, <3>
2, <4>
2, <5>

Then
SELECT pid, timestamp
FROM table3
GROUP BY pid

returns
1, <1 or 2 or 3 (all are correct)>
2, <4 or 5>

But now
SELECT pid, MAX(timestamp)
FROM table3
GROUP BY pid

returns
1, <3>
2, <5>

NOT
1, <5>
2, <5>
because it's working within the GROUPs.

I forget all the lingo for this, but I'm fairly certain I've stated it properly. Please correct any errors in my logic.

-Rob

 

[swampBoogie]

The problem with group by in Mysql can be seen from this example

Table3
pid sid timestamp
1 1 <1>
1 2 <2>
1 3 <3>
2 1 <4>
2 2 <5>

SELECT pid, sid, max(timestamp)
FROM table3
GROUP BY pid

The above query may return

pid sid timestamp
1 1 <3>
2 1 <5>

You can not assume that it will return

pid sid timestamp
1 3 <3>
2 2 <5>

To produce the above result with one you need to use a subselect or a derived table, which is what I meant by
my first post.

I.e. Mysql selects an arbitrary value for sid within each group. You don't know what value you'll get, it is possible that if you add an order by pid desc you will get another value for sid.


SELECT pid, timestamp
FROM table3
GROUP BY pid

returns
1, <1 or 2 or 3 (all are correct)>
2, <4 or 5>

I don't see why this should be deemed correct.

 

[skiflyer]

Ok, I see the first you argument there. About not relying on the third column coming along for the ride. I'll need to work on that.

Why don't you see the last part as correct though? It's actually the same argument you're making above.

-Rob

 

[swampBoogie]

The point is that the result of a query should only depend on what data you have, it should not be let to the arbitrariness of the DBMS.

 

[r937]

mysql do at least admit in their documentation that their &quot;feature&quot; is not standard

Don't use this feature if the columns you omit
from the GROUP BY part aren't unique in the
group! You will get unpredictable results.

the bold emphasis is theirs


rudy