CODEWITHSUNDEEP
mysql
Pawan
Pawan

Reputation: 32331

Is it possible to Use Only Single Query for obtaining the desired result?

This is my Data Structure 

CREATE TABLE `historical_data` (
  `symbol_name` varchar(70) DEFAULT NULL,
  `current_day` date DEFAULT NULL,
  `open_val` decimal(15,2) DEFAULT NULL,
  `high_val` decimal(15,2) DEFAULT NULL,
  `low_val` decimal(15,2) DEFAULT NULL,
  `close_val` decimal(15,2) DEFAULT NULL,
  `last_val` decimal(15,2) DEFAULT NULL,
  `prevclose_val` decimal(15,2) DEFAULT NULL
);



INSERT INTO `historical_data` (`symbol_name`, `current_day`, `open_val`, `high_val`, `low_val`, `close_val`, `last_val`, `prevclose_val`) VALUES
    ('WOCKPHARMA', '2015-12-11', 1611.00, 1620.00, 1570.30, 1581.25, 1579.00, 1602.10),
    ('YESBANK', '2015-12-11', 709.00, 713.70, 672.25, 680.60, 683.45, 707.10),
    ('WOCKPHARMA', '2015-12-14', 1572.50, 1584.70, 1545.00, 1559.55, 1557.60, 1581.25),
    ('YESBANK', '2015-12-14', 679.10, 689.00, 668.00, 683.25, 683.65, 680.60),
    ('WOCKPHARMA', '2015-12-15', 1564.70, 1580.50, 1558.00, 1572.10, 1567.50, 1559.55),
    ('YESBANK', '2015-12-15', 688.00, 694.20, 675.75, 691.35, 688.25, 683.25),
    ('WOCKPHARMA', '2015-12-16', 1581.50, 1617.90, 1578.00, 1587.15, 1589.00, 1572.10),
    ('YESBANK', '2015-12-16', 697.00, 710.60, 694.25, 698.55, 699.15, 691.35),
    ('WOCKPHARMA', '2015-12-17', 1596.10, 1642.00, 1576.05, 1628.20, 1636.80, 1587.15),
    ('YESBANK', '2015-12-17', 708.00, 723.75, 705.70, 721.10, 720.00, 698.55),
    ('WOCKPHARMA', '2015-12-18', 1630.00, 1654.85, 1620.30, 1627.55, 1631.00, 1628.20),
    ('YESBANK', '2015-12-18', 717.90, 727.45, 713.60, 718.70, 720.20, 721.10);

And to find out the Top Gainers based on the current data i am currently doing using Two Queries

The First Query Fethes me the last date present in the database 

select current_day from historical_data order by current_day desc limit 1

And in the Second Query i am using that obtained last date from the first Query 

select symbol_name , current_day ,close_val,prevclose_val, (close_val-prevclose_val) as toploosers   from  historical_data  where current_day = ?  order by toploosers desc limit 10

This is my sqlfiddle ,

Could you please let me know how to achieve this http://sqlfiddle.com/#!9/3693b

Upvotes: 0

Views: 54

Answers (3)

VIPAN SABHERWAL
VIPAN SABHERWAL

Reputation: 180

SELECT symbol_name,
       current_day,
       close_val,
       prevclose_val,
       (close_val-prevclose_val) AS toploosers
FROM historical_data
WHERE current_day =
    (SELECT max(current_day)
     FROM historical_data)
ORDER BY toploosers DESC LIMIT 10

Upvotes: 1

dsp_user
dsp_user

Reputation: 2121

How about using a subselect

SELECT symbol_name,
       current_day,
       close_val,
       prevclose_val,
       (close_val-prevclose_val) AS toploosers
FROM historical_data
WHERE current_day **IN
    (SELECT current_day
     FROM historical_data
     ORDER BY current_day DESC LIMIT 1)**
ORDER BY toploosers DESC LIMIT 10

Upvotes: 0

Thanga
Thanga

Reputation: 8141

Select the max of date using a subquery as below

select symbol_name , current_day ,close_val,prevclose_val, (close_val-prevclose_val) as toploosers   from  historical_data  where current_day = (select max(current_day) from historical_data) order by toploosers desc limit 10

Upvotes: 1

Related Questions