July 24, 2012 at 2:10 am
I have the following table of data, and I want to get each of the latest values by date and then by time for each of the symbols:
IDxSymbolxDatexTimexClose
1MSFT10089100.5000
2MSFT10091101.5000
3MSFT10093102.6000
4MSFT10171101.3000
5MSFT10176101.2000
6MSFT10177102.5000
7INTC1008931.5000
8INTC1009130.2000
9INTC1009329.3000
10AMD103231200.1000
11AMD103234201.3000
12AMD103240200.2000
13AMD105343199.6000
14AMD105345203.2000
IDxSymbolxDatexTimexClose
1MSFT10177102.5000
2INTC1009329.3000
3 AMD105345203.2000
To get the latest value for any one symbol is easy enough but I'm stumped on getting all the values at once. Can someone help?
-- get latest value for any one symbol
SELECT Top 1 xSymbol, xClose, xDate AS iLastDate, xTime AS iLastTime FROM StockQuote
WHERE xSymbol = 'MSFT' ORDER BY xDate DESC, xTime DESC;
here's a setup of the data
CREATE DATABASE StockQuoteDB2
USE StockQuoteDB2
CREATE TABLE [dbo].[StockQuote](
[ID] [int] IDENTITY(1,1) NOT NULL,
[xSymbol] [varchar](10) NOT NULL,
[xDate] [int] NOT NULL,
[xTime] [int] NOT NULL,
[xClose] [decimal](18, 4) NOT NULL,
)
-- Put some data into the table
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('MSFT', 100, 89, 100.5)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('MSFT', 100, 91, 101.5)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('MSFT', 100, 93, 102.6)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('MSFT', 101, 71, 101.3)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('MSFT', 101, 76, 101.2)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('MSFT', 101, 77, 102.5)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('INTC', 100, 89, 31.5)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('INTC', 100, 91, 30.2)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('INTC', 100, 93, 29.3)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('AMD', 103, 231, 200.1)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('AMD', 103, 234, 201.3)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('AMD', 103, 240, 200.2)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('AMD', 105, 343, 199.6)
INSERT INTO StockQuote (xSymbol, xDate, xTime, xClose) VALUES ('AMD', 105, 345, 203.2)
--SELECT * FROM StockQuote;
-- get latest value for any one symbol
SELECT Top 1 xSymbol, xClose, xDate AS iLastDate, xTime AS iLastTime FROM StockQuote WHERE xSymbol = 'MSFT' ORDER BY xDate DESC, xTime DESC;
-- InnerJoin attempt
SELECT B.xSymbol, B.xDate, B.xTime FROM StockQuote B
INNER JOIN
(SELECT xDate, MAX(xTime) AS MaxBar
FROM StockQuote
GROUP BY xSymbol, xDate, xTime, xClose
) q
ON q.xDate = B.xDate AND q.MaxBar = B.xTime
July 24, 2012 at 2:25 am
Hi, Something like this..
SELECT A.XSYMBOL,A.XCLOSE,A.XDATE AS ILASTDATE,A.XTIME AS ILASTTIME FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY XSYMBOL ORDER BY XDATE DESC, XTIME DESC) [RANK]
FROM STOCKQUOTE)A
WHERE [RANK] = 1
Regards,
Karthik.
SQL Developer.
July 24, 2012 at 2:31 am
Karthiart (7/24/2012)
Hi, Something like this..SELECT A.XSYMBOL,A.XCLOSE,A.XDATE AS ILASTDATE,A.XTIME AS ILASTTIME FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY XSYMBOL ORDER BY XDATE DESC, XTIME DESC) [RANK]
FROM STOCKQUOTE)A
WHERE [RANK] = 1
Thanks, That works! I've never used PARTITION before, so I never would have thought of that.
July 25, 2012 at 12:05 pm
I want to extend my query to get the Minimum Date and Time with Maximum Date and Time
ID xSymbol MaxDate MaxTime MinDate MinTime
1 MSFT 101 77 100 89
2 INTC 100 93 100 89
3 AMD 105 345 103 231
How to combine the following max and min on one line?
SELECT A.XSYMBOL, A.XDATE, A.XTIME, A.RANKMAX, A.RANKMIN FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY XSYMBOL ORDER BY XDATE DESC, XTIME DESC) AS [RANKMAX],
ROW_NUMBER() OVER (PARTITION BY XSYMBOL ORDER BY XDATE ASC, XTIME ASC ) AS [RANKMIN]
FROM STOCKQUOTE) A
WHERE A.[RANKMAX] = 1 OR A.[RANKMIN] = 1
Order by A.xSymbol
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply