SQL & PL/SQL :: Sort By Best Match
Jan 20, 2012
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Sharma 01-Jul-1987
Avinash Pandey 03-Feb-1988
Ankit Gupta 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Sikha Batham 17-Apr-1988
Now if I give parameters-
first_name='Manu'
last_name='Batham'
dob='02-Feb-1988'
then my result should be like below-
result-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Manu Sharma 01-Jul-1987
Ankit Gupta 02-Feb-1988
Sikha Batham 17-Apr-1988
My result is based on the approach-
if matched first_name, last_name, dob --> 1st prefrence in order
if matched first_name, dob --> 2nd prefrence in order
if matched first_name, last_name --> 3rd prefrence in order
if matched last_name, dob --> 4th prefrence in order
if matched first_name --> 5th prefrence in order
if matched last_name --> 6th prefrence in order
if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where
first_name='Manu' and
last_name='Batham' and
dob=to_date('02/02/1988','dd/mm/yyyy')
union
Select a,b,c,2 from test Where
[code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
View 19 Replies
ADVERTISEMENT
Oct 2, 2011
I am new to sql..
I need to join 2 tables based on first match.. I cldnot use distinct on the result, as distinct work with entire row..
I cant use group by as well, since for group by hv to select all the columns which we need to display
View 1 Replies
View Related
Aug 28, 2012
I have some records as below would like to match and merge them
ID1 ID2
101 103
101 104
205 103
206 103
205 106
151 222
157 222
151 223
156 222
134 223
134 435
156 438
Output should be. All matching records should be grouped together and generate result by new seq
ID1 ID2 Result
101 103 1
101 104 1
205 103 1
206 103 1
205 106 1
151 222 2
157 222 2
151 223 2
156 222 2
134 223 2
134 435 2
156 438 2
View 15 Replies
View Related
Jul 17, 2013
Seemingly a very simple problem, but I can't seem to figure it out. Table below is a table that holds UserId' s and LinkId's.
CREATE TABLE UserLink (UserId NUMBER, LinkId NUMBER );
ALTER TABLE UserLink ADD PRIMARY KEY (UserId, LinkId);
INSERT INTO UserLink VALUES (1, 1);
INSERT INTO UserLink VALUES (1, 2);
INSERT INTO UserLink VALUES (3, 1);
INSERT INTO UserLink VALUES (4, 1);
I'm looking for a query in which I can plug a List of LinkId's that outputs only users that have a relationship with ALL LinkId's provided. In this example you may assume that the List is equal to (1, 2), but you can't make any assumptions about the size of the list, other than size != 0. The query to find users that match ANY of the provided LinkId's is quite simple:
SELECT UserId FROM UserLink
WHERE LinkId IN (1,2)
The correct query should output:
USERID
------
1
And it should work for any size > 0 list of values.
View 6 Replies
View Related
Dec 28, 2011
I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.
Example:
NAME1: 'Bush', 'Thomas', 'Cook', 'Smith'
NAME2: 'Bush, B.B.', 'Thomas,C.' 'Cook', 'A.A. Smith'
Basically the code I'm trying only turns up complete matches and not partials. what I'm doing wrong or how I can improve it?
CODEselect *
from TEST
where NAME1 like ('%'||NAME2||'%');
View 4 Replies
View Related
Aug 2, 2012
Below is a column 'ADDR' with the data (single column)
ADDR
--------
/shared/Folder_1 :^BIAdministrator:^BIAuthor:^BIConsumer:BISystemUser:OracleSystemUser:System:weblogic: :F
[Code]....
View 2 Replies
View Related
Aug 19, 2013
find two words matching from two different tables.
Example:-
table1 || table2
john Dev || Kab Leva
Zaheer khan || mark dev
Cina maater || jhon dev wood
kab leva Sumo || Tony levis
output:-
john dev || john dev
kab leva || kab leva
View 9 Replies
View Related
Apr 23, 2010
I have table as follows:
create table sample1 (
i number ,
j date,
k number)
insert into sample1 values (1,'23-Apr-2010',11)
insert into sample1 values (2,'22-Apr-2010',12)
insert into sample1 values (3,'21-Apr-2010',13)
insert into sample1 values (4,'19-Apr-2010',14)
insert into sample1 values (5,'18-Apr-2010',15)
insert into sample1 values (6,'17-Apr-2010',16)
I would like to get nulls , if there is no data for a date. As we can see , here i am missing the data for '20-Apr-2010'.
I did it through "UNIX" , but it's not efficient.
The data might be missing for the complete week also. I need to test in this way only for the last 7 days. I tried something like this:
select i, j , sum(k)
from sample1
where j in (select to_date(sysdate - rownum)
from dual
connect by rownum < = 7)
group by i, j
View 3 Replies
View Related
Nov 23, 2008
I have the following three tables:
Buyer:
BuyerID
Name
Trans:
TransID
BuyerID
Trans_Item:
Qty
Price
BuyerID
TransID
I need to figure what buyer has bought the most things. I have a function already determines the amount each buyer has bought.
So that is done. I need to order this by buyerid. How do I sort something like that? ORDER BY and GROUP BY do not work.
View 2 Replies
View Related
Mar 10, 2010
I am running this query but am not getting data that is correct.
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc
View 8 Replies
View Related
Feb 28, 2011
In query I have WHERE clause like this:
WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') >
TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')
I have data like this:
PEROFOPFROMAIRCRAFTSTD
29/03/20102150
NULL NULL
NULL NULL
30/03/20102150
When I execute the query it always gives me the error "literal does not match format string".
View 7 Replies
View Related
Dec 6, 2012
I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,
create or replace function ssndate(p_date in varchar2 default '03-01-18',
p_time in varchar2)
return number
$if dbms_db_version.ver_le_10 $then
deterministic
$elsif dbms_db_version.ver_le_11 $then
result_cache
$end
as
begin
return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss')
- to_date('01-01-90','dd-mm-yy')) * (86400/2400));
end ssndate;
/
The function ssndate compiled successfully.The next step I took was to create a view through the following query,
create or replace view WEBLOG_VIEWS
as
select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT",
C_IP,
CS_USER_AGENT,
(CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK
from WEBLOG;
This was successful as well. The problem is in the next step where I try to do data grouping.
create table FINAL_WEBLOG as
select SESSION_DT, C_IP, CS_USER_AGENT,
listagg(WEB_LINK, ' ')
within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS"
from WEBLOG_VIEWS
group by C_IP, CS_USER_AGENT, SESSION_DT
order by SESSION_DT
[code]....
the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively.
View 6 Replies
View Related
Sep 3, 2012
I've created a table, i have to insert data into it. Herwith Create table statement:
CREATE TABLE IOSTAT_MAC (
IS_DATE DATE,
IS_RS NUMBER(4,2),
IS_WS NUMBER(2,2),
IS_KRS NUMBER(5,2),
IS_KWS NUMBER(5,2),
[code]....
herewith data I have to insert
insert into iostat_mac values(to_date('2012/03/28 08:00:00 AM'),'653.6','20.7','15392.0','451.8','0.0','5.5','0.0','8.2','0','64','/vol/sun_dc_u2');
Receive ora-01861 error when trying to insert.Suspected it had to do with the date format, I changed the nls parameter for my session to include the time format, but to no avail.
View 4 Replies
View Related
Jul 27, 2012
Here is the code :
CREATE OR REPLACE PROCEDURE SEND_MESSAGE_PRC (P_MESSAGE_CONTROL_id varchar2)
IS
BEGIN
DBMS_SCHEDULER.DROP_job(job_name => 'ftp_job');
DBMS_SCHEDULER.drop_program (program_name => 'ftp_prg');
DBMS_SCHEDULER.create_program(program_name => 'ftp_prg',
[code]...
When I execute this : I get an error saying that "too many declarations of set_job_argument_value match this call"..It worked before.
View 2 Replies
View Related
Jan 9, 2013
I would like add an additional column to the data below:
create table test(
id number
cust_num varchar2(5));
[Code]....
Result:
001, AODER, 'Y'
001, BODER, 'Y'
001, CODER, 'Y'
001, DODER, 'Y'
001, 'NONE', 'Y'
001, 'NONE', 'Y'
[Code]...
I would like to add an additional column indicator (Y or N) to specify which ID's do not contain all records of 'NONE'. There can be an occurrence of 'NONE' as long as there is another cust_num different to 'NONE' These should be marked as 'Y' but in cases where all the ID's cust_num = 'NONE' only then these should be marked as 'N'.
View 5 Replies
View Related
May 23, 2012
TABLE 1 : COSTS
I have a table of Costs. We have Jobs that run and there will be a cost associated with a particular machine.So JobNo 1 may run on Machine A and have a cost of 50 dollars. Although its now shown below JobNO 1 could run on MachineB and so on.
JOBNO MACHINE COST
1 MachineA 50
2 MachineA 70
3 MachineA 100
TABLE 2: OPERATIONS
We have operators (PERSONCODE) run the jobs on the machines. So Job 1 may be run by PERSONCODE 8 (e.g. Tony) and it may run on MachineA or MachineB.Multipe people may run a particular job. The PERSONCODE will be unique to the Job and it is actually unique to the list. A person never works on more than one job.
JOBNO MACHINE PERSONCODE
1 MachineA8
1 MachineA7
1 MachineB6
2 MachineA3
2 MachineA2
2 MachineA1
3 MachineA4
DESIRED RESULT:
I need to assign the COSTs to the OPERATIONS table but only want it to show next to one person (next to the appropriate machine).
DESIRED RESULT:
JOBNO MACHINE PERSONCODE COST
1 MachineA8 50
1 MachineA7
1 MachineB6
2 MachineA3 70
2 MachineA2
2 MachineA1
3 MachineA4 100
[code]....
View 5 Replies
View Related
Dec 11, 2008
I have a field called fullname that outputs records with fullname of people.
Here is what I have when I do this sql:
Select fullname from tableOne;
John Jones
Bill Aronsen
Sam Baker
George Williams
Dave Smith
I would like to sort in order of last name but cant figure out how to do the sql:
Bill Aronsen
Sam Baker
John Jones
Dave Smith
George Williams
View 1 Replies
View Related
Jun 16, 2010
how does sorting on multiple columns work
suppose my query is
select * from person order by first_name desc
and sys_person_id asc
this query works , but is this write way to sort on multiple column ?
View 12 Replies
View Related
Nov 20, 2008
I have an urgent request which is pending with the following problem.
Problem :
I have a table which contains data of various datatypes like alphanumeric,varchar and number.
Now my query is " how to sort the data of the table using alphanumeric field"
How to select the data in a required(MyRequirement) sort order.
Data
============
12.4PI1
12.4
12.2
12.4T
12.3PI1
[Code]....
afterSorting(which I am getting Now)
============
12.2
12.3PI1
12.4
12.4PI1
12.4PI10
12.4PI11
[Code]...
MyRequirement
===============
12.2
12.3PI1
12.4
12.4PI1
12.4PI2
12.4PI3
12.4PI10
[Code]..
Means it has to sort the data order by lefthand side of PI and also righthand side of PI.
Pls check the attachment if you are not getting the above data in correct order.
View 22 Replies
View Related
Jun 22, 2011
I want a trigger i have made a software abut school system i need a trigger to sort out the positions...if total number like 100,99,98 than in positions column 1,2,3 but if total marks same like 100,100 in position column shows 1,2..i need if the marks are same than in position column also same like if marks 100,100 in position column shows 1,1
View 3 Replies
View Related
Oct 17, 2012
Query result gives out put like in the following order
CITY NAME
CHENNAI
DELHI
LONDON
RIO DE GENARO
How to get output in the following sort order
LONDON
CHENNAI
DELHI
RIO DE GENARO
i dont want to hardcode the column values ..it will be great if i get option to generate dynamically.
View 9 Replies
View Related
Aug 11, 2010
I've got a oracle install [non production, but devel] that is a tad screwed up. We moved the box and as a result changed the hostname to match the new naming scheme. Ever since then OracleEM has been somewhat confused. In anycase, I don't want OEM anyways now. Plan is to learn SQLplus.
That being said I've used emctl to shut down dbconsole, but it seems there is something somewhere that keeps restarting 2 processes that like to sit around and take up 100% cpu. I can kill them, they stay dead for a few hours then crop up again.I was able to find this out about them:
[jmacdonald@devoracle ~]$ ps auxwww|grep 2033
oracle 20334 84.1 12.3 994052 255824 ? Rs Aug10 1740:43 ora_j000_orcl
oracle 20336 80.9 14.1 998140 294288 ? Ss Aug10 1674:18 ora_j001_orcl
And then this, which caused me to conlucde its OracleEM:
SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.process in (20334,20336)
[code]...
View 6 Replies
View Related
Jul 3, 2012
i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows
first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.
Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.
Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.
We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.
CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2
(30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30));
INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO');
insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO');
insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL);
INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL);
SQL> SELECT * FROM OM_MC_MASTER;
[code]....
View 6 Replies
View Related
Dec 9, 2011
We have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g) procedure .
The procedure builds the string by inserting single quotes in the following manner.
P_BU_LST is the parameter which have comma separated values
'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value
i.e., '1234','3456','4577
v_bu_lst := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';
This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS
ie.,
SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;
INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577
here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.
But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.
View 8 Replies
View Related
Mar 21, 2013
I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as
(select 101 as ID, '24/05/1899' as create_date from dual
union all
select 101, '32/03/2012' from dual
union all
select 102 ,'30/02/2012' from dual
union all
select 101 , '29/02/2013' from dual
[code]...
View 16 Replies
View Related
Jul 1, 2012
Every command I issue in RMAN I get these errors:
RMAN-03002: failure of report command at 07/01/2012 12:33:40 ORA-01861: literal does not match format stringHow should I resolve it?
View 2 Replies
View Related
Jul 16, 2010
I know the only way to guarantee a specific sort order result when querying table is by using the order by clause. However, I have an issue where I do not have access to the code for the web user interface of a very lightly used interface (has two users). In that user interface is a drop down box that is populated by a table - one table. The drop down box is populated by the query "select [column name] from [table name]". Right now there are 400+ rows in that table total, so it's small but not having the items ordered is a pain.
I would like to alter something on the db side so the result of "select [column name] from [table name]" is an ordering by the column descending. I don't want the sort order to be the same for all queries (including joins and all) just want to control the order for that one query. It is Oracle9i.
View 1 Replies
View Related
Jan 26, 2011
I have a master detail form that presents a list of 22 items that can be checked or unchecked depending on whether the test is needed.
The issue I have is trying to order or sequence the list in a specific order without a database column.
Here is the code I am trying to use in a post query trigger:
CASE :SAMPLE_TESTS.TESTCODE
when 'L001'
then :SAMPLE_TESTS.SEQ := '02' || :SAMPLE_TESTS.TESTCODE;
when 'L002'
then :SAMPLE_TESTS.SEQ := '03' || :SAMPLE_TESTS.TESTCODE;
when 'L003'
then :SAMPLE_TESTS.SEQ := '04' || :SAMPLE_TESTS.TESTCODE;
[Code]..
The non-database field is character with a length of 6.
When I try to use this field in the order by property of the data block it is unable to perform query.
Is there another way?
View 1 Replies
View Related
Jan 10, 2012
i got the data like
select * from Table1
SNO Name B_MONTH
--------------------
101 A Mar
102 B Jan
103 C Feb
104 D Apr
105 f May
106 G Jun
Select * from Table2
107 H Dec
108 I Aug
109 J Oct
110 L Jul
111 M Sep
112 N Nov
select * from table1 union select * from table2 order by 3
The B_MONTH column is in Varchar2. Expected output should be
Output:
Jan
Feb
Mar
Apr
.
.
.
.
Nov
Dec
View 8 Replies
View Related
Dec 12, 2010
I have a name field like below; i need a query to display the name field in alphabetical order like in dictionary.
Emp Name
--------
Sam
John
Noel
Alen
Saaem
output would be
---------------
Alen
John
Noel
Saaem
Sam
View 5 Replies
View Related