a complex query

I have the following SQL 2000 database table:
NEWS (IDNews, Country, PublishDate, Title)

I have to get a dataset containing only one record for each country, having most recent publish date.
Any suggestions? Thanks.SELECT TOP 1 IDNews, Country, PublishDate, Title From News Group By Country Order By PublishDate DESC?|||Try this:

select IDNews, Country, PublishDate, Title
from NEWS
where IDNews in
(
select
max(IDnews)
from news n
join
(select
country, max(publishdate) as publishdate
from news
group by country) md
on n.country = md.country
and n.publishdate = md.publishdate
group by n.country
)|||

select
 news.*
from
 news
 inner join
 (select
 country, max(publishdate) as publishdate
 from
 news
 group by
 country
 ) as TMP
 on news.country = TMP.country
 and news.publishdate = TMP.publishdate

sql

A Chart for Each Client

I'm trying to create a chart for each client.
In a DataSet, they are returned in a sorted order like this:
ClientName Month Sales
-- -- --
Client 1 1 500
Client 1 2 100
...
Client 1 10 900
Client 2 1 100
Client 2 2 200
...
Client 2 10 800
I'd like to have two charts, Client 1 and Client 2 showing their own
trend.
However, so far I get only ONE chart, either a sum, or the first client
group if it's not sum.
I don't think it will take one query per client.
Thanks.I think I got it by browsing some QA here, simply by putting it in a
group's header or footer in a table.|||Yes, that is correct. If you place a chart in a table detail section, every
chart will just get one detail data row (i.e. essentially just one
datapoint) and this is not what you want. Placing the chart however in the
group's header or footer will generate a chart from all the detail rows that
are contained in the current group.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hifchan@.yahoo.com> wrote in message
news:1108483860.264648.286130@.c13g2000cwb.googlegroups.com...
>I think I got it by browsing some QA here, simply by putting it in a
> group's header or footer in a table.
>sql

5 clients takes 5 times more

Hi there!

This is the scenario: I've got a dataset to handle data in aplication tier. I've sent transactional operations to data layer. In data layer I've got stored procedures that handle xml data to "keep" and "retrieve" data in/from database. This is the idea.

After some tests I get this code in DB:

DECLARE @.Documento as xml

DECLARE @.QtdReg as int

DECLARE @.Contador as int

SET @.Contador = 1

Set @.Documento = '

<ROOT>

<Mesas>

<CMesa>1</CMesa>

<CodMesa>1</CodMesa>

<Designacao>TESTE</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 1 </Designa>

</Mesas>

<Mesas>

<CMesa>10</CMesa>

<CodMesa>10</CodMesa>

<Designacao>TESTE 10</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 10 </Designa>

</Mesas>

</ROOT>'

SET @.QtdReg = @.Documento.value('count(//ROOT/Mesas)', 'int' )

print @.qtdreg

WHILE @.Contador<=@.QtdReg

BEGIN

SELECT

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/CodMesa)[1]','integer') Chave,

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/Designacao)[1]','varchar(255)') Designacao,

@.Documento.query('//ROOT/Mesas[sql:variable("@.Contador")]') RegistoXML

SET @.Contador = @.Contador + 1

END

I'm just doing selects (but, if I put an INSERT command before the SELECT I can insert data in DB).

The problem is: If I increment the xml data (I've tested with 500 rows - not inserted it here for space reasons - you can copy/paste one row and create 500 rows) it takes about 1 minute to handle all registers.

Worst, if I put this code in SQL SERVER MANAGEMENT STUDIO in 5 different queries (simulating 5 different clients) the time to execute the select takes 5 times more.

Am I doing this in the wrong way? Can you help me doing the right way?

Thank you very much for your time,

Rui Dias

Rui Dias VD wrote:

Hi there!

This is the scenario: I've got a dataset to handle data in aplication tier. I've sent transactional operations to data layer. In data layer I've got stored procedures that handle xml data to "keep" and "retrieve" data in/from database. This is the idea.

After some tests I get this code in DB:

DECLARE @.Documento as xml

DECLARE @.QtdReg as int

DECLARE @.Contador as int

SET @.Contador = 1

Set @.Documento = '

<ROOT>

<Mesas>

<CMesa>1</CMesa>

<CodMesa>1</CodMesa>

<Designacao>TESTE</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 1 </Designa>

</Mesas>

<Mesas>

<CMesa>10</CMesa>

<CodMesa>10</CodMesa>

<Designacao>TESTE 10</Designacao>

<NPessoas>5</NPessoas>

<Ocupacao>0</Ocupacao>

<Designa>1 - Mesa 10 </Designa>

</Mesas>

</ROOT>'

SET @.QtdReg = @.Documento.value('count(//ROOT/Mesas)', 'int' )

print @.qtdreg

WHILE @.Contador<=@.QtdReg

BEGIN

SELECT

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/CodMesa)[1]','integer') Chave,

@.Documento.value('(//ROOT/Mesas[sql:variable("@.Contador")]/Designacao)[1]','varchar(255)') Designacao,

@.Documento.query('//ROOT/Mesas[sql:variable("@.Contador")]') RegistoXML

SET @.Contador = @.Contador + 1

END

I'm just doing selects (but, if I put an INSERT command before the SELECT I can insert data in DB).

The problem is: If I increment the xml data (I've tested with 500 rows - not inserted it here for space reasons - you can copy/paste one row and create 500 rows) it takes about 1 minute to handle all registers.

Worst, if I put this code in SQL SERVER MANAGEMENT STUDIO in 5 different queries (simulating 5 different clients) the time to execute the select takes 5 times more.

Am I doing this in the wrong way? Can you help me doing the right way?

Thank you very much for your time,

Rui Dias

Sorry, forgot to tell:

I'm using SQL Server 2005 and Visual Studio 2005

|||

You should never write this type of loop. If you want to map one Mesas element to one row, please use the nodes() method as in:

SELECT

d.value('(CodMesa)[1]','integer') Chave,

d.value('(Designacao)[1]','varchar(255)') Designacao,

d.query('.') RegistoXML

FROM @.Documento.nodes('/ROOT/Mesas') as N(d)

(also please try to avoid // if you know the path).

Best regards

Michael