Selecting two rows by date

[manhattes]

I have a table  of prices by date and I am trying to select two rows, the 2nd row and a fixed row.

It the below statement I am trying to get the date less than the current date but it is returning mixed results.

 

How can I get just the row for .$Hist['BuyDateSubmit']. and the row with the date which is on the 2nd row from the top?

$HistQ = "select * from TABLE where Date = '".$Hist['BuyDateSubmit']. "' AND (Date <'". $XXX . "' OR Date = '" . $Hist['BuyDateSubmit'] ."')";

[Barand]

Do you mean something like this?

SELECT * FROM 
    (
    SELECT date
    , price
    , @seq:=@seq+1 as seq
    FROM tablename
		JOIN (SELECT @seq:=0) as init
	ORDER BY date DESC
    ) sequence
 WHERE seq = 2 OR date = '$XXX'

[edit]

This does seem an odd solution to a problem. What is the actual situation and the problem you are trying to solve?

Edited October 29, 2015 by Barand

[manhattes]

Do you mean something like this?

SELECT * FROM 
    (
    SELECT date
    , price
    , @seq:=@seq+1 as seq
    FROM tablename
		JOIN (SELECT @seq:=0) as init
	ORDER BY date DESC
    ) sequence
 WHERE seq = 2 OR date = '$XXX'

[edit]

This does seem an odd solution to a problem. What is the actual situation and the problem you are trying to solve?

I have a table with historical pricing info and it is a table that I have to update manually so the most recent date wont always be the current date.

Because it may not be updated, I just want the newest entry of Table.Close and another date that is represented in a variable.

 

The above code output:

Date

2015-10-16

Close

0.28

seq

2

 

 

I need this output and another row which is an exact match to be the output.

Edited October 29, 2015 by manhattes

[Barand]

If you want historical pricing then you want a price table like

price       DECIMAL(10,3),
valid_from  DATE,
valid_to    DATE

The current price would have valid_to date of 9999-12-31. When you add a new price from, say, tomorrow, then the current price valid_to is set to today and the new price becomes the current_price (from tomorrow until 9999-12-31)

 

You then match your (old) items like this

SELECT 
  item_desc
, qty
, price
FROM item INNER JOIN prices ON item.date BETWEEN prices valid_from AND prices.valid_to

[manhattes]

 

If you want historical pricing then you want a price table like

price       DECIMAL(10,3),
valid_from  DATE,
valid_to    DATE

The current price would have valid_to date of 9999-12-31. When you add a new price from, say, tomorrow, then the current price valid_to is set to today and the new price becomes the current_price (from tomorrow until 9999-12-31)

 

You then match your (old) items like this

SELECT 
  item_desc
, qty
, price
FROM item INNER JOIN prices ON item.date BETWEEN prices valid_from AND prices.valid_to

I think your first solution is closer to what I need. how would I add the equivalent to "AND Date = '2015-12-4'" ?

[manhattes]

I have a table with historical pricing info and it is a table that I have to update manually so the most recent date wont always be the current date.

Because it may not be updated, I just want the newest entry of Table.Close and another date that is represented in a variable.

 

The above code output:

Date

2015-10-16

Close

0.28

seq

2

 

 

I need this output and another row which is an exact match to be the output.

I got it to work by change the my variable to equal $XXX.

However, it now duplicates the result if both are found though. 

Can it only return the $XXX if it is found?

[Barand]

You could

... ORDER BY date='$XXX' DESC LIMIT 1

[manhattes]

 

You could

... ORDER BY date='$XXX' DESC LIMIT 1

Amazing. Thank you.