#temptable got order,fname and age.
trying to find two high maxes per each order.
query returns (1) but (2) is correct answer.
supposedly, max(age2) < max(age1).
****
select d.order,case d.t when 1 then s.fname else null end as fname,
case d.t when 1 then s.age else null end as age1,
case d.t when 2 then s.fname else null end as fname,
case d.t when 2 then s.age else null end as age2
from #temptable s
join ( select order, max(age) age, 1 t
from #temptable group by order
union all select order, max(age) age, 2 t
from #temptable group by order) d on d.order = s.order
and s.age = d.age
group by d.order, d.t,s.fname,s.a ge
****
(1)
1 mark1 26
1 mark1 26
2 fred1 19
2 fred1 19
(2)
1 marke1 26 mark2 24
2 fred1 19 fred2 17
--
Sent by ricksql from yahoo in area com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new 3 1841
"ri*****@yahoo. com" <u5*******@spaw nkill.ip-mobilphone.net> wrote in message
news:l.******** *************@h ost-66-81-126-37.rev.o1.com.. . #temptable got order,fname and age. trying to find two high maxes per each order. query returns (1) but (2) is correct answer. supposedly, max(age2) < max(age1). **** select d.order,case d.t when 1 then s.fname else null end as fname, case d.t when 1 then s.age else null end as age1, case d.t when 2 then s.fname else null end as fname, case d.t when 2 then s.age else null end as age2 from #temptable s join ( select order, max(age) age, 1 t from #temptable group by order union all select order, max(age) age, 2 t from #temptable group by order) d on d.order = s.order and s.age = d.age group by d.order, d.t,s.fname,s.a ge ****
(1) 1 mark1 26 1 mark1 26 2 fred1 19 2 fred1 19
(2) 1 marke1 26 mark2 24 2 fred1 19 fred2 17 -- Sent by ricksql from yahoo in area com This is a spam protected message. Please answer with reference header. Posted via http://www.usenet-replayer.com/cgi/content/new
Posting DDL statements (CREATE TABLE statements), sample data
(in the form of INSERT statements), and the expected query result are
all important accompaniments when posing a question.
This is offered untested.
SELECT T1."order", T1.fname AS fname1, T1.age AS age1,
T3.fname AS fname2, T3.age AS age2
FROM #temptable AS T1
LEFT OUTER JOIN
#temptable AS T2
ON T1."order" = T2."order" AND
T2.age > T1.age
LEFT OUTER JOIN
#temptable AS T3
ON T3."order" = T1."order" AND
T3.age < T1.age AND
NOT EXISTS (SELECT *
FROM #temptable AS T4
WHERE T4."order" = T1."order" AND
T4.age > T3.age AND
T4.age < T1.age)
WHERE T2.age IS NULL
ORDER BY T1."order", T1.age, T1.fname
Regards,
jag
Dear John Gilson,
In your posting Re: question from Sun, 31 Aug 2003 11:44:00 GMT you
write: "ri*****@yahoo. com" <u5*******@spaw nkill.ip-mobilphone.net> wrote in message news:l.******** *************@h ost-66-81-126-37.rev.o1.com.. . #temptable got order,fname and age. trying to find two high maxes per each order. query returns (1) but (2) is correct answer. supposedly, max(age2) < max(age1). **** select d.order,case d.t when 1 then s.fname else null end as fname, case d.t when 1 then s.age else null end as age1, case d.t when 2 then s.fname else null end as fname, case d.t when 2 then s.age else null end as age2 from #temptable s join ( select order, max(age) age, 1 t from #temptable group by order union all select order, max(age) age, 2 t from #temptable group by order) d on d.order = s.order and s.age = d.age group by d.order, d.t,s.fname,s.a ge ****
(1) 1 mark1 26 1 mark1 26 2 fred1 19 2 fred1 19
(2) 1 marke1 26 mark2 24 2 fred1 19 fred2 17 -- Sent by ricksql from yahoo in area com This is a spam protected message. Please answer with reference header. Posted via http://www.usenet-replayer.com/cgi/content/new
Posting DDL statements (CREATE TABLE statements), sample data (in the form of INSERT statements), and the expected query result are all important accompaniments when posing a question.
This is offered untested.
SELECT T1."order", T1.fname AS fname1, T1.age AS age1, T3.fname AS fname2, T3.age AS age2 FROM #temptable AS T1 LEFT OUTER JOIN #temptable AS T2 ON T1."order" = T2."order" AND T2.age > T1.age LEFT OUTER JOIN #temptable AS T3 ON T3."order" = T1."order" AND T3.age < T1.age AND NOT EXISTS (SELECT * FROM #temptable AS T4 WHERE T4."order" = T1."order" AND T4.age > T3.age AND T4.age < T1.age) WHERE T2.age IS NULL ORDER BY T1."order", T1.age, T1.fname
This worked for result:
1 marke1 26 mark2 24
2 fred1 19 fred2 17
How it modify it to result three highest maxes, like:
1 marke 26 mark2 24 mark3 20
2 fred1 19 fred2 17 fred3 15
--
Spam protected message from:
Sent by ricksql from yahoo piece of com
Posted via http://www.usenet-replayer.com/cgi/content/new
"ri*****@yahoo. com" <u5*******@spaw nkill.ip-mobilphone.net> wrote in message
news:r.******** *************@[63.127.215.130]... Dear John Gilson,
In your posting Re: question from Sun, 31 Aug 2003 11:44:00 GMT you write:
"ri*****@yahoo. com" <u5*******@spaw nkill.ip-mobilphone.net> wrote in message news:l.******** *************@h ost-66-81-126-37.rev.o1.com.. . #temptable got order,fname and age. trying to find two high maxes per each order. query returns (1) but (2) is correct answer. supposedly, max(age2) < max(age1). **** select d.order,case d.t when 1 then s.fname else null end as fname, case d.t when 1 then s.age else null end as age1, case d.t when 2 then s.fname else null end as fname, case d.t when 2 then s.age else null end as age2 from #temptable s join ( select order, max(age) age, 1 t from #temptable group by order union all select order, max(age) age, 2 t from #temptable group by order) d on d.order = s.order and s.age = d.age group by d.order, d.t,s.fname,s.a ge ****
(1) 1 mark1 26 1 mark1 26 2 fred1 19 2 fred1 19
(2) 1 marke1 26 mark2 24 2 fred1 19 fred2 17 -- Sent by ricksql from yahoo in area com This is a spam protected message. Please answer with reference header. Posted via http://www.usenet-replayer.com/cgi/content/new
Posting DDL statements (CREATE TABLE statements), sample data (in the form of INSERT statements), and the expected query result are all important accompaniments when posing a question.
This is offered untested.
SELECT T1."order", T1.fname AS fname1, T1.age AS age1, T3.fname AS fname2, T3.age AS age2 FROM #temptable AS T1 LEFT OUTER JOIN #temptable AS T2 ON T1."order" = T2."order" AND T2.age > T1.age LEFT OUTER JOIN #temptable AS T3 ON T3."order" = T1."order" AND T3.age < T1.age AND NOT EXISTS (SELECT * FROM #temptable AS T4 WHERE T4."order" = T1."order" AND T4.age > T3.age AND T4.age < T1.age) WHERE T2.age IS NULL ORDER BY T1."order", T1.age, T1.fname
This worked for result: 1 marke1 26 mark2 24 2 fred1 19 fred2 17
How it modify it to result three highest maxes, like: 1 marke 26 mark2 24 mark3 20 2 fred1 19 fred2 17 fred3 15
-- Spam protected message from: Sent by ricksql from yahoo piece of com Posted via http://www.usenet-replayer.com/cgi/content/new
As I think I see where this is going, allow me to suggest a normalization
of relations. Let's assume your data isn't in a temp table as I will be
defining a view. Let's place your data in table T.
CREATE VIEW Rank (order_id, fname, age, rank)
AS
SELECT T1."order", T1.fname, T1.age, COUNT(DISTINCT T2.age)
FROM T AS T1
INNER JOIN
T AS T2
ON T1."order" = T2."order" AND
T2.age >= T1.age
GROUP BY T1."order", T1.fname, T1.age
SELECT R1.order_id, R1.fname AS fname1, R1.age AS age1,
R2.fname AS fname2, R2.age AS age2,
R3.fname AS fname3, R3.age AS age3
FROM Rank AS R1
LEFT OUTER JOIN
Rank AS R2
ON R1.order_id = R2.order_id AND
R2.rank = 2
LEFT OUTER JOIN
Rank AS R3
ON R3.order_id = R1.order_id AND
R3.rank = 3
WHERE R1.rank = 1
Regards,
jag This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Mohammed Mazid |
last post by:
Can anyone please help me on how to move to the next and previous
question?
Here is a snippet of my code:
Private Sub cmdNext_Click()
End Sub
Private Sub cmdPrevious_Click()
showrecord
|
by: Stevey |
last post by:
I have the following XML file...
<?xml version="1.0"?>
<animals>
<animal>
<name>Tiger</name>
<questions>
<question index="0">true</question>
<question index="1">true</question>
</questions>
|
by: nospam |
last post by:
Ok, 3rd or is it the 4th time I have asked this question on Partial Types,
so, since it seems to me that Partial Types is still in the design or
development stages at Microsoft, I am going to ask it differently.
FOUR QUESTIONS:
The background:
I got three (3) files
|
by: Ekqvist Marko |
last post by:
Hi,
I have one Access database table including questions and answers. Now
I need to give answer id automatically to questionID column. But I
don't know how it is best (fastest) to do?
table before
rowID answID qryrow questionID datafield
1591 12 06e 06e 06e question
1593 12 06f 06f 06f question
1594 12 answer to the question 06f
|
by: glenn |
last post by:
I am use to programming in php and the way session and post vars are past
from fields on one page through to the post page automatically where I can
get to their values easily to write to a database or continue to process on
to the next page.
I am now trying to learn ASP to see if we can replace some of our
applications that were written in php with an ASP alternative. However,
after doing many searches on google and reading a couple...
| |
by: Rider |
last post by:
Hi, simple(?) question about asp.net configuration..
I've installed ASP.NET 2.0 QuickStart Sample successfully. But, When I'm
first start application the follow message shown.
=========
Server Error in '/QuickStartv20' Application.
--------------------------------------------------------------------------------
Configuration Error
Description: An error occurred during the processing of a configuration file
|
by: Jeff |
last post by:
In the function below, can size ever be 0 (zero)?
char *clc_strdup(const char * CLC_RESTRICT s)
{
size_t size;
char *p;
clc_assert_not_null(clc_strdup, s);
size = strlen(s) + 1;
|
by: spibou |
last post by:
In the statement "a *= expression" is expression assumed to be
parenthesized ? For example if I write "a *= b+c" is this the same
as "a = a * (b+c)" or "a = a * b+c" ?
|
by: Allan Ebdrup |
last post by:
Hi,
I'm trying to render a Matrix question in my ASP.Net 2.0 page, A matrix
question is a question where you have several options that can all be rated
according to several possible ratings (from less to more for example).
I have a question object that has two properties that contain the
collections Options and Ratings.
now I want this kind of layout:
---
Rating1 Rating2 Rating3
Option 1 () () ...
|
by: Zhang Weiwu |
last post by:
Hello!
I wrote this:
..required-question p:after {
content: "*";
}
Corresponding HTML:
<div class="required-question"><p>Question Text</p><input /></div>
<div class="not-required-question"><p>Question Text</p><input /></div>
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: 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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |