How to Select Columns that's exclusive of Group By columns

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)

How to Select Columns that's exclusive of Group By columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-08 : 10:28:40

Jafedo writes "I'm SQL Server Database Programming.
I have a table with following rows :

Code1 Code2 Date                     
----- ----- ---------- 
A     1     2001-10-06
A     2     2001-10-05
A     3     2001-10-04
B     1     2001-10-03
B     2     2001-10-02
C     1     2001-10-01

I want to appear value of columns 'Code1','Code2' that have recently date each group on 'Code1'

How does I write my 'select' command to retrieve rows that i want?"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-06 : 12:05:45

A "GROUP BY Code1" in the subquery might help.

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-06 : 12:55:43

or

SELECT A.Code1, A.Code2, A.Date
FROM myTable A
where A.Date = (select max(B.Date) from myTable B where A.Code1=B.Code1)

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-06 : 16:09:18

As far as I recall, you'll probably get the same query plan in SQL Server 2000. Certainly these two do.


USE Northwind


SELECT *
FROM Orders a
WHERE OrderDate = (
  SELECT MAX(OrderDate)
  FROM Orders b
  WHERE a.CustomerID = b.CustomerID)


SELECT a.*
FROM Orders a
INNER JOIN (
  SELECT CustomerID, MAX(OrderDate) AS MaxOrderDate
  FROM Orders
  GROUP BY CustomerID) b
 ON a.CustomerID = b.CustomerID AND a.OrderDate = b.MaxOrderDate

Microsoft Criticism: Segment operator isn't documented.

Edited by - Arnold Fribble on 12/06/2001 16:11:59

izaltsman
A custom title

1139 Posts

Posted - 2001-12-06 : 16:25:28

quote:

Microsoft Criticism: Segment operator isn't documented.

Please forigve my ignorance... What is "Segment operator"?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2001-12-07 : 05:12:48

The Segment operator is what SQL Server 2000's query processor (probably) uses as part of its query plan for the two queries I quoted. Rather than find the max of each and semi-join it with the table, it will give a query plan like this:


  |--Top(1)
       |--Segment
            |--Filter(WHERE:([a].[CustomerID]<>NULL AND [a].[OrderDate]<>NULL))
                 |--Sort(ORDER BY:([a].[CustomerID] DESC, [a].[OrderDate] DESC))
                      |--Clustered Index Scan(OBJECT:([Northwind].[dbo].
                         [Orders].PK_Orders] AS [a]))

This probably isn't particularly advantageous in this case since it need to build and sort a temporary to get the data in the right order for the Segment. If you try a similar query on "Order Details", where there's a clustered pk on (OrderID, ProductID), it can do it without the sort and filter:


SELECT *
FROM "Order Details" a
WHERE ProductID = (
  SELECT MAX(ProductID)
  FROM "Order Details" b
  WHERE a.OrderID = b.OrderID)

Gives a query plan:


  |--Top(1)
       |--Segment
            |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Order Details].
               [PK_Order_Details] AS [a]), ORDERED BACKWARD)

This is a pretty good approach if the number of ProductIDs for each OrderID is low. So far, I've only managed to get this description which came from "one of the SQL Server Developers" via Kalen Delaney:

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.

Edited by - Arnold Fribble on 12/07/2001 05:22:11

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources