CODEWITHSUNDEEP
mysqlsql
Andrew_ww
Andrew_ww

Reputation: 11

Mysql select order by many columns

Script below creates a simple database and table. In windows environment, you can use it with wamp server's ( https://sourceforge.net/projects/wampserver/ ) phpmyadmin sql console:

delimiter $$

create database testtimes $$
use testtimes $$

create table mytime (
 hour bigint not null,
 min varchar(3) not null,
 sec bigint not null,
 unique key mytime_lst (hour,min,sec)
 ) $$

insert into mytime (hour,min,sec) values (0,"3",10) $$
insert into mytime (hour,min,sec) values (0,"3",11) $$
insert into mytime (hour,min,sec) values (0,"4",8) $$
insert into mytime (hour,min,sec) values (0,"5",3) $$
insert into mytime (hour,min,sec) values (1,"0",7) $$
insert into mytime (hour,min,sec) values (1,"3",10) $$

delimiter ;

In the simplified example, there are 3 columns and some rows. Columns are tied together as one logical unit, I created index on them. I want to pinpoint 1 logical unit in the table, and use the index to give me 2 result next to that point. I am not sure, do I have that support or not. If the problem is related to specific SQL server, please, give me hint about.

(To prevent unrelated questions: the column "min" is converted to varchar on purpose. The example above is a simplified one. In the real environment, I do not have the luxury to rely on bigints only.)

An example query:

select *
from mytime
where (hour >= 0 and min >= "4" and sec >= 7)
order by hour, min, sec asc
limit 2

When I run the query above (phpmyadmin helps), it gives back only the row inserted by:

insert into mytime (hour,min,sec) values (0,"4",8) $$

I would like to get back this row too:

insert into mytime (hour,min,sec) values (0,"5",3) $$

Is it possible to fix that example query to do the job?

Upvotes: 0

Views: 92

Answers (2)

Gosfly
Gosfly

Reputation: 1300

You should convert your fields hour, min and sec to date format like this :

SELECT * FROM mytime 
WHERE STR_TO_DATE(CONCAT(hour,'-',min,'-',sec),'%H-%i-%s') > '00:04:07'
ORDER BY hour,min,sec asc limit 2

You could even use > '0:4:7' if you prefer

OR 

SELECT * FROM mytime 
WHERE CONVERT(CONCAT(hour,':',min,':',sec), TIME) > '0:4:7'
ORDER BY hour,min,sec ASC LIMIT 2

If you don't want to convert to date using str_to_date and concat, you could use multiple where clause with OR like this :

SELECT *
FROM mytime
WHERE (HOUR > 0)
OR (HOUR = 0 AND min > 4)
OR (HOUR = 0 AND min = 4 AND SEC >= 7)
ORDER BY hour, min, sec asc
LIMIT 2

Upvotes: 1

Mohammad Aslam
Mohammad Aslam

Reputation: 1

You change get result with changing your min condition

SELECT * 
FROM mytime 
WHERE (hour >= 0 AND min > "4" AND sec >= 3) 
ORDER BY hour,
         min,
         sec ASC 
LIMIT 2

Upvotes: 0

Related Questions