473,396 Members | 2,038 Online

# SQL Problem

Hello

I would like to obtain all the fields of a table where the two fields C1 and
C2 from this table are distinct, what is the request ? I can't find it ...
sorry, and thank you
Jul 20 '05 #1
3 1989
Patrick,

What do you mean by "the two fields C1 and C2 from this
table are distinct" ?

If you mean C1 is not equal to C2 (or "C1 is distinct
from C2") then you just need

select * from T
where C1 <> C2

But if you want a result set where the pairs (C1,C2)
are not duplicated in more than one row, your question is
not well-defined. If that is what you want, what would
you want if this is your table?

C1 C2 C3
---------- ---- ----
VINET 5 10
VINET 5 11
VINET 5 12
TOMSP 5 13
TOMSP 5 13
TOMSP 6 18
TOMSP 7 19
TOMSP 6 19
TOMSP 7 18

For example, one of the distinct pairs (C1, C2) is (VINET, 5).
If you only want one row for these values, do you want 10 or 11
in column C3? Even if it doesn't matter, you need to be precise
about what you want in order to write the query.

-- Steve Kass
-- Drew University
-- Ref: D1F0FE10-E0DF-4AA4-81BE-ECE3134096E7

lavasseur patrick wrote:
Hello

I would like to obtain all the fields of a table where the two fields C1 and
C2 from this table are distinct, what is the request ? I can't find it ...
sorry, and thank you

Jul 20 '05 #2
you're right

I would like
C1 C2 C3
---------- ---- ----
VINET 5 10
VINET 5 11
VINET 5 12
TOMSP 5 13
TOMSP 5 13
TOMSP 6 18
TOMSP 7 19
TOMSP 6 19
TOMSP 7 18
and to obtain
C1 C2 C3
---------- ---- ----
VINET 5 10
TOMSP 5 13

for the result
Jul 20 '05 #3
Patrick,

In this particular case, you could write

select C1, C2, min(C3) as C3
from T
group by C1, C2

but if you have more than three columns or
don't want the minimum value of the third
column, you need a clear definition of what
values you want past the first two columns.

Do you want any value? Must the remaining column
values all come from the same row? Do you want the
one with the smallest or largest value of some
specific column?

Generally queries like this might look like

select C1, C2, C3, C4, C5
from T T1
where not exists (
select * from T T2
where T2.C1 = T1.C1
and T2.C2 = T1.C2
and (
T2.C3 < T1.C3 or (
T2.C3 = T1.C3 and T2.C4 < T1.C4) or (
T2.C3 = T1.C3 and T2.C4 = T1.C4 and T2.C5 < T1.C5)
)
)

SK
lavasseur patrick wrote:
you're right

I would like

C1 C2 C3
---------- ---- ----
VINET 5 10
VINET 5 11
VINET 5 12
TOMSP 5 13
TOMSP 5 13
TOMSP 6 18
TOMSP 7 19
TOMSP 6 19
TOMSP 7 18

and to obtain

C1 C2 C3
---------- ---- ----
VINET 5 10
TOMSP 5 13

for the result

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

### Similar topics

 11 by: Kostatus | last post by: I have a virtual function in a base class, which is then overwritten by a function of the same name in a publically derived class. When I call the function using a pointer to the derived class... 117 by: Peter Olcott | last post by: www.halting-problem.com 18 by: Ian Stanley | last post by: Hi, Continuing my strcat segmentation fault posting- I have a problem which occurs when appending two sting literals using strcat. I have tried to fix it by writing my own function that does the... 28 by: Jon Davis | last post by: If I have a class with a virtual method, and a child class that overrides the virtual method, and then I create an instance of the child class AS A base class... BaseClass bc = new ChildClass();... 6 by: Ammar | last post by: Dear All, I'm facing a small problem. I have a portal web site, that contains articles, for each article, the end user can send a comment about the article. The problem is: I the comment length... 16 by: Dany | last post by: Our web service was working fine until we installed .net Framework 1.1 service pack 1. Uninstalling SP1 is not an option because our largest customer says service packs marked as "critical" by... 2 by: Mike Collins | last post by: I cannot get the correct drop down list value from a drop down I have on my web form. I get the initial value that was loaded in the list. It was asked by someone else what the autopostback was... 0 by: =?Utf-8?B?am8uZWw=?= | last post by: Hello All, I am developing an Input Methop (IM) for PocketPC / Windows Mobile (PPC/WM). On some devices the IM will not start. The IM appears in the IM-List but when it is selected from the... 1 by: sherifbk | last post by: Problem description ============== - I have 4 clients and 1 server (SQL server) - 3 clients are Monitoring console 1 client is operation console - Monitoring console collects some data from... 9 by: AceKnocks | last post by: I am working on a framework design problem in which I have to design a C++ based framework capable of solving three puzzles for now but actually it should work with a general puzzle of any kind and I... 0 by: ryjfgjl | last post by: In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files... 1 by: nemocccc | last post by: hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions? 0 by: Hystou | last post by: There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2... 0 by: Hystou | last post by: Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can... 0 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,... 0 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... 0 by: Hystou | last post by: Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows... 0 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... 0 by: agi2029 | last post by: Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...