Question on some joins
-
Am I just making this way harder than it needs to be? I have a table represented by the following sample data.
CREATE TABLE #tbl1(ID INT IDENTITY (1, 1) NOT NULL , orderid INT, orderdata VARCHAR(50), datemodified datetime)
INSERT INTO #tbl1 (orderid,orderdata,datemodified) VALUES (1,'olddata','01/10/2007') INSERT INTO #tbl1 (orderid,orderdata,datemodified) VALUES (1,'newdata','01/11/2007') INSERT INTO #tbl1 (orderid,orderdata,datemodified) VALUES (2,'newdata2','01/15/2007') INSERT INTO #tbl1 (orderid,orderdata,datemodified) VALUES (2,'olddata2','01/10/2007')
The data I'm looking for can be represented by the following query.
SELECT t2.orderid, t2.orderdata
FROM #tbl1 t2
INNER JOIN
(SELECT t1.ORDERid,MAX(t1.datemodified)AS maxmoddate
FROM #tbl1 t1 GROUP BY t1.ORDERid) t3
ON t2.orderid = t3.orderid AND t2.datemodified = t3.maxmoddate ORDER BY t2.orderid ASC
The question I have is, this seems like the very long way to do this, especially since I'm talking about a decent number of records inthis table. Anyone else have ideas on how to do this without jumping through so many hoops?
tia
-Luke.
-
Luke, I would have to disagree with you and say that this is not the long way; rather, this is the short way as many would feel inclined to use a cursor to accomplish what you've just done with a derived table. I guess I'm a bit confused at why you feel this is jumping through hoops? I would recommend checking your execution plan for efficiency, but besides that, what you've come up with is an effective, SET based way to get the newest rows (by datemodified) from your table.
-
Thanks for the vote of confidence. I guess it just seemed sort of strange to me to be referencing the same table by 3 diferent names... t1,t2,t3. Obviously with no indexes in my sample code the execution plan is rather odd, but we'll see how it goes...
I guess the reason I'm not a big fan of seeing this is that this is a portion of an already complex Select statement and I just needed to join to the result of this select statement as another derived table. so it ends up being a table joined to a derived table, that is made up of another derived table etc... It just looked rather odd I guess.
Thanks again.
-Luke.
-
As far as you table aliases go, keep in mind that the table and column aliases have scope in that the alias of t1 that you are using inside the derived table could be used again in the main select. You could also forgo the use of the alias within the derived table as only one table is referenced in there. This may help with making the multiple alias names less confusing. Here is an example of your queries written both ways that I referenced:
SELECT t1.orderid, t1.orderdata
FROM #tbl1 t1
INNER JOIN
(SELECT t1.ORDERid,MAX(t1.datemodified)AS maxmoddate
FROM #tbl1 t1 GROUP BY t1.ORDERid) t2
ON t1.orderid = t2.orderid AND t1.datemodified = t2.maxmoddate ORDER BY t1.orderid ASC
SELECT t1.orderid, t1.orderdata
FROM #tbl1 t1
INNER JOIN
(SELECT ORDERid,MAX(datemodified)AS maxmoddate
FROM #tbl1 GROUP BY ORDERid) t2
ON t1.orderid = t2.orderid AND t1.datemodified = t2.maxmoddate ORDER BY t1.orderid ASC
-
Yeah, that's true. I didnt' really think about the scope aspects of it. As for not using them, unfortunately the individual who designed the database I must now support liked really long table names, so I end up using alliases for just about everything just out of pure laziness. I have a personal presference to always referencing table names explicitly instead of having to deal with what table does that colrefer to again, again laziness...
When you aren't referencing the same table multiple times they work out very handily, and I don't use t1,t2 except in very general examples like this one. I use abreviations that actually mean something to me.
Thanks for the thoughts
-Luke.
-
A slightly different approach that avoids grouping, and only has two instances of the same table.
Whether that makes any difference in performance is another question
SELECT t1.orderid, t1.orderdata
FROM #tbl1 t1
WHERE NOT EXISTS (
SELECT *
FROM #tbl1 t2
WHERE t1.orderid = t2.orderid
AND t1.datemodified < t2.datemodified )
-
dis 1 seems to b simple -
select distinct t1.orderid , t1.orderdata
from #tbl1 t1
inner join #tbl1 t2
on t1.datemodified > t2.datemodified
group by t1.orderid , t1.orderdata
-
Yep - but simple can be tricky
A couple of minor items....
This should also be joined on orderid to meet the requirements (although the sample data provided returns the same result set in this case without that join condition). And your method will exclude any records that have only one row for an orderid, rather than returning the lastest (or only) row for an orderid.
If this assumed requirement to return the lastest/only row is accurate, then a bit more complicated solution is indeed needed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply