Tag Info

New answers tagged

2

There's probably something more elegant than this, but since you are doing a kind of pivot (transforming from columns to rows, i.e. metadata to data) it is never going to be pretty. Assuming a table like: create table t ( ts timestamp not null , EngOilP_sd smallint not null , CompOilLVL_sd smallint not null ) engine = innodb; insert into t (ts, EngOilP_sd, ...


1

Working with this setup: CREATE TABLE tbl ( tbl_id serial PRIMARY KEY , user_id text NOT NULL , start_time timestamp NOT NULL -- if values could be NULL ... , end_time timestamp NOT NULL -- ... it would be more complicated ); Total duration without redundancy Your numbers indicate you just want the total duration without counting ...


7

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer: Gap-less sequence where multiple transactions with multiple tables are involved Here ...


0

You could try this: [edit] fixed a bug .. comment on line fixed [/edit] with w_data as ( select 'A' emid, to_date('01/01/2012','mm/dd/yyyy') fromdate, to_date('12/31/2012','mm/dd/yyyy') todate from dual union all select 'A' emid, to_date('12/31/2012','mm/dd/yyyy') fromdate, to_date('12/31/2013','mm/dd/yyyy') todate from dual union all ...


1

Based on Paul White's answer here is my solution. It skips only weekends and groups rows without comment. WITH GroupStart AS ( -- Identify the start of each new group SELECT td.id, td.holidaydate, td.holidaycomment, CASE --New group, if more then two days between two dates (more than a weekend) WHEN ...


0

This is my attempt at solving this "groups and islands" problem, with comments in-line: WITH GroupStart AS ( -- Identify the start of each new group SELECT TD.id, TD.holidaydate, TD.holidaycomment, -- New group if the holidaycomment has changed -- (in holidaydate order) CASE WHEN holidaycomment = ...


0

I wouldn't use connect by to solve that, I'd just use some good old analytics :) [edit] based on comment, adjust query - still use analytics, this is NOT a heirarchical query .. [/edit] with w_group as ( select id, sum(grp_cnt) over (order by id) grp_id, holidaydate, holidaycomment from ( select id, holidaydate, ...


3

The answer with the variables is going to be more efficient but here is an answer with pure SQL: select a.id_user, a.id_ringtype, a.number as min, min(b.number) as max from rings as a join rings as b on a.id_user = b.id_user and a.id_ringtype = b.id_ringtype and a.number <= b.number where not exists ...


3

Since MySQL doesn't support ROW_NUMBER(), You can use a variable to create a group like this. Sample Data CREATE TABLE `Rings` ( ID_RingType CHAR(2), Number MEDIUMINT UNSIGNED, ID_User INT(11) ); INSERT INTO `Rings` VALUES ('AA',1,1), ('AA',2,1), ('AA',3,1), ('AA',11,1), ('AA',12,1), ('AA',13,1), ('AA',14,1), ('AA',15,1), ...


2

This gets continuity by keeping track of the last number and creating a new column that is a continuity count - an increasing number which increases only when the continuity breaks. That way we can group by that continuity count also and have the results you want: set @lastnumber=0; set @continuous=0; select id_user, id_ringtype, min(number), ...


3

If this is a table of back-to-back ranges only, your case can be treated as a classic "gaps and islands" problem, where you just need to isolate islands of consecutive ranges and then "condense" them by taking the minimum [from] and the maximum [to] per island. There is an established method of solving this using two ROW_NUMBER calls: WITH islands AS ( ...



Top 50 recent answers are included