How to write a SQL query that generate values based on data in the table

I have a table that looks like the table below and I need to make a query that shows the previous price of the Stock

Stock_ID   | Stock_Code | Price | From_date  | To_date
-----------+------------+-------+------------+-----------
1          | XYZ        | 71    | 2013-01-05 | 2013-01-06
1          | XYZ        | 72    | 2013-01-07 | 2013-01-08
1          | XYZ        | 74    | 2013-01-09 | 2013-01-10
2          | QWE        | 24    | 2013-01-05 | 2013-01-06
2          | QWE        | 22    | 2013-01-07 | 2013-01-08
2          | QWE        | 30    | 2013-02-09 | 2013-01-10

The query should resulted in something like this:

Stock_ID   | Stock_Code | Price | From_date  | To_date   | Previous_Price
-----------+------------+-----+--------------+---------------------------
1          | XYZ        | 71    | 2013-01-05 | 2013-01-06| null
1          | XYZ        | 72    | 2013-01-07 | 2013-01-08| 71
1          | XYZ        | 74    | 2013-01-09 | 2013-01-10| 72
2          | QWE        | 24    | 2013-01-05 | 2013-01-06| null
2          | QWE        | 22    | 2013-01-07 | 2013-01-08| 24
2          | QWE        | 30    | 2013-02-09 | 2013-01-10| 22

what I tried:

SELECT *, 
(SELECT Price 
WHERE To_date in (SELECT DATEADD(day, -1, From_date) from StockTable))
FROM StockTable

However, the order is incorrect and the null is showed in the bottom. I suspect this is because it's a totally separate query and the second query does not directly uses the date from the SELECT * table.

I included Order By as well as suggested by one of the comments, but it still doesn't work.

Is it possible to do so without creating a new table and use join? What can I do to obtain the result?

Perhaps the window function lag() over() would be a good fit here

Select * 
      ,Prev_Price = lag(Price) over (partition by Stock_ID order by From_date)
 from YourTable

Results

A first principles approach is to simply use an inline query to select the previous record. It not a generally efficient solution but if the dates are contiguous and there are not many records for each Stock_Code then the performance might be acceptable:

SELECT s.Stock_ID, s.Stock_Code, s.Price, s.From_date, s.To_date
       , (SELECT Price 
          FROM StockTable lst
          WHERE lst.Stock_ID = s.Stock_ID 
            AND lst.Stock_Code = s.Stock_Code
            AND lst.To_date = (
                SELECT MAX(To_date)
                FROM StockTable mx
                WHERE mx.Stock_ID = s.Stock_ID 
                AND mx.Stock_Code = s.Stock_Code
                AND mx.To_date < s.To_Date
            )
          ) as Previous_Price
FROM StockTable s

You could however simplify this with a simple LAG window query:

SELECT Stock_ID, Stock_Code, Price, From_date, To_date
       , LAG (Price, 1, 0) 
         OVER (PARTITION BY Stock_ID, Stock_Code
               ORDER BY To_Date)
         as Previous_Price
FROM StockTable s

Try this fiddle for proof: http://sqlfiddle.com/#!18/a4372/2

I'm not sure that is the best solution, but:

create table #temp(
Stock_ID INT,
Stock_Code NChar(5),
Price INT,
From_Date DATE,
To_Date DATE
)

INSERT INTO #temp VALUES
(1, 'XYZ', 71, '2013-01-05', '2013-01-06'),
(1, 'XYZ', 72, '2013-01-07', '2013-01-08'),
(1, 'XYZ', 74, '2013-01-09', '2013-01-10'),
(2, 'QWE', 24, '2013-01-05', '2013-01-06'),
(2, 'QWE', 22, '2013-01-07', '2013-01-08'),
(2, 'QWE', 30, '2013-01-09', '2013-01-10')    
SELECT
        t1.Stock_ID, 
        t1.Stock_Code, 
        t1.Price, 
        t1.From_Date, 
        t1.To_Date,
        (SELECT
            t3.Price [Previouse_Price]
        FROM
            #temp t3
        INNER JOIN
            (SELECT
                MAX(t2.From_Date) [Previouse_Date]
            FROM
                #temp t2 
            WHERE 
                t1.Stock_ID = t2.Stock_ID 
                AND 
                t1.Stock_Code = t2.Stock_Code 
                AND
                t1.From_Date > t2.From_Date
            ) A ON A.Previouse_Date = t3.From_Date
        WHERE
            t1.Stock_ID = t3.Stock_ID 
            AND 
            t1.Stock_Code = t3.Stock_Code)
    FROM #temp t1