query on my mysql

Question

query on my mysql

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

hi friends could help me complete a query

I have these records, from which I have to obtain day 1 the start date and the end date the day two start date and end date and so with the others, is what I get this way:

(SELECT date_game_large FROM tabla where journay=1 ORDER BY date_game_large LIMIT 1) as date_start;
(SELECT date_game_large FROM tabla where journay=1 ORDER BY date_game_large DESC LIMIT 1) as date_end;

I'm doing this query to do it for all days:

SELECT id_competition,
journay,
(SELECT date_game_large FROM tabla where journay=1 ORDER BY date_game_large LIMIT 1) as date_start,
(SELECT date_game_large FROM tabla where journay=1 ORDER BY date_game_large DESC LIMIT 1) as date_end
FROM tabla GROUP by journay

and they stay this way

but only does it with the 1st day, I do not know how to save the value of the day and pass it to the where to do it for all or how to do it so that it does day by day

could you help me, regards

mysql query

asked by skycomputer2 10.05.2017 в 00:23

source

2 answers

Problem when deploying html elements with toogle I do not work the stack swing

score 0 · Answer 1

I think you can do it using UNION , as follows:

SQL Fiddle Demo

MySQL 5.6 Schema Setup :

CREATE TABLE jornadas
    ('id_competition' int, 'journay' int, 'date_start' int, 'date_end' int)
;

INSERT INTO jornadas
    ('id_competition', 'journay', 'date_start', 'date_end')
VALUES
    (1, 1, 1472236200, 1472236500),
    (1, 2, 1472236700, 1472236900),
    (1, 3, 1472237100, 1472237200),
    (2, 4, 1482236200, 1482236500),
    (2, 5, 1482236700, 1482236900),
    (2, 6, 1482237100, 1482237200),
    (3, 7, 1492236200, 1492236500),
    (3, 8, 1492236700, 1492236900),
    (3, 9, 1492237100, 142237200)
;

Query 1 :

select id_competition, journay as first_journay, date_start, date_end
  from (
    select j1.id_competition, j1.journay, j1.date_start, j1.date_end
    from jornadas j1
    group by j1.journay

    union

    select j2.id_competition, j2.journay, j2.date_start, j2.date_end
    from jornadas j2
    group by j2.journay

  ) tabla_union
group by id_competition

Results :

| id_competition | first_journay | date_start |   date_end |
|----------------|---------------|------------|------------|
|              1 |             1 | 1472236200 | 1472236500 |
|              2 |             4 | 1482236200 | 1482236500 |
|              3 |             7 | 1492236200 | 1492236500 |

score 0 · Answer 2

0

You only need a GROUP BY with MIN / MAX :

select journay,
       min(date_game_large) as date_start,
       max(date_game_large) as date_end
  from tabla
 group by journay
 order by journay

answered by 10.05.2017 в 02:01