# Selecting two rows by date

Go to solution Solved by Barand,

## Recommended Posts

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'] ."')";
##### Share on other sites

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'

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

Edited by Barand
##### Share on other sites

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'

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 by manhattes
##### Share on other sites

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
##### Share on other sites

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'" ?

##### Share on other sites

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?

##### Share on other sites

• Solution

You could

... ORDER BY date='\$XXX' DESC LIMIT 1
##### Share on other sites

You could

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

Amazing. Thank you.

##### Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.