SQL query help -- sql server programming

Martin
9/29/2003 12:44:12 AM

Thanks for the fast answer, but i was a bit unclear, i
need to fetch the id, and other columns from the table
aswell.

CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int,
colC int,
colD int,
colE int,
colF int,
colG int
)

IF A and B are the same, only retrive the one with
highest C

Thanks

Martin
**********************************

[quoted text, click to view]

oj
9/29/2003 12:51:27 AM

select *
from #demo t1
where t1.c=(select max(c) from #demo t2 where t2.a=t1.a and t2.b=t1.b)

--
-oj
Rac v2.2 & QALite!
http://www.rac4sql.net

[quoted text, click to view]

Martin
9/29/2003 1:56:47 AM

Thanks, that's what I was looking for.

Why aren't there any partial distinct func in SQL, in the
case we get all if col A, B, and C are the same. DB could
just use random to choose witch partial duplicate to
return.

[quoted text, click to view]

Deepankar
9/29/2003 3:13:37 AM

Hi,
Does this work for you?
select id,a.street,revenue
from Company as a,
(
select street
from company
group by street

) as b
where a.street=b.street
and revenue=(select max(revenue) from company where
street=a.street)

Thanx
Deep
[quoted text, click to view]

Uri Dimant
9/29/2003 10:01:30 AM

Martin
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int,
colC int
)

INSERT INTO #Demo(colA,colB,colC) VALUES (1,6,1)
INSERT INTO #Demo(colA,colB,colC) VALUES (1,6,2)
INSERT INTO #Demo(colA,colB,colC) VALUES (2,4,1)
INSERT INTO #Demo(colA,colB,colC) VALUES (3,3,1)

SELECT colA,colB,max(colC)AS D FROM #Demo
GROUP BY colA,colB

[quoted text, click to view]

oj
9/30/2003 12:05:09 AM

<g> there is no such thing. However, you could try sqlwish@microsoft.com. If
you have a strong business case, they might implement such.

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : SQL query help