StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelect an aggregate column from 2 views
text
Body
copied!<p>I have two query for views: </p> <p>First view:</p> <pre><code>SELECT t1.entity_id, t1.entity_name, t2.order1, t2.order2, t2.order3, t2.order4, t3.date, t2.score FROM table1 t1 LEFT JOIN table2 t2 ON t1.entity_id = t2.entity_id LEFT JOIN table3 t3 ON t2.code = t3.code WHERE t1.entity_id = 1 </code></pre> <p>Second view:</p> <pre><code>SELECT t1.entity_id, t1.entity_name, t2.order1, t2.order2, t2.order3, t2.order4, max(t3.date) as 'date' FROM table1 t1 LEFT JOIN table2 t2 ON t1.entity_id = t2.entity_id LEFT JOIN table3 t3 ON t2.code = t3.code WHERE t1.entity_id = 1 GROUP BY t1.entity_id, t1.entity_name, t2.order4, t2.order3, t2.order2, t2.order1 </code></pre> <p>The problem that come to me is when I'm trying to select the final score with the max date, it comes with the error:</p> <blockquote> <p><em>Column 'dbo.t2.score' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</em></p> </blockquote> <p>I'm stuck to get the score with the latest date without using any aggregate for the score. I've tried so many ways but I couldn't figure it out to get the score with the latest date <br /><br> How do I get the score with this condition (after checking order1 to order4 when it's the same):</p> <ul> <li>When the date is different, pick the latest score</li> <li>When the date is same, pick the max score</li> </ul> <p>Is it possible to do that? </p> <p>How can I get the <code>entity_id</code> and <code>score</code> with the condition above? </p> <h2>UPDATE</h2> <p>The result of view1: </p> <pre><code>entity_id | entity_name | order1 | order2 | order3 | order4 | date | score ================================================================================ 1 | entity 1 | aaa | rrr | eee | NULL | 2012-12-15 | 2 1 | entity 1 | aaa | rrr | eee | NULL | 2012-11-01 | 5.3 1 | entity 1 | bbb | sss | ttt | ggg | 2012-11-16 | 1.5 1 | entity 1 | ccc | sss | xxx | NULL | 2012-12-15 | 2.3 </code></pre> <p>The result of view2:</p> <pre><code>entity_id | entity_name | order1 | order2 | order3 | order4 | date ======================================================================== 1 | entity 1 | aaa | rrr | eee | NULL | 2012-12-15 1 | entity 1 | bbb | sss | ttt | ggg | 2012-11-16 1 | entity 1 | ccc | sss | xxx | NULL | 2012-12-15 </code></pre> <p>The result I want: </p> <pre><code>entity_id | entity_name | order1 | order2 | order3 | order4 | date | score ================================================================================ 1 | entity 1 | aaa | rrr | eee | NULL | 2012-12-15 | 2 1 | entity 1 | bbb | sss | ttt | ggg | 2012-11-16 | 1.5 1 | entity 1 | ccc | sss | xxx | NULL | 2012-12-15 | 2.3 </code></pre> <p>I have resolved this problem.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload