manhattes Posted October 29, 2015 Share Posted October 29, 2015 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'] ."')"; Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2015 Share Posted October 29, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 29, 2015 Author Share Posted October 29, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted October 29, 2015 Share Posted October 29, 2015 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 Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 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'" ? Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 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? Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted October 30, 2015 Solution Share Posted October 30, 2015 You could ... ORDER BY date='$XXX' DESC LIMIT 1 Quote Link to comment Share on other sites More sharing options...
manhattes Posted October 30, 2015 Author Share Posted October 30, 2015 You could ... ORDER BY date='$XXX' DESC LIMIT 1 Amazing. Thank you. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.