I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.
CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)
[code]....
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
Refer to the txt file to create table and insert data.
I executed the following query-
SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail
and got the following result-
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1
Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1
how to select the sixth highest earner in my employees table.how to select 6th lowest earner..
View 18 Replies View RelatedHave a table like this:
ID1ID2DATEID2Value
1121/1/20066
1241/1/2006400
1246/1/2006410
1366/1/2006100
2121/1/20077
2246/1/2007350
2247/1/2007360
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1246/1/2006410
2247/1/2007360
I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.
I want to select data inserted in the table for that day only.
Table name -->ADJCOLUMNS
i want to select areAccount_no-->number datatype TRANSACT_DATE-- NOT NULL DATE I have written the query below .Is the below query correct.
select account_no,to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') T_date from adj
where to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') between
to_char(TRUNC(sysdate),'DD-MON-YY hh24:mi:ss') AND
to_char(TRUNC(sysdate+1) - 1/86400,'DD-MON-YY hh24:mi:ss');
I need to find the top two values value for each ID Number:
CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));
INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');
Desired Result:
ID Number 1st 2nd
12345679 30 30
99999999 30 25
BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for HPUX: Version 11.2.0.3.0 - Production
[code]...
SELECT job_request_id,
CAST (COLLECT (USER_ID) AS SYS.OdcinumberList) user_ids
FROM mytable
GROUP BY job_request_id;
ORA-22814: attribute or element value is larger than specified in type
I have tables SUBJECT(subject_id, name, number) and PS(ps_id, subject_id, student_id). I need to select all from SUBJECT,subject_id and student_id from SP, joined by subject_id, where student_id needs to be read from session. I'm using asp.net with oracle database. How to get the value from the session.
View 2 Replies View Relatedhow can I select whole table in parts of 100 rows?
If I have primary key I can:
CODEstart=0;
end=100;
select * from table where ID>=start_point and ID<end;
start=end;
end=end+100;
and repeat:
CODEselect * from table where ID>=start_point and ID<end;
How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]...
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]...
One person (zmo_kodas) can have a lot of reports (ats_nr).
Every report can have one or more services (pas_kodas).
any way to select the clob value from dblink.?
View 7 Replies View RelatedI am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:
SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID
However, I only need the DATABASE piece selected in my FOR loop below.
CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;
[Code]...
I'm putting together a path to select a revision of a particular novel:
SELECT e.documentname, e.Revision, e.VersionNumber
FROM Catalog, BookInCatalog
INNER JOIN NovelMaster
INNER JOIN HasNovelRevision
INNER JOIN NovelRevision e
LEFT JOIN NovelRevision s
[code]...
My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.
When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.
One other challenge is the format of the revision- a revision sequence could look like this:
A
B
C1
C2
C
D
E1
E
So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:
A1
A
B1
B
I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).
I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.
[URL]
What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).
[URL]
One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).
correct this one.
declare
v_ename varchar2(10):='emp';
begin
for j in (select ename from v_ename)
loop
dbms_output.put_line(j.ename);
end loop;
end;
getting error v_ename table does not exists. i should use v_ename, as i dont know the table name.
have a bit of a SQL trouble. I have a simple table (pcuk_BG_alloc_TAB) which stores Parts, Quantities and Applied dates
PART_NO QUANTITY APPLIED
PartA 100 10/8/2012
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 50 10/8/2012
PartC 75 15/8/2012
PartC 80 21/8/2012
I am only interested the latest applied date for each part and am looking for this to be returned in a select statement (as below)
PART_NO QUANTITY APPLIED
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 80 21/8/2012
I have tried using the max function (select part_no, quantity, max(applied) from pcuk_BG_alloc_TAB group by part_no, quantity) but seems as the records have different quantities it treats them separately.
I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way
SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /
#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US
- - - - - - - - - - - - - - - - -
Desired Output:
#ENCODING WINDOWS-1252
Language Section EN-US
Catalog Section Title Date Source
I'm selecting a set of records from one table, for example: ID, description and date. From this I'm only wanting the latest inserted row. I've used the max function on the date which is fine, however, there are some records that have had their description changed. This then returns two values for one ID, the max for the original description and the max for the changed description.
I'm getting:
ID |Description |Date
1 ABC 01/01/2010
2 XYZ 02/03/2010
2 XYZ1 03/05/2010
When I want:
ID |Description |Date
1 ABC 01/01/2010
2 XYZ1 03/05/2010
As ID 2 with XYZ1 Description is the very latest row for that ID.
This is an audit table so the ID appears on numerous rows as it a composite key with date.
I just want to know that "is it safe to select a columns using ROWID in a table?"
View 3 Replies View RelatedIs information in v$sql enough to select all queries executed between given date and now? When the queries are removed from v$sql?
View 1 Replies View RelatedI have table TEST_REP with below data
DA SUMA
---------------------- ----------------------
2011 2
2011 3
2011 5
2012 2
2012 7
2014 2
2014 10
2015 2
2016 33
2015 26
2017 21
2017 2
2018 23
13 rows selected
I have used following query to get the below output:
select
br_mat MAT_YEAR,
sum(br_par) TOTAL
from (
(select to_char(da) br_mat,suma br_par from test_rep)
UNION ALL
[code].......
Output :
MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 23
2018 23
2019 0
2020 0
10 rows selected
Expected Output :
MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 and Greater 46
I am trying to select the owner of a certain object, only knowing the name of the object and the user calling it.
Problem is this object might exist in more than one schema with the same name, and I only need the one that is called by the active user, which himself/herself might have access to other schemas containing their own copy of the object.
Example:
SELECT OWNER
INTO v_schema_name
FROM ALL_OBJECTS
WHERE OBJECT_NAME = p_object_name;
This works fine, until the object exists in more than one schema to which the current user has access.
I have a table with following structure:
CREATE TABLE ID_comments
(
ID CHAR(10 BYTE) NOT NULL,
S_COMMENTS VARCHAR2(255 BYTE),
P_COMMENTS VARCHAR2(255 BYTE),
C_COMMENTS VARCHAR2(255 BYTE)
);
For each Id, I can have multiple records.
Below is the insert script of one of the ID:
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE','');
Insert into ID_comments values ('0813654254','','JR','');
[code].......
Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.
i created a form by using wizard...i want to improve that form...
I created one lov which is attached in one of form field..now when i press f9 lov displayed..
But when i select any one record in that LOV , its does not come in my form field
I have a field which stores notes. These notes are either just text, just numbers or a mix of the two.
I am looking for a way to return the fields containing a mix of the two. For example
123
abc
12cd
Would just return 12cd.
I have a table with a count of customers for about a year, but I only want to select Wednesday and Thursday of each week, starting at the beginning of dates. Table is simple and has two columns. Each row is Distinct to a Date, there are no Date duplicates, it's counted for each day of the year.
column 1: count of customers, count(customer_id)
column 2: Date
Not even sure if this is possible to select a date using the day name ?
Basically I want to select each Wednesday and Thursday from each week and compare the counts week over week, over week for all week to see if the counts are going up or down, to get trends .
I have records like the following
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N
ABCD 2/14/2012 N
ABCD 2/20/2012 Y
ABCD 3/01/2012 N
ABCD 3/10/2012 N
[Code]...
I have to write a select statement to to keep the first record and then pull only the records when the Valid_Flag changed. The result set should be like below.
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N -- I have preserved the first record
ABCD 2/20/2012 Y -- Valid_Flag chages to a Y for teh first time and so on.
ABCD 3/01/2012 N
ABCD 3/14/2012 Y
ABCD 3/25/2012 N
ABCD 4/25/2012 Y
If there is no change in the flag, I do not have to pull that record.
I have a table test with 10,000 records in it and 50 columns.I have to select those rows which contain values as "Sales Dum" in their field..For table with small number of colums i did this
SELECT * FROM tbl_website_dtl WHERE created_by like '%Sales%' or website_name like '%Sales%' or website_code like '%sales%';But should i do for table containing 50 columns.
