2-table join where only need one row of second table.

For discussion sake, I've got two tables: customers and orders. What I want
is a query that will return the most recent order for each customer. Let's
say the tables are like:

Customer: customerId, customerName
Orders: orderId, customerId, orderDate

The simple joins I've done give me back all of the orders for all of the
customers.

Any advice?Try

select * from customer c, orders o
where c.customerId = o.customerId
and c.orderId =
(
select max (o2.orderDate)
from orders o2
where o2.customerId = c.customerId
)

--
Regards Bagieta
~~~~~~~~~~~~~~~~~~~~~~~~~~~
dbDeveloper - Multiple databases editor
http://www.prominentus.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~

2K-to-2K Attach Error: Could not find row in sysindexes for database ID...

OK, I have read through many similar posts about this issue and most of them are due to that, they wanted to attach 2K5 MDF to 2K. But unfortunately, I was trying to attach my old SQL2K MDF file (with LDF) to my SQL2K enterprise manager, as well as query analyzer, and I still hit this error:

Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Connection Broken

I have also tried starting sql server in single user mode and run the following:

dbcc checktable ('sysindexes', repair_rebuild)

and get this in QA:

DBCC results for 'sysindexes'.
There are 100 rows in 4 pages for object 'sysindexes'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In command window, I get this:

2006-06-05 03:12:32.15 spid51 DBCC CHECKTABLE (master.dbo.sysindexes, repair_
rebuild) executed by sa found 0 errors and repaired 0 errors. Elapsed time: 0 h
ours 0 minutes 0 seconds.

After that I tried to attach the same file again, both with and without LOG/LDF file, still getting the same error as mentioned earlier. I'm getting nervous now because this MDF is too critical to me. Can somebody help? I don't mind sending my MDF file to anyone who can help me to recover it! Thanks!

Antonio

I think problem here is: You attached it in SQL2005 already so this file is changed. Try attached it again in 2005 version and do an import wizard from 2000 version.

Hope this help.

2005: forbidding INSERTs and DELETEs

Please help.
I have a table with single row. I need to allow only UPDATEs of the
table, forbid INSERTs and DELETEs. How to achieve it?
Thank you for information
/RAM/No permissions exist when a new object is created. Only privileged users
such as the object owner, sysadmin role members, the database owner and
db_owner role members have access until permissions are granted. To grant
only UPDATE permissions:

GRANT UPDATE ON dbo.MyTable TO SomeRole

--
Hope this helps.

Dan Guzman
SQL Server MVP

"R.A.M." <r_ahimsa_m@.poczta.onet.plwrote in message
news:4ltpa2hsjphrsifi2voj6kdr0vfcuh2f0c@.4ax.com...

Quote:

Originally Posted by

Please help.
I have a table with single row. I need to allow only UPDATEs of the
table, forbid INSERTs and DELETEs. How to achieve it?
Thank you for information
/RAM/

|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

I have a table with single row. I need to allow only UPDATEs of the
table, forbid INSERTs and DELETEs. How to achieve it?

CREATE TRIGGER update_only FOR INSERT, DELETE AS
RAISERROR('INSERT and DELETE not permitted on this table!', 16, -1)
ROLLBACK TRANSACTION

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Thu, 06 Jul 2006 13:42:39 +0200, R.A.M. wrote:

Quote:

Originally Posted by

>Please help.
>I have a table with single row. I need to allow only UPDATEs of the
>table, forbid INSERTs and DELETEs. How to achieve it?
>Thank you for information
>/RAM/

Hi RAM,

Dan already replied how to do this with GRANT and DENY. If you also must
keep the database owner and administrators from accidentally inserting
or deleting a row, add the following trigger:

CREATE TRIGGER NoInsertOrDelete
ON SingleRowTable
AFTER INSERT, DELETE
AS
IF @.@.ROWCOUNT = 0 RETURN
ROLLBACK TRANSACTION
RAISERROR ('Don''t add rows to or remove rows from this table!', 16, 1)
GO

--
Hugo Kornelis, SQL Server MVP

2005 fti query performance issue

I have a 10 million row table with two columns(name, address) full text
indexed. I want to search the table for
the first 200 occurances of "Bob jones" in the name field. I have ran
another query to find out that there are actully 15,291 rows with "Bob Jones"
in the name field. I want just the first 200.
When I run the FT search, it takes 2 minutes for the query to finish. When I
run a simple query
(select * from tst where name = 'Bob jones'),
the query comes back in under two seconds. It appears that the query is
searching the whole catalog before going to the data table. Is this expected?
How can I get the query to search the catalog and stop after the first 200
occurances are found?
Here is the query I am using:
Select name, address from tst
where contains(Name, '"Bob Jones"')
Table schema is as follows:
create table tst(
uid int identity(1,1),
Name varchar(255),
address varchar(255)
)
use containstable. It allows you to limit your results set to the first 200
or so rows.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:51042F5E-9BCF-43AE-A232-73670F90EEEB@.microsoft.com...
>I have a 10 million row table with two columns(name, address) full text
> indexed. I want to search the table for
> the first 200 occurances of "Bob jones" in the name field. I have ran
> another query to find out that there are actully 15,291 rows with "Bob
> Jones"
> in the name field. I want just the first 200.
> When I run the FT search, it takes 2 minutes for the query to finish. When
> I
> run a simple query
> (select * from tst where name = 'Bob jones'),
> the query comes back in under two seconds. It appears that the query is
> searching the whole catalog before going to the data table. Is this
> expected?
> How can I get the query to search the catalog and stop after the first 200
> occurances are found?
> Here is the query I am using:
> Select name, address from tst
> where contains(Name, '"Bob Jones"')
> Table schema is as follows:
> create table tst(
> uid int identity(1,1),
> Name varchar(255),
> address varchar(255)
> )
|||Carl,
You should be able to use TOP x in your select list, where x is 200 in your
SQL 2005 FTS query, for example:
Select TOP 200 name, address from tst
where contains(Name, '"Bob Jones"')
You may think that using CONTAINSTABLE (or FREETEXTTABLE) with Top_N_Rank
would also meet your requirement of getting the "first 200 occurrences of
"Bob jones" in the name field" (emphasis on first), but this would be
incorrect. The Top_N_Rank parameter limits the top number of rows by RANK
and not the *first* rows that contain your search phrase.
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:51042F5E-9BCF-43AE-A232-73670F90EEEB@.microsoft.com...
> I have a 10 million row table with two columns(name, address) full text
> indexed. I want to search the table for
> the first 200 occurances of "Bob jones" in the name field. I have ran
> another query to find out that there are actully 15,291 rows with "Bob
Jones"
> in the name field. I want just the first 200.
> When I run the FT search, it takes 2 minutes for the query to finish. When
I
> run a simple query
> (select * from tst where name = 'Bob jones'),
> the query comes back in under two seconds. It appears that the query is
> searching the whole catalog before going to the data table. Is this
expected?
> How can I get the query to search the catalog and stop after the first 200
> occurances are found?
> Here is the query I am using:
> Select name, address from tst
> where contains(Name, '"Bob Jones"')
> Table schema is as follows:
> create table tst(
> uid int identity(1,1),
> Name varchar(255),
> address varchar(255)
> )