select max brings back more than 1 row... 1

[Sniipe]

I have this code:

Select max(a.templateid) as templateid, a.questionid,  a.ANSWER, a.quoteid 
from PPT_Answer A, PPT_Question Q 
where A.QUESTIONID = Q.questionID 
and A.templateid = Q.templateid 
AND A.QuoteID='TCA0136327' 
and a.answercell = 'B15'
group by a.questionid, a.answer, a.quoteid

unfortunately it sometimes brings back more than 1 row for example:

TEMPLATEID	QUESTIONID					ANSWER	QUOTEID
532			EndPointsSingleLinePhonesIP	10		TCA0136327
545			EndPointsSingleLinePhonesIP	9		TCA0136327

I only want the row with the highest templateid brought back, which is what I thought max(a.templateid) would do?

 

[RiverGuy]

SELECT x.*
FROM
  (Select  a.templateid, a.questionid,  a.ANSWER, a.quoteid
  from PPT_Answer A, PPT_Question Q
  where A.QUESTIONID = Q.questionID
  and A.templateid = Q.templateid
  ) x
INNER JOIN
  (SELECT MAX(TemplateID) AS MaxID FROM PPT_Answer
  WHERE A.QuoteID='TCA0136327'
  and a.answercell = 'B15'
  ) y
ON x.TempladeID = y.MaxID

 

[r937]

it's doing exactly what you asked

you wanted the highest templateid for each unique combination of questionid, answer, quoteid



r937.com | rudy.ca

 

[PHV]

You may try this:

SELECT A.templateid, A.questionid, A.ANSWER, A.quoteid
FROM PPT_Answer A INNER JOIN (
Select max(B.templateid) AS templateid, B.questionid, B.quoteid 
from PPT_Answer B inner join PPT_Question Q 
on B.QUESTIONID = Q.questionID and B.templateid = Q.templateid
where B.QuoteID='TCA0136327' and B.answercell = 'B15'
group by B.questionid, B.quoteid
) M ON A.templateid = M.templateid AND A.questionid = B.questionid AND A.quoteid = B.QuoteID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Sniipe]

this would work... but I don't think its the best/right way to do it.

select * from 
(
Select max(a.templateid) as templateid, a.questionid,  a.ANSWER, a.quoteid 
from PPT_Answer A, PPT_Question Q 
where A.QUESTIONID = Q.questionID 
and A.templateid = Q.templateid 
AND A.QuoteID='TCA0136327' 
and a.answercell = 'B15'
group by a.questionid, a.answer, a.quoteid
Order By templateid desc
) where rownum = 1 [code]

 

[PHV]

rownum is not (AFAIK) standard.
Did you try my suggestion ?

 

[Sniipe]

sorry, didn't see any answers, just posted that last reply.

I tried yours and got ORA-00904: "B"."QUOTEID": invalid identifier.

Tried riverguys and got ORA-00904: "A"."ANSWERCELL": invalid identifier

 

[PHV]

Sorry for the typo, replace this:
) M ON A.templateid = M.templateid AND A.questionid = B.questionid AND A.quoteid = B.QuoteID
with this:
) M ON A.templateid = M.templateid AND A.questionid = M.questionid AND A.quoteid = M.QuoteID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SQLSister]

Since those are Oracle errors you might get better answers inthe ORacle forums.

"NOTHING is more important in a database than integrity." ESquared

 

[Sniipe]

almost there. with your query PHV;

SELECT A.templateid, a.answercell, A.questionid, A.ANSWER, A.quoteid
FROM PPT_Answer A 
INNER JOIN 
(
Select max(B.templateid) AS templateid, B.questionid, B.quoteid 
from PPT_Answer B inner join PPT_Question Q 
on B.QUESTIONID = Q.questionID 
and B.templateid = Q.templateid
where B.QuoteID='TCA0136327'
group by B.questionid, B.quoteid
) M 
ON A.templateid = M.templateid 
AND A.questionid = M.questionid 
AND A.quoteid = M.QuoteID

It brings back the expected values, but I want to add in 1 more column, which is PPT_Question.Screen

 

[Sniipe]

this is what I came up with... but it looks horrible...

select tbl1.templateid, tbl1.answercell, tbl1.questionid, tbl1.answer, tbl1.quoteid, tbl2.screen, tbl2.guilabel from 
(
SELECT A.templateid, a.answercell, A.questionid, A.ANSWER, A.quoteid
FROM PPT_Answer A
INNER JOIN 
(
Select max(B.templateid) AS templateid, B.questionid, B.quoteid 
from PPT_Answer B inner join PPT_Question Q 
on B.QUESTIONID = Q.questionID 
and B.templateid = Q.templateid
where B.QuoteID='TCA0136327'
group by B.questionid, B.quoteid
) M 
ON A.templateid = M.templateid 
AND A.questionid = M.questionid 
AND A.quoteid = M.QuoteID
) tbl1, PPT_Question tbl2
where tbl1.templateid = tbl2.templateid
and tbl1.questionid = tbl2.questionid

 

[PHV]

A simpler way:

SELECT A.templateid, a.answercell, A.questionid, A.ANSWER, A.quoteid, C.screen, C.guilabel
FROM PPT_Answer A 
INNER JOIN 
(
Select max(B.templateid) AS templateid, B.questionid, B.quoteid 
from PPT_Answer B inner join PPT_Question Q 
on B.QUESTIONID = Q.questionID 
and B.templateid = Q.templateid
where B.QuoteID='TCA0136327'
group by B.questionid, B.quoteid
) M 
ON A.templateid = M.templateid 
AND A.questionid = M.questionid 
AND A.quoteid = M.QuoteID
INNER JOIN PPT_Question C
ON A.templateid = C.templateid
AND A.questionid = C.questionid

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Sniipe]

Ahh I see what u did there PHV, I assume both do the exact same thing tho? Perhaps computationally yours is quicker?

 

[MarcLodge]

Not sure about ANSI SQL, but in DB2 I would do this:

Select max(a.templateid) as templateid, a.questionid,  a.ANSWER, a.quoteid 
from PPT_Answer A, PPT_Question Q 
where A.QUESTIONID = Q.questionID 
and A.templateid = Q.templateid 
AND A.QuoteID='TCA0136327' 
and a.answercell = 'B15'
group by a.questionid, a.answer, a.quoteid
order by templateid desc
fetch first 1 row only

Marc

 

[r937]

how many rows would [tt]fetch first 1 row only[/tt] return in total?



r937.com | rudy.ca

 

[MarcLodge]

r937,
the outcome of execution of the sql is one row.
Marc

 

[PHV]

Marc, how would your suggestion work if you want different questionid and/or quoteid ?

 

[r937]

marc, it appears that the intent was to obtain the max for each combination of unique values in the GROUP BY clause

otherwise there wouldnta been a GROUP BY clause ;-)

r937.com | rudy.ca

 

[MarcLodge]

r937,
I don't think snipe wants the max for each combination of unique values in the group by. If you look at his first post, he is querying the fact that he gets two rows back, when he only requires one. If you then look at his 2nd post, he is attempting to order by descending on the max column and bring back just the first row - which is what my supplied db2 sql does.

PHV,
my suggestion wouldn't work for a different questionid/quoteid, but I don't believe that is the question that snipe is asking.

snipe, are you able to comment on this?

Marc

 

[Sniipe]

Hey Guys, I was looking for the most recent result which will always be the one with the higest templateid. I do only require 1 value.

The quoteid's will always remain the same. There could be 100+ questionID's involved in the results.