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"