Raven1313 Posted June 21, 2012 Share Posted June 21, 2012 I'm trying to select PVOL (a number) by desc to get the top 5 values of that column. But I don't get the top 5 values of PVOL... I just get the highest which is LESS than 1,000,000 all the numbers above 1mil don't show up. select PLAST,PREF,POPEN,PCLOSE,PBID,PVOL,PVALUE,PASK,TICKER from hist_sec where DATE(PDATE) = DATE(NOW()) and PVOL != '' order by PVOL DESC limit 0 , 5; How do I select where I also include the numbers above 1 mil? Quote Link to comment https://forums.phpfreaks.com/topic/264543-select-by-desc-doesnt-work/ Share on other sites More sharing options...
Barand Posted June 21, 2012 Share Posted June 21, 2012 can you post the results of this query SHOW CREATE TABLE hist_sec; Quote Link to comment https://forums.phpfreaks.com/topic/264543-select-by-desc-doesnt-work/#findComment-1355695 Share on other sites More sharing options...
Raven1313 Posted June 21, 2012 Author Share Posted June 21, 2012 Hello Barand, Below is the result you request: CREATE TABLE `hist_sec` ( `PDATE` date NOT NULL, `STKNO` double NOT NULL, `ROWNO` double NOT NULL, `TICKER` varchar(25) NOT NULL, `NATIVE_TICKER` varchar(25) NOT NULL, `PREF` varchar(15) NOT NULL, `PLAST` varchar(15) NOT NULL, `POPEN` varchar(15) NOT NULL, `PHIGH` varchar(15) NOT NULL, `PLOW` varchar(15) NOT NULL, `PVOL` varchar(15) NOT NULL, `PVALUE` varchar(15) NOT NULL, `PTRADES` varchar(15) NOT NULL, `PCLOSE` varchar(15) NOT NULL, `PBID` varchar(15) NOT NULL, `PASK` varchar(15) NOT NULL, `CHANGE` varchar(15) NOT NULL, `VWAP` varchar(15) NOT NULL, `YHIGH` varchar(15) NOT NULL, `YLOW` varchar(15) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 Quote Link to comment https://forums.phpfreaks.com/topic/264543-select-by-desc-doesnt-work/#findComment-1355702 Share on other sites More sharing options...
litebearer Posted June 21, 2012 Share Posted June 21, 2012 You need to modify your table. the fields that contain values that are in reality numbers should NOT be varchar Quote Link to comment https://forums.phpfreaks.com/topic/264543-select-by-desc-doesnt-work/#findComment-1355703 Share on other sites More sharing options...
Raven1313 Posted June 21, 2012 Author Share Posted June 21, 2012 What should I change VARCHAR to? Quote Link to comment https://forums.phpfreaks.com/topic/264543-select-by-desc-doesnt-work/#findComment-1355706 Share on other sites More sharing options...
Barand Posted June 21, 2012 Share Posted June 21, 2012 If they don't have decimals then INT. If they have decimals then something like DECIMAL(12,2) assuming 2 dec places. Also don't store the commas, format the numbers on output. This is the result of sorting nubers DESC in a varchar field +----------+ | title | +----------+ | 60000 | | 50000 | | 40000 | | 15000000 | | 12000000 | | 1000000 | +----------+ Quote Link to comment https://forums.phpfreaks.com/topic/264543-select-by-desc-doesnt-work/#findComment-1355711 Share on other sites More sharing options...
Raven1313 Posted June 24, 2012 Author Share Posted June 24, 2012 Thank you Barand! Now it's working Quote Link to comment https://forums.phpfreaks.com/topic/264543-select-by-desc-doesnt-work/#findComment-1356525 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.