CODEWITHSUNDEEP
sqlsql-server
user2202098
user2202098

Reputation: 860

Select top 2 prices for each symbol

i am wanting to grab the top two recent prices for each symbol in table below

if i wanted only the most recent price i would "select distinct symbol, price from table_1 order by date, time"

getting two most recent prices is leaving me a little stuck

CREATE TABLE [dbo].[Table_1](
[symbol] [nchar](15) NOT NULL,
[price] [float] NULL,
[date] [date] NOT NULL,
[time] [time](7) NOT NULL,
) ON [PRIMARY]

Upvotes: 2

Views: 109

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

In SQL Server, you can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by symbol order by date desc, time desc) as seqnum
      from table_1 t
     ) t
where seqnum <= 2;

As a note. To get the most recent price, you would not do:

select distinct symbol, price
from table_1
order by date, time;

This would generate a syntax error, because date and time are not in the select clause. If you removed the order by, you would simply get a list of all distinct symbol/price pairs.

Upvotes: 4

knightRider
knightRider

Reputation: 153

How about select top 2 symbol, price from table_1 order by date, time ?

Upvotes: 0

Related Questions