StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUsing GROUP-BY-HAVING on two joined tables
text
Body
copied!<p>I'm trying to join two tables, and select columns from both based on where constraints as a well as a group-by-having condition. I'm experiencing some issues and behavior that I do not understand. I'm using sybase. A simple example below</p> <pre><code>CREATE TABLE #test( name varchar(4), num int, cat varchar(3) ) CREATE TABLE #other( name varchar(4), label varchar(20) ) Insert #test VALUES('a',2,'aa') Insert #test VALUES ('b',2,'aa') Insert #test VALUES ('c',3,'bb') Insert #test VALUES ( 'a',3,'aa') Insert #test VALUES ( 'd',4,'aa') Insert #other VALUES('a','this label is a') Insert #other VALUES ('b','this label is b') Insert #other VALUES ('c','this label is c') Insert #other VALUES ( 'd','this label is d') SELECT t.name,t.num,o.label FROM #other o inner JOIN #test t ON o.name=t.name WHERE t.name='a' GROUP BY t.name HAVING t.num=MAX(t.num) </code></pre> <p>I get non-sense when I have the <code>GROUP BY</code> (the label columns are clearly related to a different t.name). If I cut out the <code>GROUP BY</code> statement the query behaves as I would expect, but then I am forced to use this as a subquery and then apply </p> <pre><code>SELECT * FROM (subquery here) s GROUP BY s.name having s.num=MAX(s.num) </code></pre> <p>There has to be a better way of doing this. Any help and explanation for this behavior would be very appreciated.</p> <p>**I should clarify. In my actual query I have something like SELECT .... FROM (joined tables) WHERE name IN (long list of names), GROUP BY .....</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