Need help with what seems to be an easy SQL query....

[James Shao]

Hi guys, thanks for all the help recently, this is really a great forum!   :) Today I was trying to write what seems like a simple query statement, but unbelievably after hours of trying I still can't get it to work.   X| My table looks like the following: ID   Ticker SlopeofLine 1   MSFT   5 2   MSFT   2 3   MSFT   1 4   MSFT   0 5   MSFT   -3 6   MSFT   1 1   DELL   -2 2   DELL   -1 3   DELL   2 4   DELL   4 5   DELL   3 6   DELL   4 ... ... I want to retrieve, for each Ticker, the last/largest ID where SlopeofLine is less than 0.   So ideally, the query should retrieve this from the above table: ID   Ticker   SlopeofLine 5   MSFT      -3 2   DELL      -1 Please forgive me if this is a stupid question; I'd greatly appreciate any help. My query which doesn't work currently look like this:

Select Ticker, ID, SlopeOfLine
From Table
Where SlopeofLine < 0
AND ID = (SELECT MAX(ID) FROM Table t1 Where Table.Ticker = t1.Ticker)

[Luc Pattyn]

Hi, I would try:

Select Ticker, MAX(ID) as MAXID, SlopeOfLine
From Table
Where SlopeofLine < 0 GROUP BY Ticker

Not tested! [ADDED] Now tested, and considered incorrect: Ticker and MAX(ID) are fine, SlopeOfLine is not. conclusion: you need two selects. [/ADDED] :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

Happy New Year to all.
We hope 2010 soon brings us automatic PRE tags!
Until then, please insert them manually.

---

modified on Wednesday, January 13, 2010 8:22 PM

[loyal ginger]

You may want to try something like this:

select last(ID),Ticker,last(SlopeofLine) from
(select ID,Ticker,SlopeofLine from YourTableName where SlopeofLine <0 order by Ticker,ID)
group by Ticker;

Happy querying!

[i j russell]

USE [TempDB]
GO

CREATE TABLE #Tracker(
[Id] [int] NOT NULL,
[Ticker] [nvarchar](4) NOT NULL,
[SlopeOfLine] [int] NOT NULL,
)
GO

INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'MSFT', 5)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'MSFT', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'MSFT', 0)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'MSFT', -3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'MSFT', 1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'DELL', -2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'DELL', -1)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'DELL', 2)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'DELL', 4)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'DELL', 3)
INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'DELL', 4)
GO

SELECT Id, Ticker, SlopeOfLIne
FROM #Tracker t
WHERE Id = (SELECT TOP 1 Id
FROM #Tracker
WHERE Ticker = t.Ticker
AND SlopeOfLine < 0
ORDER BY Id DESC)
GO

DROP TABLE #Tracker
GO

[Lee Ludden]

CREATE TABLE #Tracker( [Id] [int] NOT NULL, [Ticker] [nvarchar](4) NOT NULL, [SlopeOfLine] [int] NOT NULL, ) GO INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'MSFT', 5) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'MSFT', 2) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'MSFT', 1) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'MSFT', 0) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'MSFT', -3) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'MSFT', 1) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (1, 'DELL', -2) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (2, 'DELL', -1) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (3, 'DELL', 2) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (4, 'DELL', 4) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (5, 'DELL', 3) INSERT INTO #Tracker (Id, Ticker, SlopeOfLIne) VALUES (6, 'DELL', 4) GO SELECT T1.Id, T1.Ticker, T1.SlopeOfLIne FROM #Tracker t1 LEFT OUTER JOIN (SELECT * FROM #Tracker WHERE SlopeOfLine<0) T2 ON T2.Ticker = t1.Ticker AND T2.Id > t1.Id WHERE T2.Id IS NULL AND t1.SlopeOfLine < 0 GO This may or may not be faster than the subquery method used above.

[James Shao]

Thanks for all the help guys.   I'll try each one of them soon!   :-D

[Niladri_Biswas]

Even this works

SELECT X.Id,X.Ticker,Y.SlopeOfLine FROM
(SELECT MAX(Id)Id,Ticker
FROM @Tracker
WHERE SlopeOfLine < 0
GROUP BY Ticker)X JOIN @Tracker Y ON X.Id = Y.Id AND X.Ticker = Y.Ticker

:)

Niladri Biswas

[James Shao]

Yes it works!   Thanks a lot.

[James Shao]

This works too!   Thanks Russell.