question

Article View: bit.databases.mssql-l

Article #218

Back to bit.databases.mssql-l Previous Next

#218

From: ricksql
Date: Sun, 31 Aug 2003 06:03

38 lines
2097 bytes

#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.age
****

(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

Technical Details

Message-ID: <l.1062309783.1757537841@host-66-81-126-37.rev.o1.com>

Path:

arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!newsfeed.stanford.edu!logbridge.uoregon.edu!newsfeed.vmunix.org!feed.news.nacamar.de!news1.ip-mobilphone.net!news2.ip-mobilphone.net!u50415039@spawnkill.ip-mobilphone.net

Show Full Headers

Complete Article Headers

X-Google-Language: ENGLISH,ASCII-7-bit
X-Google-Thread: 109365,dccbf7583e9eb21c,start
X-Google-Attributes: gid109365,public
X-Google-Thread: f6611,dccbf7583e9eb21c,start
X-Google-Attributes: gidf6611,public
X-Google-Thread: 104de1,dccbf7583e9eb21c,start
X-Google-Attributes: gid104de1,public
X-Google-ArrivalTime: 2003-08-30 23:10:04 PST
From: u50415039@spawnkill.ip-mobilphone.net  (ricksql@yahoo.com)
Sender: u50415039@spawnkill.ip-mobilphone.net
Newsgroups: bit.databases.mssql-l,comp.databases.ms-sqlserver,microsoft.public.sqlserver.mseq
Subject: question
Date: Sun, 31 Aug 2003 06:03:03 GMT
Lines: 39
Message-ID: <l.1062309783.1757537841@host-66-81-126-37.rev.o1.com>
NNTP-Posting-Host: 66.81.126.37
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-Mailer: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624 - ur
Path: arc1-ca.pugleaf.net!archiver1.google.com!news1.google.com!newsfeed.stanford.edu!logbridge.uoregon.edu!newsfeed.vmunix.org!feed.news.nacamar.de!news1.ip-mobilphone.net!news2.ip-mobilphone.net!u50415039@spawnkill.ip-mobilphone.net