CODEWITHSUNDEEP
sql-servert-sql
xkcd
xkcd

Reputation: 2590

Getting the first occurence of rows

I have a table created by the following t-sql statement:

CREATE TABLE [Core].[PriceHistory](
    [PriceHistoryId] [bigint] IDENTITY(1,1) NOT NULL,
    [SourceId] [tinyint] NOT NULL,
    [SymbolId] [smallint] NOT NULL,
    [Ask] [real] NOT NULL,
    [Bid] [real] NOT NULL,
    [TickTime] [bigint] NOT NULL,
    [ModifiedDate] [datetime2](3) NOT NULL,
    [Direction] [tinyint] NULL,
CONSTRAINT [PK_PriceHistory] PRIMARY KEY CLUSTERED 
(
[PriceHistoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Let's say I have a list of SymbolIds, for example (1, 2, 3).

I want to get the first rows foreach SymbolId having ModifiedDate is > than '2016-04-01 00:00:00'

Upvotes: 1

Views: 39

Answers (1)

Abdul Rasheed
Abdul Rasheed

Reputation: 6719

SELECT *
FROM (
    SELECT DENSE_RANK() OVER (
            PARTITION BY SymbolId ORDER BY ModifiedDate
            ) RNK
        ,*
    FROM PriceHistory
    WHERE ModifiedDate > '2014-04-01 00:00:00'
    ) T
WHERE RNK = 1

Upvotes: 2

Related Questions