Window Functions, or the OVER() Clause
It appears that what you are looking for is a window function or an OVER()
clause.
In your original example, you are trying to use two max()
conditions, which doesn't work, because when you try to then apply GROUP BY
, you can have a condition where the max of the first column ID
and the second column startdate
aren't in the same row, and so then GROUP BY
simply can't be understood.
However, if you are looking to extract the max over a grouping, and you can define that grouping, and you want to obtain the 'column-wise' maximum for more than one column for that grouping (as you seem to want to do), then here's the solution.
CREATE TABLE test (
jobcode integer,
id integer,
a_startdate datetime,
a_enddate datetime,
b_startdate datetime,
b_enddate datetime);
INSERT INTO test VALUES (513801, 7136, '11-01-2011', '12-31-9998', '11-01-2011', '12-31-9998');
INSERT INTO test VALUES (513801, 7137,'04-26-2014', '12-31-9998','04-26-2014', '12-31-9998');
I first made this table to recreate your input data in the picture. I then applied this query.
SELECT jobcode, max(id) OVER (PARTITION BY jobcode),
max(a_startdate) OVER(PARTITION BY jobcode),
a_enddate, b_startdate, b_enddate FROM test;
Try out this SQL Fiddle, and see if it gives the results you're looking for. I did the best I could with the description I had.
Your first query works as an inner join
because the y.TransType = 'used'
condition which uses the right table is in the where
clause.
Your second query can be rewritten without derived tables by simply moving that condition to the on
clause:
select x.*, y.*
from factI as x
left join factI as y on x.tickedId = y.tickedId
and y.TransType = 'used'
where x.TransType = 'sold' ;
Best Answer
As JNK said, for an
INNER JOIN
, these are going to be the same. You can prove it by comparing actual (don't bother with estimated) execution plans. In this case, they are exactly the same (click to enlarge):Personally, I like to keep the join conditions and the filter conditions separate. Join conditions go in the
ON
clause, and filter conditions go in theWHERE
clause. This is one of the primary benefits of having explicitINNER JOIN
syntax in the first place, which helps to reduce the risk of returning too many rows because of not enough (or even no) join criteria inherent in old-style joins (where filter and join criteria are thrown together) - see this blog post for more details.JNK is also right though that you need to be careful when you're talking about
OUTER JOIN
. A simple example:Results:
As you can see from the results, the first query still returns all three rows from
a
(as you would expect), however the second query turns theOUTER JOIN
into anINNER JOIN
, and only returns the rows froma
with a match fromb
on all conditions. You may want one behavior or the other, so neither of these is "worse" or "wrong," it's just important to understand the different functionality so you know to write the query to get the results you are after.