Hi All,
I'm using the sample table:
ID | CODE | V1 | V2 | V3
----------------------------------------------------------------
1 3 10 3 43
1 4 9 8 22
1 2 6 2 55
1 5 57 12 6
I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)
and their respective CODEs.
For the above table the returned record for ID=1 should be:
v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2
currently I've got a very messy solution, I'm
looking for an elegant way to do this.
10x,
Assaf.
3  1301 
You do not say how you want to hand duplicates, where the same value
is the max for more than one code.
SELECT X.*,
(select min(CODE) from Whatever as W1
where W1.ID = X.ID
and W1.V1 = X.V1) as code_v1,
(select min(CODE) from Whatever as W2
where W2.ID = X.ID
and W2.V2 = X.V1) as code_v2,
(select min(CODE) from Whatever as W3
where W3.ID = X.ID
and W3.V3 = X.V3) as code_v3
FROM (select ID, max(V1) as V1, max(V2) as V2, max(V3) as V3
from Whatever
group by ID) as X
Roy Harvey
Beacon Falls, CT
On 3 Jul 2006 06:20:33 -0700, st*****@gmail.com wrote:
>Hi All,
I'm using the sample table:
ID | CODE | V1 | V2 | V3
----------------------------------------------------------------
1 3 10 3 43
1 4 9 8 22
1 2 6 2 55
1 5 57 12 6
I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)
and their respective CODEs.
For the above table the returned record for ID=1 should be:
v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2
currently I've got a very messy solution, I'm
looking for an elegant way to do this.
10x,
Assaf.
Hi Roy,
10x for your prompt reply.
lets say duplicates are allowed, how can i simplify the solutions?
Regards,
Assaf.
Roy Harvey wrote:
You do not say how you want to hand duplicates, where the same value
is the max for more than one code.
SELECT X.*,
(select min(CODE) from Whatever as W1
where W1.ID = X.ID
and W1.V1 = X.V1) as code_v1,
(select min(CODE) from Whatever as W2
where W2.ID = X.ID
and W2.V2 = X.V1) as code_v2,
(select min(CODE) from Whatever as W3
where W3.ID = X.ID
and W3.V3 = X.V3) as code_v3
FROM (select ID, max(V1) as V1, max(V2) as V2, max(V3) as V3
from Whatever
group by ID) as X
Roy Harvey
Beacon Falls, CT
On 3 Jul 2006 06:20:33 -0700, st*****@gmail.com wrote:
Hi All,
I'm using the sample table:
ID | CODE | V1 | V2 | V3
----------------------------------------------------------------
1 3 10 3 43
1 4 9 8 22
1 2 6 2 55
1 5 57 12 6
I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)
and their respective CODEs.
For the above table the returned record for ID=1 should be:
v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2
currently I've got a very messy solution, I'm
looking for an elegant way to do this.
10x,
Assaf.
>lets say duplicates are allowed, how can i simplify the solutions?
The example I posted handles duplicates by returning the smallest,
min(CODE). Returning the largest would be easy enough, max(CODE).
Anything else woule become more complicated. Otherwise I think that
pretty much IS the simple solution! 8-)
Roy
On 3 Jul 2006 06:54:11 -0700, st*****@gmail.com wrote:
>Hi Roy,
10x for your prompt reply.
lets say duplicates are allowed, how can i simplify the solutions?
Regards,
Assaf.
Roy Harvey wrote:
>You do not say how you want to hand duplicates, where the same value
is the max for more than one code.
SELECT X.*,
(select min(CODE) from Whatever as W1
where W1.ID = X.ID
and W1.V1 = X.V1) as code_v1,
(select min(CODE) from Whatever as W2
where W2.ID = X.ID
and W2.V2 = X.V1) as code_v2,
(select min(CODE) from Whatever as W3
where W3.ID = X.ID
and W3.V3 = X.V3) as code_v3
FROM (select ID, max(V1) as V1, max(V2) as V2, max(V3) as V3
from Whatever
group by ID) as X
Roy Harvey
Beacon Falls, CT
On 3 Jul 2006 06:20:33 -0700, st*****@gmail.com wrote:
>Hi All,
I'm using the sample table:
ID | CODE | V1 | V2 | V3
----------------------------------------------------------------
1 3 10 3 43
1 4 9 8 22
1 2 6 2 55
1 5 57 12 6
I want to get for a given ID the MAX(V1), MAX(V2), MAX(V3)
and their respective CODEs.
For the above table the returned record for ID=1 should be:
v1=57, v2=12, v3=55, code_v1=5, code_v2=5, code_v3=2
currently I've got a very messy solution, I'm
looking for an elegant way to do this.
10x,
Assaf.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: aznFETISH |
last post by:
Below I found a code to make multiple colums from the output of a DB, how
can I incorporate alternat colors to the multiple row snippet?
<?php
//set the number of columns
$columns = 2;
...
|
by: Philip Smith |
last post by:
Call this a C++ programmers hang-up if you like.
I don't seem to be able to define multiple versions of __init__ in my matrix
class (ie to initialise either from a list of values or from 2...
|
by: Rigga |
last post by:
Hi,
I am new to mysql and need help on how to join tables. I have a database
which contains 4 tables, the main table contains information by date order
and the other 3 contain data also in date...
|
by: Steven An |
last post by:
Howdy,
I need to write an update query with multiple aggregate functions.
Here is an example:
UPDATE t
SET
t.a = ( select avg(f.q) from dbo.foo f where f.p = t.y ),
t.b = ( select sum(f.q)...
|
by: Billy Jacobs |
last post by:
I am using a datagrid to display some data. I need to
create 2 header rows for this grid with columns of
varying spans. In html it would be the following.
<Table>
<tr>
<td colspan=8>Official...
|
by: David Londeck |
last post by:
I am using Visual Basic 2003 and I have written a notepad like application
using the RichTextBox control. I am having trouble trying to emulate
Microsoft Words text block copy/paste feature. In...
|
by: Andrew |
last post by:
Hey all,
I am very new to ASP.Net (and .Net in general), but that isn't stopping the
boss from wanting to begin new projects in it. This latest project has me
kinda stumped and after a couple...
|
by: samoore33 |
last post by:
I want to list all of the items in a dataset in a textbox. The dataset
has multiple tables. When I try to use the code below, even though I
dim myState as the DataTable("state"). It still looks for...
|
by: =?Utf-8?B?TG9zdEluTUQ=?= |
last post by:
Hi All :)
I'm converting VB6 using True DBGrid Pro 8.0 to VB2005 using DataGridView.
True DBGrid has a MultipleLines property that controls whether individual
records span multiple lines. Is...
|
by: sharonrao123 |
last post by:
hello all,
I have a parent gridview company and in this one a nested gridview people, Is it possible to allow the user to select one row or multiple rows from the people gridview using a check box...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Aliciasmith |
last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
| |
