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
Is Not Null Test for a Record Does Not Return True When Variable Is Set
SQL Server Agent Job Account Issue
Sql: Parse Comma-Delimited String and Use as Join
Ssms: How to Import (Copy/Paste) Data from Excel
MySQL Count() Multiple Columns
SQL Query to Create a Calculated Field
Reverse in Oracle This Path Z/Y/X to X/Y/Z
To Get Date from Datetime in SQL
How to Generate All N-Grams in Hive
Connect to Remote SQL Database Using Excel
Case Statement with Different Data Type
Unpivot on an Indeterminate Number of Columns
Using Openxml in SQL Server 2008 Stored Proc - Insert Order Differs from Xml Document