the maximum of the results of an other query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

the maximum of the results of an other query

Author

Topic

pino
Starting Member

5 Posts

Posted - 2004-05-14 : 04:02:30

I'm using Delphi 5.0 and I have 2 queries

query1:
SELECT GebrNaam, TestCode, Datum, SUM(resultaat) AS TotRes
FROM test
WHERE TestCode= 'L'
GROUP BY TestCode, GebrNaam, Datum

here are the total results of test L shown, but every result is shown, for example for user jvanrijt the result of the 5th of may and the result of the 7th of may is shown

but i want the maximum of those results for each user so i thought i use this fot query2

SELECT GebrNaam,TestCode,MAX(TotRes)
FROM Query1
GROUP BY GebrNaam,TestCode

bu then i get this error message when I put the query on active:
Table does not exist

what do I do wrong?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-14 : 04:25:12

Don't know about Delphi, but SQL Server would expect you to put at least an

into

in your first query as a set of results out of a query do not a table make...

pino
Starting Member

5 Posts

Posted - 2004-05-14 : 04:26:42

how do i use this INTO then?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-14 : 04:29:19

[code]select * into #table from table[/code]

The second query must be completed in the same batch and it's best to drop the #table at the end of the batch...

There are many other ways to do this, I am just showing you the minimum you need to do...

pino
Starting Member

5 Posts

Posted - 2004-05-14 : 04:30:43

buit i have to create these tables don't I?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-05-14 : 04:39:08

no, its a temporary table... Look in SQL Server BOL (Books online) for more info...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-14 : 06:38:16

SELECT GebrNaam, TestCode, Datum, SUM(resultaat) AS TotRes
FROM test t1
WHERE TestCode= 'L'
and Datum = (select top 1 Datum from (select t2.Datum, SUM(t2.resultaat) AS TotRes from test t2 where t1.GebrNaam = t2.GebrNaam and t1.TestCode = t2.TestCode group by t2.Datum order by TotRes desc) a)
GROUP BY TestCode, GebrNaam, Datum

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources