I have a question regarding the behaviour of sql with OR and fetch
first 1 rows only :
I have a table with data :
ACNO NAME TELNO CITY
ZIP
1000 tim 8887778888 denver
1000 tim 8887778888 denver
56465
1000
denver
2000 1112223333
11111
2000
11111
3000 jack
3000 jack 9998887777 NYC
22222
I want to select the rows with more data . My sql will look like this :
select *
from table 1
where
(ACNO = 1000 and NAME = 'tim' and TELNO = '888777888' and CITY =
'denver' and
ZIP = 56465)
OR
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY =
'denver' and
ZIP = ' ')
or
(ACNO = 1000 and NAME = 'tim' and TELNO =' 8887778888' and CITY = ' '
and
ZIP = ' ' )
or
(ACNO = 1000 and NAME = 'tim' and TELNO =' ' and CITY = ' ' and
ZIP = ' ' )
OR
(ACNO = 1000 and NAME = ' ' and TELNO =' ' and CITY = ' ' and
ZIP = ' ' )
FETCH FIRST 1 ROW ONLY ;
my aim is to get row # 2 ie
1000 tim 8887778888 denver
56465
the row with most amount of data. But I am getting row #1 ie
1000 tim 8887778888 denver
How can I accomplish this ? I was under the impression that DB2 will
fetch records in the order of the WHERE clause.
Can anybody help ? This is on DB2 V7 on Z/os , but I think the answer
will be the same for all DB2s
Thanks 8 2254
Roger wrote: I have a question regarding the behaviour of sql with OR and fetch first 1 rows only :
I have a table with data :
ACNO NAME TELNO CITY ZIP 1000 tim 8887778888 denver 1000 tim 8887778888 denver 56465 1000 denver 2000 1112223333 11111 2000 11111 3000 jack 3000 jack 9998887777 NYC 22222
I want to select the rows with more data . My sql will look like this :
select * from table 1 where (ACNO = 1000 and NAME = 'tim' and TELNO = '888777888' and CITY = 'denver' and ZIP = 56465) OR (ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = 'denver' and ZIP = ' ') or (ACNO = 1000 and NAME = 'tim' and TELNO =' 8887778888' and CITY = ' ' and ZIP = ' ' ) or (ACNO = 1000 and NAME = 'tim' and TELNO =' ' and CITY = ' ' and ZIP = ' ' ) OR (ACNO = 1000 and NAME = ' ' and TELNO =' ' and CITY = ' ' and ZIP = ' ' ) FETCH FIRST 1 ROW ONLY ;
my aim is to get row # 2 ie 1000 tim 8887778888 denver 56465
the row with most amount of data. But I am getting row #1 ie 1000 tim 8887778888 denver
How can I accomplish this ? I was under the impression that DB2 will fetch records in the order of the WHERE clause.
Can anybody help ? This is on DB2 V7 on Z/os , but I think the answer will be the same for all DB2s
Thanks
I don't see a way to do this with just ORs in 1 SELECT. Make each branch
into a SELECT, adding something like 1 as goodness, 2 as goodness, etc.,
to each SELECT indicating how much data each SELECT is getting. UNION
all of the SELECTS, ORDER BY goodness then FETCH FIRST 1 ROW ONLY.
"Roger" <wo**********@g mail.com> wrote in message
news:11******** **************@ y41g2000cwy.goo glegroups.com.. . How can I accomplish this ? I was under the impression that DB2 will fetch records in the order of the WHERE clause.
No, DB2 fetches the rows in the manner that the optimizer thinks is most
efficient. That should not make any difference to you if the SQL statement
is functionally correct according to relational semantics.
how can i accomplish what I am trying to do ?
Mark A wrote: "Roger" <wo**********@g mail.com> wrote in message news:11******** **************@ y41g2000cwy.goo glegroups.com.. . How can I accomplish this ? I was under the impression that DB2 will fetch records in the order of the WHERE clause.
No, DB2 fetches the rows in the manner that the optimizer thinks is most efficient. That should not make any difference to you if the SQL statement is functionally correct according to relational semantics.
Yor sample data were folded. So, I'm not sure my interpretation is
right.
Any way, this is my understasndings and one sample solution.
----------------------- Commands Entered ------------------------
CREATE TABLE Roger.table
(ACNO SMALLINT NOT NULL
,NAME VARCHAR(10)
,TELNO CHAR(10)
,CITY VARCHAR(10)
,ZIP CHAR(5)
);
------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------- Commands Entered --------------------
select *
from Roger.table;
--------------------------------------------------------------------
ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 denver
1000 tim 8887778888 denver 56465
1000 denver
2000 1112223333 11111
2000 11111
3000 jack
3000 jack 9998887777 NYC 22222
7 record(s) selected.
------------------------- Commands Entered -------------------------
select *
from Roger.table
where
(ACNO = 1000 and NAME = 'tim' and TELNO = '888777888'
and CITY = 'denver' and ZIP = '56465')
OR
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888'
and CITY = 'denver' and ZIP = ' ')
or
(ACNO = 1000 and NAME = 'tim' and TELNO =' 8887778888'
and CITY = ' ' and ZIP = ' ' )
or
(ACNO = 1000 and NAME = 'tim' and TELNO =' '
and CITY = ' ' and ZIP = ' ' )
OR
(ACNO = 1000 and NAME = ' ' and TELNO =' '
and CITY = ' ' and ZIP = ' ' )
ORDER BY
ACNO, NAME DESC, TELNO, CITY, ZIP
FETCH FIRST 1 ROW ONLY ;
--------------------------------------------------------------------
ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 denver
1 record(s) selected.
Roger wrote: I have a question regarding the behaviour of sql with OR and fetch first 1 rows only :
I have a table with data :
ACNO NAME TELNO CITY ZIP 1000 tim 8887778888 denver 1000 tim 8887778888 denver 56465 1000 denver 2000 1112223333 11111 2000 11111 3000 jack 3000 jack 9998887777 NYC 22222
I want to select the rows with more data . My sql will look like this :
select * from table 1 where (ACNO = 1000 and NAME = 'tim' and TELNO = '888777888' and CITY = 'denver' and ZIP = 56465) OR (ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = 'denver' and ZIP = ' ') or (ACNO = 1000 and NAME = 'tim' and TELNO =' 8887778888' and CITY = ' ' and ZIP = ' ' ) or (ACNO = 1000 and NAME = 'tim' and TELNO =' ' and CITY = ' ' and ZIP = ' ' ) OR (ACNO = 1000 and NAME = ' ' and TELNO =' ' and CITY = ' ' and ZIP = ' ' ) FETCH FIRST 1 ROW ONLY ;
my aim is to get row # 2 ie 1000 tim 8887778888 denver 56465
the row with most amount of data. But I am getting row #1 ie 1000 tim 8887778888 denver
How can I accomplish this ? I was under the impression that DB2 will fetch records in the order of the WHERE clause.
Can anybody help ? This is on DB2 V7 on Z/os , but I think the answer will be the same for all DB2s
Thanks
DECLARE GLOBAL TEMPORARY TABLE Test (ACNO INT, NAME CHAR(10), TELNO
CHAR(10), CITY CHAR(10), ZIP CHAR(5))
INSERT INTO SESSION.Test(AC NO, NAME, TELNO, CITY, ZIP) VALUES (1000,
'tim', '8887778888', 'denver', '')
INSERT INTO SESSION.Test(AC NO, NAME, TELNO, CITY, ZIP) VALUES (1000,
'tim', '8887778888', 'denver', '56465')
INSERT INTO SESSION.Test(AC NO, NAME, TELNO, CITY, ZIP) VALUES (1000,
'', '', 'denver', '')
INSERT INTO SESSION.Test(AC NO, NAME, TELNO, CITY, ZIP) VALUES (2000,
'', '1112223333', '', '11111')
INSERT INTO SESSION.Test(AC NO, NAME, TELNO, CITY, ZIP) VALUES (2000,
'', '', '', '11111')
INSERT INTO SESSION.Test(AC NO, NAME, TELNO, CITY, ZIP) VALUES (3000,
'jack', '', '', '')
INSERT INTO SESSION.Test(AC NO, NAME, TELNO, CITY, ZIP) VALUES (3000,
'jack', '9998887777', 'NYC', '22222')
With your query, you can add a number and UNION, then ORDER BY the
number.
SELECT 1, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY =
'denver' and ZIP = '56465' \
UNION SELECT 2, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY =
'denver' and ZIP = '' \
UNION SELECT 3, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = '' and
ZIP = '' \
UNION SELECT 4, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = 'tim' and TELNO = '' and CITY = '' and ZIP = ''
\
UNION SELECT 5, ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE
ACNO = 1000 and NAME = '' and TELNO = '' and CITY = '' and ZIP = '' \
ORDER BY 1 \
FETCH FIRST ROW ONLY
Though, there is no reason to do that. You should be able to use IN and
ORDER BY DESC:
SELECT ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE ACNO = 1000
\
and NAME IN ('tim', '') and TELNO IN ('8887778888', '') and CITY IN
('denver', '') and ZIP IN('56465', '') \
ORDER BY NAME DESC, TELNO DESC, CITY DESC, ZIP DESC \
FETCH FIRST ROW ONLY
B.
Brian Tkatch wrote: Though, there is no reason to do that. You should be able to use IN and ORDER BY DESC:
SELECT ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE ACNO = 1000 \ and NAME IN ('tim', '') and TELNO IN ('8887778888', '') and CITY IN ('denver', '') and ZIP IN('56465', '') \ ORDER BY NAME DESC, TELNO DESC, CITY DESC, ZIP DESC \ FETCH FIRST ROW ONLY
1) I made some mistakes(For example:first condition for TELNO =
'888777888', and forgot some DESC keyword).
Here is a corrected query.
------------------------- Commands Entered -------------------------
select *
from Roger.table
where
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888'
and CITY = 'denver' and ZIP = '56465')
OR
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888'
and CITY = 'denver' and ZIP = ' ')
OR
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888'
and CITY = ' ' and ZIP = ' ' )
OR
(ACNO = 1000 and NAME = 'tim' and TELNO =' '
and CITY = ' ' and ZIP = ' ' )
OR
(ACNO = 1000 and NAME = ' ' and TELNO =' '
and CITY = ' ' and ZIP = ' ' )
ORDER BY
ACNO DESC, NAME DESC, TELNO DESC, CITY DESC, ZIP DESC
FETCH FIRST 1 ROW ONLY ;
--------------------------------------------------------------------
ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 denver 56465
1 record(s) selected.
2) If all data are such that if a column is blank then later columns
also all blank, your query will work well.
In other words, there is no following data.
ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 56465
But, If there were such data, your query would return incorrect result.
I want show you this with very simple example.
If there are only two following data.
ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 56465
1000 tim 8887778888
Your query returns
ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888 56465
But, this data not match any WHERE condition of Roger's original query.
My query returns
ACNO NAME TELNO CITY ZIP
------ ---------- ---------- ---------- -----
1000 tim 8887778888
This match with 3rd condition of Roger's original query.
(ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = ' '
and
ZIP = ' ' )
So, it depends on the characteristics of data that your query works
well or not.
Tonkuma wrote: Brian Tkatch wrote: Though, there is no reason to do that. You should be able to use IN and ORDER BY DESC:
SELECT ACNO, NAME, TELNO, CITY, ZIP FROM SESSION.Test WHERE ACNO = 1000 \ and NAME IN ('tim', '') and TELNO IN ('8887778888', '') and CITY IN ('denver', '') and ZIP IN('56465', '') \ ORDER BY NAME DESC, TELNO DESC, CITY DESC, ZIP DESC \ FETCH FIRST ROW ONLY 1) I made some mistakes(For example:first condition for TELNO = '888777888', and forgot some DESC keyword). Here is a corrected query. ------------------------- Commands Entered ------------------------- select * from Roger.table where (ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = 'denver' and ZIP = '56465') OR (ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = 'denver' and ZIP = ' ') OR (ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = ' ' and ZIP = ' ' ) OR (ACNO = 1000 and NAME = 'tim' and TELNO =' ' and CITY = ' ' and ZIP = ' ' ) OR (ACNO = 1000 and NAME = ' ' and TELNO =' ' and CITY = ' ' and ZIP = ' ' ) ORDER BY ACNO DESC, NAME DESC, TELNO DESC, CITY DESC, ZIP DESC FETCH FIRST 1 ROW ONLY ; --------------------------------------------------------------------
ACNO NAME TELNO CITY ZIP ------ ---------- ---------- ---------- ----- 1000 tim 8887778888 denver 56465
1 record(s) selected.
2) If all data are such that if a column is blank then later columns also all blank, your query will work well. In other words, there is no following data. ACNO NAME TELNO CITY ZIP ------ ---------- ---------- ---------- ----- 1000 tim 8887778888 56465
But, If there were such data, your query would return incorrect result. I want show you this with very simple example.
If there are only two following data.
ACNO NAME TELNO CITY ZIP ------ ---------- ---------- ---------- ----- 1000 tim 8887778888 56465 1000 tim 8887778888
Your query returns
ACNO NAME TELNO CITY ZIP ------ ---------- ---------- ---------- ----- 1000 tim 8887778888 56465
But, this data not match any WHERE condition of Roger's original query.
My query returns
ACNO NAME TELNO CITY ZIP ------ ---------- ---------- ---------- ----- 1000 tim 8887778888
This match with 3rd condition of Roger's original query. (ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = ' ' and ZIP = ' ' )
So, it depends on the characteristics of data that your query works well or not.
Heh, Tonk. :)
Thanx for the correction. I think i just made some assumptions.
B.
The result you are searching for is difficult to discern from the
limited information you have provided. Given the following data; which
row has "more" data?
ACNO NAME TELNO CITY ZIP
1000 tim 8887778888 denver
1000 tim denver 56465
If the NAME, TELNO, CITY, and ZIP are slways the same for each account
(ACNO) and you actually want to get all of the information available
from multiple rows - not quite what you stated in the original request;
then the following is an interesting alternative:
select ACNO, max(NAME), max(TELNO), max(CITY), max(ZIP) from table
group by ACNO;
This will yield one row for each ACNO, containing as much information as
can be gathered from multiple source rows. Qualification can be added to
the query to limit the results to specific ACNOs.
Phil Sherman
Roger wrote: I have a question regarding the behaviour of sql with OR and fetch first 1 rows only :
I have a table with data :
ACNO NAME TELNO CITY ZIP 1000 tim 8887778888 denver 1000 tim 8887778888 denver 56465 1000 denver 2000 1112223333 11111 2000 11111 3000 jack 3000 jack 9998887777 NYC 22222
I want to select the rows with more data . My sql will look like this :
select * from table 1 where (ACNO = 1000 and NAME = 'tim' and TELNO = '888777888' and CITY = 'denver' and ZIP = 56465) OR (ACNO = 1000 and NAME = 'tim' and TELNO = '8887778888' and CITY = 'denver' and ZIP = ' ') or (ACNO = 1000 and NAME = 'tim' and TELNO =' 8887778888' and CITY = ' ' and ZIP = ' ' ) or (ACNO = 1000 and NAME = 'tim' and TELNO =' ' and CITY = ' ' and ZIP = ' ' ) OR (ACNO = 1000 and NAME = ' ' and TELNO =' ' and CITY = ' ' and ZIP = ' ' ) FETCH FIRST 1 ROW ONLY ;
my aim is to get row # 2 ie 1000 tim 8887778888 denver 56465
the row with most amount of data. But I am getting row #1 ie 1000 tim 8887778888 denver
How can I accomplish this ? I was under the impression that DB2 will fetch records in the order of the WHERE clause.
Can anybody help ? This is on DB2 V7 on Z/os , but I think the answer will be the same for all DB2s
Thanks This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jeremy |
last post by:
I am relatively inexperienced with SQL, and I am trying to learn how
to analyze some data with it.
I have a table with the following information.
COMPANY ID , DATE, MarektValue
I would like to select the largest 100 marketvalues for each date in
my table. How can I do this efficiently in MySql.
|
by: Mike |
last post by:
Hello,
I have a few rather urgent questions that I hope someone can help with (I
need to figure this out prior to a meeting tomorrow.) First, a bit of
background: The company I work for is developing a web-based application,
one part of which involves allowing the user the ability to page through
transaction "history" information.
The _summary_ history table will have the following fields: ServiceName,
Date, User-Ref1, User-Ref2,...
|
by: Thomasb |
last post by:
With a background in MS SQL Server programming I'm used to temporary
tables. Have just started to work with DB2 ver 7 on z/OS and stumbled
into the concept of GLOBAL TEMPORARY TABLE.
I have created a temporary database with a tables space. Verified that
DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER);
INSERT INTO SESSION.TEMP VALUES(10);
SELECT A FROM SESSION.TEMP;
works from a query tool.
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I COULD be wrong... :) I've tried the
access group...twice...and all I get is "Access doesn't like ".", which I know,
or that my query names are too long, as there's a limit to the length of the SQL
statement(s). But this works when I don't try to...
|
by: Brad |
last post by:
I need to select the Top 50 records from a table of over 10,000 records and
all is working just fine, but I have a what if question.
What if the 50th record's value matches the 51st record (and possible the
52nd and so on)? Does the Select Command also include those records or no?
If not, how is the 50th record determined?
Thanks for the information.
Brad
| |
by: Regnab |
last post by:
I often want to query the database with certain parameters in my query
which I do not want included in the query result.
The problem is when I want to group on 1, sum the 2nd (and group by)
and use a parameter on the 3rd (but don't want to group by it). For
example, I might want all records later than a certain date, but don't
want to group on the date. In the past I've made 2 queries, the first
choosing the records with the date...
|
by: GreatAlterEgo |
last post by:
Hi,
This is my query which is embedded in a COBOL program.
EXEC SQL
SELECT DATE, AGE, DURATION, AMT
INTO :LDATE, :L.AGE, :L.DURATION, :L.AMT
FROM TAB1
WHERE CODE = :KEY.CODE
AND SET = :KEY.SET
AND DATE <= :KEY.DATE
|
by: JJ |
last post by:
How do I set one field to have the updated timestamp, and another to have
the created timestamp?
I want to do this directly from code generated from DB Designer if
possible?!
JJ
|
by: rbg |
last post by:
I did use query plans to find out more. ( Please see the thread BELOW)
I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :
where PermitID like @WorkType
order by WorkStart DESC
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |