Hi,
i try to find a query for selecting one arbitrarily chosen row from
each group of rows.
Example:
$ db2 "select * from T"
A B C
----------- ----------- -----------
100 1 10
100 20 2
101 3 3
3 record(s) selected.
The result should be 'Select A From T group by A' but with one (B,C)-
pair for each group.
So {(100, 1, 10), (101, 3, 3)} and {(100, 20, 2), (101, 3, 3)} would
both be valid results,
but not {(100, 1, 2), (101, 3, 3)}.
Is there a way to formulate this in SQL?
Thanks,
Ralf 1 3976
Ralf wrote:
Hi,
i try to find a query for selecting one arbitrarily chosen row from
each group of rows.
Example:
$ db2 "select * from T"
A B C
----------- ----------- -----------
100 1 10
100 20 2
101 3 3
3 record(s) selected.
The result should be 'Select A From T group by A' but with one (B,C)-
pair for each group.
So {(100, 1, 10), (101, 3, 3)} and {(100, 20, 2), (101, 3, 3)} would
both be valid results,
but not {(100, 1, 2), (101, 3, 3)}.
Is there a way to formulate this in SQL?
SELECT x.a, x.b, x.c
FROM ( SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.a) AS rn
FROM yourTable AS t ) AS x
WHERE x.rn = 1
A B C
----------- ----------- -----------
100 1 10
101 3 3
2 record(s) selected.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Joe Six-Pack |
last post by:
Hi,
Im having problems in randomly selecting an element in an array that
has not been selected before.. in other words, I have an array of
answers to questions, then I want to select 5, with one of them being
the correct answer to the question, and the others not..
Here is my code.. (I already have the correct answer at array position
)
...
|
by: Ben Gribaudo |
last post by:
Hello!
I am trying to query a MySQL table and retrieve either the earliest or the
latest message in each thread. The messages are in a table with MsgID being
the primary key and fThreadID identifying to which thread the message
belongs.
Examples below.
Any ideas on the SQL I need to use here?
|
by: karthik |
last post by:
Hi,
I having problems returning only the first row of data for each GROUP
for the query below. Can someone please help? Thanks.
SELECT bid, xact_date, xact_time
FROM badge_history bh
GROUP BY bid, xact_date, xact_time
ORDER BY bid, xact_date DESC, xact_time DESC
So if the data returned by the above query is:
|
by: throat.wobbler.mangrove |
last post by:
Hi,
I was hoping someone may be able to help me with a tricky T-SQL
problem.
I need to come up with a SELECT statement that does basically the
following:
Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from
..tblPersonInfo group by RCRD_REFNO
|
by: Andreas Håkansson |
last post by:
I've been using the Muenchian method to group XML data, however
I have run into a situation where I am unsure how to solve it. What I
need to do is select distinct groups of items.
I have an XML dokument which looks like the one below, where I
have 1-n item nodes and each item node can have 2-n detail nodes.
The group node can have a value...
| |
by: Henrik Larsson |
last post by:
Hi,
I need help with selecting the following rows from a table looking like this:
ID IP
Query 1 -> 1 2.2.2.2
(ie first IP 1 1.1.1.1 <- Query 2
for each ID) 1 3.3.3.3 (ie second IP for each ID)
-> 2 5.5.5.5
2 6.6.6.6 <-
|
by: vince |
last post by:
Is there an easy way to determine which radio button in a
group has been chosen, besides having to put code in the
checkchanged or clicked event for each button in the
group...???
thanks,
vince
|
by: J |
last post by:
I am editing a pre-existing view.
This view is already bringing data from 40+ tables so I am to modify it
without screwing with anything else that is already in there.
I need to (left) join it with a new table that lists deposits and the
dates they are due. What I need is to print, for each record in the
view, the due date for the next...
|
by: doulos05 |
last post by:
Ok, I'm sure the answer is right in front of me, but I can't see if
for the life of me. Here's my xml document:
<price_guide>
<group><name>Wacky Widgets</name>
<product>
<product_id>1</product_id>
<description>Widget 1</description>
<sell>
<quantity>500</quantity>
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
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...
|
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...
| |