Insert Blank Row Between Groups of Rows and Sorted by Id in SQL

Insert blank row to result after ORDER BY

You can, pretty much as Michael and Gordon did, just tack an empty row on with union all, but you need to have it before the order by:

...
and to_date(to_char(t.enddatetime, 'DD-MON-YYYY')) <=
to_date('?DATE2::?','MM/DD/YYYY')
union all
select null, null, null, null, null, null, null, null
from dual
order by eventid, starttime, actionsequence;

... and you can't use the case that Gordon had directly in the order by because it isn't a selected value - you'll get an ORA-07185. (Note that the column names in the order by are the aliases that you assigned in the select, not those in the table; and you don't include the table name/alias; and it isn't necessary to alias the null columns in the union part, but you may want to for clarity).

But this relies on null being sorted after any real values, which may not always be the case (not sure, but might be affected by NLS parameters), and it isn't known if the real eventkey can ever be null anyway. So it's probably safer to introduce a dummy column in both parts of the query and use that for the ordering, but exclude it from the results by nesting the query:

select crewactionfactid, crewkey, eventid, actionsequence, type,
starttime, endtime, duration
from (
select 0 as dummy_order_field,
t.crewactionfactid,
t.crewkey,
t.eventkey as eventid,
t.actionsequence,
case t.actiontype
when 'DISPATCHED' then '2-Dispatched'
when 'ASSIGNED' then '1-Assigned'
when 'ENROUTE' then '3-Enroute'
when 'ARRIVED' then '4-Arrived'
else 'unknown'
end as type,
t.startdatetime as starttime,
t.enddatetime as endtime,
t.duration
from schema_name.table_name t
where to_date(to_char(t.startdatetime, 'DD-MON-YYYY')) >=
to_date('?DATE1::?','MM/DD/YYYY')
and to_date(to_char(t.enddatetime, 'DD-MON-YYYY')) <=
to_date('?DATE2::?','MM/DD/YYYY')
union all
select 1, null, null, null, null, null, null, null, null
from dual
)
order by dummy_order_field, eventid, starttime, action sequence;

The date handling is odd though, particularly the to_date(to_char(...)) parts. It looks like you're just trying to lose the time portion, in which case you can use trunk instead:

where trunc(t.startdatetime) >= to_date('?DATE1::?','MM/DD/YYYY')
and trunc(t.enddatetime) <= to_date('?DATE2::?','MM/DD/YYYY')

But applying any function to the date column prevents any index on it being used, so it's better to leave that alone and get the variable part in the right state for comparison:

where t.startdatetime >= to_date('?DATE1::?','MM/DD/YYYY')
and t.enddatetime < to_date('?DATE2::?','MM/DD/YYYY') + 1

The + 1 adds a day, so id DATE2 was 07/12/2012, the filter is < 2012-07-13 00:00:00, which is the same as <= 2012-07-12 23:59:59.

Display a blank row between every unique row?

You can do it in the database, but this type of processing should really be done at the application layer.

But, it is kind of an amusing trick to figure out how to do it in the database, and that is your specific question:

WITH e AS (
SELECT employee, ITEM_TYPE, COUNT(ITEM_TYPE) as cnt
FROM hr_database
GROUP BY employee, ITEM_TYPE
)
SELECT (case when cnt is not null then employee end) as employee,
item_type, cnt
FROM (select employee, item_type, cnt, 1 as x from e union all
select distinct employee, NULL, NULL, 2 as x from e
) e
ORDER BY e.employee, x;

I emphasize, though, that this is really for amusement and perhaps for understanding better how SQL works. In the real world, you do this type of work at the application layer.

A summary of how this works. The union all brings in one additional row for each employee. The x is a priority for sorting -- because you have to sort the result set to get the proper ordering. The case statement is needed to prevent the employee from being in the first column. cnt should never be NULL for the valid rows.

Insert number of blank rows in SQL

Just define a second query that builds the empty rows that you need and union them together.

SELECT ROW_NUMBER() OVER(PARTITION BY family_id ORDER BY family_id) AS rowNum,
full_name as name,
family_id
FROM tbl_person

UNION ALL

SELECT n.N AS rowNum,
NULL AS name,
p.family_id
FROM (SELECT family_id, COUNT(*) family_count FROM tbl_person group by family_id) p
INNER JOIN (
SELECT 1 AS N
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
) n
ON n.n > p.family_count
ORDER BY family_id,
rowNum,
name;

How do I insert an empty row, but have the autonumber update correctly?

INSERT INTO `table` (`primary_key`) VALUES (NULL);

Insert blank row between groups and maintain original order

The issue is that after the column 'month' got changed to an ordered factor, "" is not specified as one of the levels. So, naturally any value that is not a level is treated as missing value and hence we get the NA. Correction can be done at the earlier step by including the "" as one of the levels

df$month <- ordered(df$month, levels = c("2", "5", "8", "11", "annual", ""))

NOTE: The order of the "" is not clear. So, it is specified as the last level

LINQ to SQL group by to a blank row of data?

You should have a field that shows which table you have taken them from:

SELECT "Field 1" AS fullname, null  AS limsuser_id, 1 as [order]
UNION
SELECT fullname, limsuser_id, 1 as [order] from table1
UNION
SELECT "Field 2" AS fullname, null AS limsuser_id, 2 as [order]
UNION
SELECT fullname, limsuser_id, 2 as [order] from table2

Then you can simply do this:

var result = yourItems.OrderByDescending(x=> x.fullname == "Field 1" || x.fullname == "Field 2")
.ThenBy(x=> x.fullname)
.GroupBy(x=> x.order);

.OrderByDescending(x=> x.fullname == "Field 1" || x.fullname == "Field 2") ensures that Field 1 and Field 2 will stand on top of their groups no matter what their aphabetical order is.

.ThenBy(x=> x.fullname) will then sort it by fullname

then you can GroupBy on order field.

Or if you have null id for only Field 1 and Field 2 you can do this:

var result = yourItems.OrderByDescending(x=> x.limsuser_id  == null)
.ThenBy(x=> x.fullname)
.GroupBy(x=> x.order);


Related Topics



Leave a reply



Submit