Jump to content

Selecting two rows by date


manhattes
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'] ."')";
Link to comment
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'

[edit]

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
Link to comment
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'

[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 by manhattes
Link to comment
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
Link to comment
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'" ?

Link to comment
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?

Link to comment
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.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.