a simple Query help

Hi everybody! i need anybody's help in a query im trying to build
here's the table
intID nameID name address
projectid projectname
----
--
1 1 jacklyn ohio
5478 sheetrock
2 1 jacklyn ohio
5479 beers
3 1 jacklyn ohio
5489 cellphones
4 2 jose wisconsin
4567 cereals
5 2 jose wisconsin
4569 oatmeals
6 3 pirena vermont
3457 rockstar
7 3 pirena vermont
3458 iPod
8 3 pirena vermont
3459 cowlabel
9 3 pirena vermont
3477 computer
10 3 pirena vermont
3437 insurance
how do u create a query wherein it'll just get 2 of each of the names from
the table
above?
expected result:
intID nameID name address
projectid projectname
----
--
1 1 jacklyn ohio
5478 sheetrock
2 1 jacklyn ohio
5479 beers
4 2 jose wisconsin
4567 cereals
5 2 jose wisconsin
4569 oatmeals
6 3 pirena vermont
3457 rockstar
7 3 pirena vermont
3458 iPod
Thanks!!2-0 huh? ;-)
Which two? i.e., based on what criteria?
HTH
Jerry
"ChiWhiteSox" <ChiWhiteSox@.discussions.microsoft.com> wrote in message
news:291BF37A-8F21-409D-BE61-9D05A2F1EFF7@.microsoft.com...
> Hi everybody! i need anybody's help in a query im trying to build
> here's the table
> intID nameID name address
> projectid projectname
> ----
--
> 1 1 jacklyn ohio
> 5478 sheetrock
> 2 1 jacklyn ohio
> 5479 beers
> 3 1 jacklyn ohio
> 5489 cellphones
> 4 2 jose wisconsin
> 4567 cereals
> 5 2 jose wisconsin
> 4569 oatmeals
> 6 3 pirena vermont
> 3457 rockstar
> 7 3 pirena vermont
> 3458 iPod
> 8 3 pirena vermont
> 3459 cowlabel
> 9 3 pirena vermont
> 3477 computer
> 10 3 pirena vermont
> 3437 insurance
> how do u create a query wherein it'll just get 2 of each of the names from
> the table
> above?
> expected result:
> intID nameID name address
> projectid projectname
> ----
--
> 1 1 jacklyn ohio
> 5478 sheetrock
> 2 1 jacklyn ohio
> 5479 beers
> 4 2 jose wisconsin
> 4567 cereals
> 5 2 jose wisconsin
> 4569 oatmeals
> 6 3 pirena vermont
> 3457 rockstar
> 7 3 pirena vermont
> 3458 iPod
> Thanks!!
>
>|||hi, thanks for replying. basically no criteria needed necessary. what i mea
n
is
the query would generate 2 jackyln,2 jose, and 2 pirena. coz right now im
getting 3 jacklyns, 2 joses, and 4 pirenas.
please tell me if this clarifies your question
"Jerry Spivey" wrote:

> 2-0 huh? ;-)
> Which two? i.e., based on what criteria?
> HTH
> Jerry
> "ChiWhiteSox" <ChiWhiteSox@.discussions.microsoft.com> wrote in message
> news:291BF37A-8F21-409D-BE61-9D05A2F1EFF7@.microsoft.com...
>
>|||"a simple Query"
:-) What makes you think this is simple?
See below:
ChiWhiteSox wrote:
> Hi everybody! i need anybody's help in a query im trying to build
> here's the table
> intID nameID name address
> projectid projectname
> ----
--
> 1 1 jacklyn ohio
> 5478 sheetrock
> 2 1 jacklyn ohio
> 5479 beers
> 3 1 jacklyn ohio
> 5489 cellphones
> 4 2 jose wisconsin
> 4567 cereals
> 5 2 jose wisconsin
> 4569 oatmeals
> 6 3 pirena vermont
> 3457 rockstar
> 7 3 pirena vermont
> 3458 iPod
> 8 3 pirena vermont
> 3459 cowlabel
> 9 3 pirena vermont
> 3477 computer
> 10 3 pirena vermont
> 3437 insurance
> how do u create a query wherein it'll just get 2 of each of the names
> from the table
> above?
> expected result:
> intID nameID name address
> projectid projectname
> ----
--
> 1 1 jacklyn ohio
> 5478 sheetrock
> 2 1 jacklyn ohio
> 5479 beers
> 4 2 jose wisconsin
> 4567 cereals
> 5 2 jose wisconsin
> 4569 oatmeals
> 6 3 pirena vermont
> 3457 rockstar
> 7 3 pirena vermont
> 3458 iPod
>
Well, for this particular set of data, a union query will work:
select top 2 <column list> from tbl where nameid = 1
union
select top 2 <column list> from tbl where nameid = 2
union
select top 2 <column list> from tbl where nameid = 3
But I doubt this solution will be very workable for you in your actual
situation ... :-)
Try this
SELECT
(select count(*) from tbl
where intid <= s.intID and nameID = s.nameID),
intID,
nameID,
[name],
address,
projected,
projectname
FROM Test.dbo.tbl s
WHERE (select count(*) from tbl
where intid <= s.intID and nameID = s.nameID) < 3
HTH,
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The general logic is to generate a ranking expression based on your
requirements and use that expression in your WHERE clause. Here is one
option:
SELECT *
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2 WHERE t2.name = t1.name
AND t2.projectid <= t1.projectid ) <= 2 ;
You can use your intId column instead of projectid in the subquery to get
the exact results you have in your post. Of course there are other ways
using a self join, TOP clause etc. for which you can find some examples, if
you search the archives of this newsgroup.
Anith|||thank you so much!! this was a HUGE help in my project..
"Anith Sen" wrote:

> The general logic is to generate a ranking expression based on your
> requirements and use that expression in your WHERE clause. Here is one
> option:
> SELECT *
> FROM tbl t1
> WHERE ( SELECT COUNT( * )
> FROM tbl t2 WHERE t2.name = t1.name
> AND t2.projectid <= t1.projectid ) <= 2 ;
> You can use your intId column instead of projectid in the subquery to get
> the exact results you have in your post. Of course there are other ways
> using a self join, TOP clause etc. for which you can find some examples, i
f
> you search the archives of this newsgroup.
> --
> Anith
>
>|||Hey!!
I gave the same solution! ;-)
Glad you got your problem solved. :-)
ChiWhiteSox wrote:
> thank you so much!! this was a HUGE help in my project..
> "Anith Sen" wrote:
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

A Simple Problem. But....

Hi everybody,

I have a requirement in which a user fetches some records from a table. I want that records to be locked for that user so that when other fetches records these should be records not fetched earlier. Currently I am using a stored proc in which I rerun the select statement to lock the records in temp table. Any better solution ?

Thanks in advance

You don't want users to even be able to READ rows that have been selected and locked? Not sure you can do that, as they can SELECT even while the locks are in place. Sounds like you are doing it in a way that makes sense already... that you control which rows are "checked out" and then not allow others to get them untill they are checked in... You could add a status field on your table and then use that to allow an application to select those rows or not... Bruce|||

Locks can be a good thing and they can also be devastating if mismanaged.

You can tweak the isolation level to help most situations.

How long do you plan to hold these locks?

Why do you need to manage them?

What is your objective?

Please provide a little more detail and depth on your situation and needs.

|||How about updating the table, using the OUTPUT clause to return what you've changed, like:

UPDATE thetable
SET islocked = 1
OUTPUT DELETED.*
WHERE islocked = 0
AND ... --other criteria goes here

This way, you'll get the locked rows, and the next user won't be able to. If you want to mark who locked it, then perhaps use a different type for islocked.

Hope this helps.

Rob|||

Hi Dale,

I am making a call center application in two or three agents make outbound calls to customers. If there are 100 calls to be made then each of my agent sees these 100 entries and makes multiple calls to the same customer. I want that when an agent logs in then it should some fixed number of entries(say 25) and when another agent logs in then it should see entries not fetched earlier.

In this way each agent will have different number for outbound call. I am not locking the records per se. In my main table I have a primary key. When I fetch the records I make an entry in the temp table for that records. For other agent I fetch the records that are not in temp table.

|||I would add a column to the table for the [Agent ID], have a Agent first 'mark' n number of rows with his/her [Agent ID], and then retreive the rows that were marked.|||

Ah.

It's an allocation challenge.

Like Arnie says, add the agent id to the call record.

Or

Create an intermediate cross-reference/assignment table that matches call records to agent's.

you can then easily manipulate this table with inserts, updates, deletes.

And when you create a new set of assignments you just get those calls that aren't present in the assignment table.

|||

Hi Arnie,

Thanks for sparing time. This is exactly what I am doing currently but I don't give agent to mark the rows. Some fixed number of rows are marked(in a temp table) for that agent. And these rows are released when the agent logs out.

Thanks

|||Did you try using the OUTPUT clause of an UPDATE statement? Regardless of whether you call use "allocatedto = system_user" or "islocked = 1", the effect will be similar.

(Following the order of posts here is awkward - your later post appeared higher than mine in the thread tree)

Rob

A Simple Problem. But....

Hi everybody,

I have a requirement in which a user fetches some records from a table. I want that records to be locked for that user so that when other fetches records these should be records not fetched earlier. Currently I am using a stored proc in which I rerun the select statement to lock the records in temp table. Any better solution ?

Thanks in advance

You don't want users to even be able to READ rows that have been selected and locked? Not sure you can do that, as they can SELECT even while the locks are in place. Sounds like you are doing it in a way that makes sense already... that you control which rows are "checked out" and then not allow others to get them untill they are checked in... You could add a status field on your table and then use that to allow an application to select those rows or not... Bruce|||

Locks can be a good thing and they can also be devastating if mismanaged.

You can tweak the isolation level to help most situations.

How long do you plan to hold these locks?

Why do you need to manage them?

What is your objective?

Please provide a little more detail and depth on your situation and needs.

|||How about updating the table, using the OUTPUT clause to return what you've changed, like:

UPDATE thetable
SET islocked = 1
OUTPUT DELETED.*
WHERE islocked = 0
AND ... --other criteria goes here

This way, you'll get the locked rows, and the next user won't be able to. If you want to mark who locked it, then perhaps use a different type for islocked.

Hope this helps.

Rob|||

Hi Dale,

I am making a call center application in two or three agents make outbound calls to customers. If there are 100 calls to be made then each of my agent sees these 100 entries and makes multiple calls to the same customer. I want that when an agent logs in then it should some fixed number of entries(say 25) and when another agent logs in then it should see entries not fetched earlier.

In this way each agent will have different number for outbound call. I am not locking the records per se. In my main table I have a primary key. When I fetch the records I make an entry in the temp table for that records. For other agent I fetch the records that are not in temp table.

|||I would add a column to the table for the [Agent ID], have a Agent first 'mark' n number of rows with his/her [Agent ID], and then retreive the rows that were marked.|||

Ah.

It's an allocation challenge.

Like Arnie says, add the agent id to the call record.

Or

Create an intermediate cross-reference/assignment table that matches call records to agent's.

you can then easily manipulate this table with inserts, updates, deletes.

And when you create a new set of assignments you just get those calls that aren't present in the assignment table.

|||

Hi Arnie,

Thanks for sparing time. This is exactly what I am doing currently but I don't give agent to mark the rows. Some fixed number of rows are marked(in a temp table) for that agent. And these rows are released when the agent logs out.

Thanks

|||Did you try using the OUTPUT clause of an UPDATE statement? Regardless of whether you call use "allocatedto = system_user" or "islocked = 1", the effect will be similar.

(Following the order of posts here is awkward - your later post appeared higher than mine in the thread tree)

Rob

A problem with SQL Server Authentication?

Hi everybody...
I need to implement a publication but I got the next message when I start
the assistant: "SQL Server Agent on 'USER07' currently uses the system
account, which causes replication between servers to fail. In the following
dialog box, specify another account for the Service startup account". I
don't know which account I have to specify because the error persist. Do I
have to create a new account?
Thanks very much for any help!
Juan
juan,
if you're using replication across different boxes, you'll need a domain
user as the startup account for the SQL Server agent.
There's a few sections on this under Replication, Security in BOL.
HTH,
Paul Ibison
|||If I might just quibble with Paul's excellent answer.
For a push subscription the SQL Agent Account on the distribution must be
able to access the snapshot share on the publisher. This share is by default
\\PublisherServerName\C$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\UNC
If your Publisher and Distributor are on the same server this is not a
problem.
Its only a problem when you are doing pull subscriptions, because then its
your SQL Server agent account on the Subscriber which must be able to map a
drive to
\\PublisherServerName\C$\Program Files\Microsoft SQL
Server\MSSQL\ReplData\UNC
So for this to work
1) your Subscriber must be part of the admin group on the Publisher, and you
select the impersonate the SQL Server Agent account on the Publisher
or
2) you change the share name to a share which the SQL Server agent account
on the subscriber can access and give permission to this account to access
the share and the underlying files and folders.
If you are in an untrusted domain you can use pass through authentication
http://support.microsoft.com/default...&Product=sql2k
and if you are an internet user you will have to use either the everyone
group/account (not recommended) or use FTP (recommended).
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Om$SH07IEHA.228@.TK2MSFTNGP10.phx.gbl...
> juan,
> if you're using replication across different boxes, you'll need a domain
> user as the startup account for the SQL Server agent.
> There's a few sections on this under Replication, Security in BOL.
> HTH,
> Paul Ibison
>