Query Help

Dear All,
I have a single table name "Remark". It contains the item code, date,
status and remark. If I want to build a query that select items out which
the latest status is still in "pending" for example. How do I make it?
Thanks
Best Rdgs
EllisEllis
SELECT <columns list>
FROM Remarks WHERE[date]=(SELECT TOP 1 [date]
FROM Remarks R WHERE
R.Itemcode=Remarks.Itemcode
ORDER BY [date] DESC)
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:uSSJ$UXbFHA.464@.TK2MSFTNGP15.phx.gbl...
> Dear All,
> I have a single table name "Remark". It contains the item code,
date,
> status and remark. If I want to build a query that select items out which
> the latest status is still in "pending" for example. How do I make it?
> Thanks
> Best Rdgs
> Ellis
>sql

query for this purpose

We have a worktran table
WoNo Jobno Status
1 01 Fullfill
1 02 Pending
2 01 Fullfill
2 02 Fullfill
We need to display those WoNo having all its rows status=fulfill
In the example above, it should display only WoNo 2 because it contain
all Fullfill status
We tried - select distinct WoNo from worktran where status='Fullfill'
But it does not make sure that all of the rows for certian WoNo have
status='Fulfill'
Thanks in advance.Hello, MadhavC
You can use one of these queries:
SELECT WoNo FROM worktran
GROUP BY WoNo
HAVING COUNT(*)=SUM(CASE WHEN Status='Fulfill' THEN 1 ELSE 0 END)
SELECT DISTINCT WoNo FROM worktran a
WHERE NOT EXISTS (
SELECT * FROM worktran b
WHERE a.WoNo=b.WoNo
AND b.WoNo<>'Fulfill'
)
You should also take a look at the following article, by Joe Celko,
regarding Relational Division:
http://www.dbazine.com/ofinterest/oi-articles/celko1
Razvan|||Thanks for your reply it worked.

Query for Replication Status

Hi,
We have 50 databases in Merge Replication.
We want to know the replication status (replicating or failed) using query,
Any query is there to find out the status?
Please advise me.
Rgds,
Soura
Soura,
you could have a look at sp_MSenum_merge_subscriptions. It takes the last
value for a merge agent in MSmerge_history and looks at the runstatus value.
Alternatively, you could use my proc which works against job states directly
(http://www.replicationanswers.com/Do...unningJobs.txt). These
could be mapped to : -- 1 = Executing, 2 = Waiting For Thread, 3 = Between
Retries, 4 = Idle, 5 = Suspended, 6 = [obsolete], 7 =
PerformingCompletionActions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

query cannot return any results

I have a very uncommon problem ... I have a application that runs like this:
1) it receives users inserts, including a Status = 'U' field;
2) based on a Status field (index) the application query and select the last
inserted registries all day long, each 30 seconds;
3) every time it read the registries it changes the Status field to 'R'.
The problem is that after about 24 hours, the query identifies no longer
registries with that index. It returns nothing. I use a execurereader
command, and it happens that myreader.hasrows = false, even if there are row
s
with Status field = 'U'
Can somebody help me to know what is happening?
--
Sergio R Piresquery optimiser should use best available index or column stats to decide
strategy, and this may be cached for long period.
Maybe stats get recomputed automatically if you make lotsa changes and have
updatestats dboption set, or explicitly by your DBA [recommendation used to
be to do explicitly due to excessive overhead but nowadays with autonomics
MSSQL does the right thing].
If you truncate/delete staging table [daily] just before query is compiled
into cache it may decide to use tablescan even if index available [since so
_few_ rows] and this may persist some time even if cardinality builds up a
lot.
Unfortunately the sysindexes.rowcnt cannot be relied on for accuracy [due to
transaction activity], so you may have to force count(*) to get real count
but this has locking issues [nolock would only give approx count like
sysindexes].
Dependencies can be omitted from sysdepends [to support forward compilation]
so optimiser may be similarly ignorant.
I suspect the optimiser is getting , so suggest that
1. check latest Service Pack applied
2. exec sp_dboption 'pubs','auto create statistics','on' -- substitute
dbname for pubs
3. exec sp_dboption 'pubs','auto update statistics','on' -- substitute
dbname for pubs
4. use QA to show query plan [Control-L]
5. try explicit sp_recompile
6. check dependencies
if all else fails you can mark your sproc "WITH RECOMPILE" to ignore cached
copy, thus keep abreast of actual cardinality
best wishes!
Dick
"Sergio R Pires" wrote:

> I have a very uncommon problem ... I have a application that runs like thi
s:
> 1) it receives users inserts, including a Status = 'U' field;
> 2) based on a Status field (index) the application query and select the la
st
> inserted registries all day long, each 30 seconds;
> 3) every time it read the registries it changes the Status field to 'R'.
> The problem is that after about 24 hours, the query identifies no longer
> registries with that index. It returns nothing. I use a execurereader
> command, and it happens that myreader.hasrows = false, even if there are r
ows
> with Status field = 'U'
> Can somebody help me to know what is happening?
> --
> Sergio R Pires