Max Date record without subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Max Date record without subquery

Author

Topic

augustin_p
Starting Member

21 Posts

Posted - 2002-04-02 : 23:25:34

Hi,
how do i fetch maximum date's record without using subquery.

Thanks,
prasanna

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-02 : 23:28:34

Well, uh, this isn't really enough information for us to help.

And what's wrong with a subquery? SQL Server supports them very nicely.

augustin_p
Starting Member

21 Posts

Posted - 2002-04-03 : 03:59:39

quote:

Well, uh, this isn't really enough information for us to help.

And what's wrong with a subquery? SQL Server supports them very nicely.

Hi,
i need to display the information for each item in Item Master about the last receipt of each item. How do i do this without a subquery?
Doesnt SubQuery affect performance as it has to execute each subquery each time instead of executing a Statement at one shot.

Thanks,
prasanna

Teroman
Posting Yak Master

115 Posts

Posted - 2002-04-03 : 04:06:43

Subqueries arent too bad, and if you want to do what you're saying you dont have a lot of choice, but I'd just use a derrived table

select *
from ItemMaster IM
join ( select ItemID, max(ItemDate) as ItemDate from ItemMaster Group by ItemID) D
on IM.ItemID = D.ItemID
and IM.ItemDate = D.ItemDate

should work ok, if thats what you are trying to achieve

col

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-03 : 05:27:00

quote:

Doesnt SubQuery affect performance as it has to execute each subquery each time instead of executing a Statement at one shot.

No. Compare the query plans for these two queries on Northwind:


USE NORTHWIND
GO

SELECT *
FROM Orders o
WHERE OrderDate = (
    SELECT MAX(OrderDate) OrderDate
    FROM Orders omax
    WHERE o.CustomerID = omax.CustomerID)

SELECT o.*
FROM Orders o
INNER JOIN (
    SELECT CustomerID, MAX(OrderDate) OrderDate
    FROM Orders
    GROUP BY CustomerID) omax
  ON o.CustomerID = omax.CustomerID
    AND o.OrderDate = omax.OrderDate

Edited by - Arnold Fribble on 04/03/2002 05:29:33

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-03 : 06:07:29

hi

i have tried to understand the query plan but not up to my satsfication.... but the both the query plan look same....
any tips to understand better...

======================================
Ask to your self before u ask someone

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-03 : 06:41:39

They are the same. I'd forgotten that those queries generate (Sort,) Segment and Top plans on a table that small. Unfortunately, the Segment operator isn't documented.

My second-hand information on this is that, "Segment is dividing the input set into "segments" based on the value of one or more columns. This column(s) is shown as "argument" in the Segment operator. The operator then outputs one segment at a time. In your case there is a TOP above it fetching the top most (or more if there is a tie)
row from each segment."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources