SQL query help  
Author Message
capahance





PostPosted: Sun Dec 30 16:17:40 CST 2007 Top

SQL Server >> SQL query help

Hello all,
I'm trying to write a query that limits the number of rows returned for each
unique person in the table. For this particular query, I would like to
limit the results to 2. So John Doe may have 10 rows in the table but I
only want to display 2 rows in the results. I also don't care which 2 rows
are returned for each person. I have provided the table below and the
results expected. Any help is always appreciated!

Example of rows in the table:

Name Col1 Col2
Joe Doe 1 A
Jane Doe 1 A
John Smith 1 A
Jill Smith 1 A
Joe Doe 2 B
Jane Doe 2 B
John Smith 2 B
Jill Smith 2 B
Joe Doe 3 B
Jane Doe 3 B
John Smith 3 B
Jill Smith 3 B

Results expected, once again, I don't care which two rows are return for
each Name.

Name Col1 Col2
Joe Doe 1 A
Joe Doe 2 B
Jane Doe 1 A
Jane Doe 2 B
John Smith 1 A
John Smith 2 B
Jill Smith 1 A
Jill Smith 2 B

Thanks again,

Ted

SQL Server26  
 
 
David





PostPosted: Sun Dec 30 16:17:40 CST 2007 Top

SQL Server >> SQL query help

> Hello all,
> I'm trying to write a query that limits the number of rows returned for
> each unique person in the table. For this particular query, I would like
> to limit the results to 2. So John Doe may have 10 rows in the table but
> I only want to display 2 rows in the results. I also don't care which 2
> rows are returned for each person. I have provided the table below and
> the results expected. Any help is always appreciated!
>
> Example of rows in the table:
>
> Name Col1 Col2
> Joe Doe 1 A
> Jane Doe 1 A
> John Smith 1 A
> Jill Smith 1 A
> Joe Doe 2 B
> Jane Doe 2 B
> John Smith 2 B
> Jill Smith 2 B
> Joe Doe 3 B
> Jane Doe 3 B
> John Smith 3 B
> Jill Smith 3 B
>
> Results expected, once again, I don't care which two rows are return for
> each Name.
>
> Name Col1 Col2
> Joe Doe 1 A
> Joe Doe 2 B
> Jane Doe 1 A
> Jane Doe 2 B
> John Smith 1 A
> John Smith 2 B
> Jill Smith 1 A
> Jill Smith 2 B
>
> Thanks again,
>
> Ted
>


What key(s) exist in your table? Please include DDL in future so that we
don't have to guess.

What version of SQL Server are you using? The following 2 possible answers
both assume 2005 or 2008. If we knew your table had a key and knew what the
key was then it might be possible to make a simpler or more efficient
solution to the same problem.

SELECT t2.name, t2.col1, t2.col2
FROM
(SELECT DISTINCT name
FROM tbl) t1
CROSS APPLY
(SELECT DISTINCT TOP (2) name, col1, col2
FROM tbl
WHERE name = t1.name
ORDER BY col1, col2) t2;

SELECT name, col1, col2
FROM
(SELECT name, col1, col2,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY col1, col2) rn
FROM tbl) t
WHERE rn <=2;

--
David Portas


 
 
Ted





PostPosted: Sun Dec 30 18:41:03 CST 2007 Top

SQL Server >> SQL query help David,
Thanks for the reply! The version of SQL is 2000. Currently there is no
key on the table. Is appears the Cross Apply isn't a feature in SQL 2000,
what other options do I have. Thanks again.

>> Hello all,
>> I'm trying to write a query that limits the number of rows returned for
>> each unique person in the table. For this particular query, I would like
>> to limit the results to 2. So John Doe may have 10 rows in the table but
>> I only want to display 2 rows in the results. I also don't care which 2
>> rows are returned for each person. I have provided the table below and
>> the results expected. Any help is always appreciated!
>>
>> Example of rows in the table:
>>
>> Name Col1 Col2
>> Joe Doe 1 A
>> Jane Doe 1 A
>> John Smith 1 A
>> Jill Smith 1 A
>> Joe Doe 2 B
>> Jane Doe 2 B
>> John Smith 2 B
>> Jill Smith 2 B
>> Joe Doe 3 B
>> Jane Doe 3 B
>> John Smith 3 B
>> Jill Smith 3 B
>>
>> Results expected, once again, I don't care which two rows are return for
>> each Name.
>>
>> Name Col1 Col2
>> Joe Doe 1 A
>> Joe Doe 2 B
>> Jane Doe 1 A
>> Jane Doe 2 B
>> John Smith 1 A
>> John Smith 2 B
>> Jill Smith 1 A
>> Jill Smith 2 B
>>
>> Thanks again,
>>
>> Ted
>>
>
>
> What key(s) exist in your table? Please include DDL in future so that we
> don't have to guess.
>
> What version of SQL Server are you using? The following 2 possible answers
> both assume 2005 or 2008. If we knew your table had a key and knew what
> the key was then it might be possible to make a simpler or more efficient
> solution to the same problem.
>
> SELECT t2.name, t2.col1, t2.col2
> FROM
> (SELECT DISTINCT name
> FROM tbl) t1
> CROSS APPLY
> (SELECT DISTINCT TOP (2) name, col1, col2
> FROM tbl
> WHERE name = t1.name
> ORDER BY col1, col2) t2;
>
> SELECT name, col1, col2
> FROM
> (SELECT name, col1, col2,
> ROW_NUMBER() OVER (PARTITION BY name ORDER BY col1, col2) rn
> FROM tbl) t
> WHERE rn <=2;
>
> --
> David Portas
>
>


 
 
ShaAnand





PostPosted: Sun Dec 30 23:22:00 CST 2007 Top

SQL Server >> SQL query help The following query should work for you in SQL 2000.

I assume that there wont be any duplicate rows with the exact same columns
values (as if there is a key on Name+Col1+Col2). I have used the '~' chr in
this query assuming that this wont be present in any of your column data.


select Name,Col1,Col2 from
(
select Name,Col1,Col2,
(select count(*) from names n2 where n1.name = n2.name and (n1.Col1 + '~'
+ n1.Col2) >= (n2.Col1 + '~'+ n2.Col2)) as SeqNo
from names n1
) x
where x.SeqNo <=2



- Sha Anand




> David,
> Thanks for the reply! The version of SQL is 2000. Currently there is no
> key on the table. Is appears the Cross Apply isn't a feature in SQL 2000,
> what other options do I have. Thanks again.
>
> >> Hello all,
> >> I'm trying to write a query that limits the number of rows returned for
> >> each unique person in the table. For this particular query, I would like
> >> to limit the results to 2. So John Doe may have 10 rows in the table but
> >> I only want to display 2 rows in the results. I also don't care which 2
> >> rows are returned for each person. I have provided the table below and
> >> the results expected. Any help is always appreciated!
> >>
> >> Example of rows in the table:
> >>
> >> Name Col1 Col2
> >> Joe Doe 1 A
> >> Jane Doe 1 A
> >> John Smith 1 A
> >> Jill Smith 1 A
> >> Joe Doe 2 B
> >> Jane Doe 2 B
> >> John Smith 2 B
> >> Jill Smith 2 B
> >> Joe Doe 3 B
> >> Jane Doe 3 B
> >> John Smith 3 B
> >> Jill Smith 3 B
> >>
> >> Results expected, once again, I don't care which two rows are return for
> >> each Name.
> >>
> >> Name Col1 Col2
> >> Joe Doe 1 A
> >> Joe Doe 2 B
> >> Jane Doe 1 A
> >> Jane Doe 2 B
> >> John Smith 1 A
> >> John Smith 2 B
> >> Jill Smith 1 A
> >> Jill Smith 2 B
> >>
> >> Thanks again,
> >>
> >> Ted
> >>
> >
> >
> > What key(s) exist in your table? Please include DDL in future so that we
> > don't have to guess.
> >
> > What version of SQL Server are you using? The following 2 possible answers
> > both assume 2005 or 2008. If we knew your table had a key and knew what
> > the key was then it might be possible to make a simpler or more efficient
> > solution to the same problem.
> >
> > SELECT t2.name, t2.col1, t2.col2
> > FROM
> > (SELECT DISTINCT name
> > FROM tbl) t1
> > CROSS APPLY
> > (SELECT DISTINCT TOP (2) name, col1, col2
> > FROM tbl
> > WHERE name = t1.name
> > ORDER BY col1, col2) t2;
> >
> > SELECT name, col1, col2
> > FROM
> > (SELECT name, col1, col2,
> > ROW_NUMBER() OVER (PARTITION BY name ORDER BY col1, col2) rn
> > FROM tbl) t
> > WHERE rn <=2;
> >
> > --
> > David Portas
> >
> >
>
>
>
 
 
SB





PostPosted: Mon Dec 31 02:45:25 CST 2007 Top

SQL Server >> SQL query help
> Hello all,
> I'm trying to write a query that limits the number of rows returned for ea=
ch
> unique person in the table. =A0For this particular query, I would like to
> limit the results to 2. =A0So John Doe may have 10 rows in the table but I=

> only want to display 2 rows in the results. =A0I also don't care which 2 r=
ows
> are returned for each person. =A0I have provided the table below and the
> results expected. =A0Any help is always appreciated!
>
> Example of rows in the table:
>
> Name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col1 =A0 =A0 =A0 =A0Col2
> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0A
> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0A
> John Smith =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 A
> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 A
> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 B
> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> John Smith =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0B
> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0B
> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0B
> John Smith =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0B
> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0B
>
> Results expected, once again, I don't care which two rows are return for
> each Name.
>
> Name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col1 =A0 =A0 =A0 =A0Col2
> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0A
> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 B
> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0A
> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> John Smith =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 A
> John Smith =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 A
> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0B
>
> Thanks again,
>
> Ted

select name, max(col1), min(col2)
from mytable
group by name
union all
select name, min(col1), min(col2)
from mytable
group by name
order by name
 
 
SB





PostPosted: Mon Dec 31 03:29:41 CST 2007 Top

SQL Server >> SQL query help

> The following query should work for you in SQL 2000.
>
> I assume that there wont be any duplicate rows with the exact same columns=

> values (as if there is a key on Name+Col1+Col2). =A0I have used the '~' ch=
r in
> this query assuming that this wont be present in any of your column data.
>
> select Name,Col1,Col2 from
> (
> select Name,Col1,Col2,
> (select count(*) from names =A0n2 where n1.name =3D n2.name and =A0(n1.Col=
1 + '~'
> + n1.Col2) >=3D =A0(n2.Col1 + '~'+ n2.Col2)) as SeqNo
> from names n1
> ) x
> where x.SeqNo <=3D2
>
> - Sha Anand
>
>
>

> > David,
> > Thanks for the reply! =A0The version of SQL is 2000. =A0Currently there =
is no
> > key on the table. =A0Is appears the Cross Apply isn't a feature in SQL 2=
000,
> > what other options do I have. =A0Thanks again.
>
> > >> Hello all,
> > >> I'm trying to write a query that limits the number of rows returned f=
or
> > >> each unique person in the table. =A0For this particular query, I woul=
d like
> > >> to limit the results to 2. =A0So John Doe may have 10 rows in the tab=
le but
> > >> I only want to display 2 rows in the results. =A0I also don't care wh=
ich 2
> > >> rows are returned for each person. =A0I have provided the table below=
and
> > >> the results expected. =A0Any help is always appreciated!
>
> > >> Example of rows in the table:
>
> > >> Name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col1 =A0 =A0 =A0 =A0Col2
> > >> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0A
> > >> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0A
> > >> John Smith =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 A
> > >> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 A
> > >> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 B
> > >> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > >> John Smith =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > >> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0B
> > >> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0B
> > >> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0B
> > >> John Smith =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0B
> > >> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0B=

>
> > >> Results expected, once again, I don't care which two rows are return =
for
> > >> each Name.
>
> > >> Name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col1 =A0 =A0 =A0 =A0Col2
> > >> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0A
> > >> Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 B
> > >> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0A
> > >> Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > >> John Smith =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 A
> > >> John Smith =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > >> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 A
> > >> Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0B
>
> > >> Thanks again,
>
> > >> Ted
>
> > > What key(s) exist in your table? Please include DDL in future so that =
we
> > > don't have to guess.
>
> > > What version of SQL Server are you using? The following 2 possible ans=
wers
> > > both assume 2005 or 2008. If we knew your table had a key and knew wha=
t
> > > the key was then it might be possible to make a simpler or more effici=
ent
> > > solution to the same problem.
>
> > > SELECT t2.name, t2.col1, t2.col2
> > > FROM
> > > (SELECT DISTINCT name
> > > FROM tbl) t1
> > > CROSS APPLY
> > > (SELECT DISTINCT TOP (2) name, col1, col2
> > > FROM tbl
> > > WHERE name =3D t1.name
> > > ORDER BY col1, col2) t2;
>
> > > SELECT name, col1, col2
> > > FROM
> > > (SELECT name, col1, col2,
> > > ROW_NUMBER() OVER (PARTITION BY name ORDER BY col1, col2) rn
> > > FROM tbl) t
> > > WHERE rn <=3D2;
>
> > > --
> > > David Portas- Hide quoted text -
>
> - Show quoted text -

Hi,
I think this maybe better:

select mytable.*
from mytable,
(select name, col1=3Dmax(col1)
from mytable
group by name) a
where mytable.name =3D a.name
and mytable.col1 =3D a.col1
union all
select mytable.*
from mytable,
(select name, col1=3Dmin(col1)
from mytable
group by name) a
where mytable.name =3D a.name
and mytable.col1 =3D a.col1
order by name
 
 
ShaAnand





PostPosted: Mon Dec 31 03:54:01 CST 2007 Top

SQL Server >> SQL query help You query wont work for the following data.

Jane Doe 1 A
Jane Doe 5 Z
Jane Doe 3 K

It will return the following rows, which are not present in the table !!
Jane Doe 3 A
Jane Doe 1 Z


- Sha Anand




> > Hello all,
> > I'm trying to write a query that limits the number of rows returned for each
> > unique person in the table. For this particular query, I would like to
> > limit the results to 2. So John Doe may have 10 rows in the table but I
> > only want to display 2 rows in the results. I also don't care which 2 rows
> > are returned for each person. I have provided the table below and the
> > results expected. Any help is always appreciated!
> >
> > Example of rows in the table:
> >
> > Name Col1 Col2
> > Joe Doe 1 A
> > Jane Doe 1 A
> > John Smith 1 A
> > Jill Smith 1 A
> > Joe Doe 2 B
> > Jane Doe 2 B
> > John Smith 2 B
> > Jill Smith 2 B
> > Joe Doe 3 B
> > Jane Doe 3 B
> > John Smith 3 B
> > Jill Smith 3 B
> >
> > Results expected, once again, I don't care which two rows are return for
> > each Name.
> >
> > Name Col1 Col2
> > Joe Doe 1 A
> > Joe Doe 2 B
> > Jane Doe 1 A
> > Jane Doe 2 B
> > John Smith 1 A
> > John Smith 2 B
> > Jill Smith 1 A
> > Jill Smith 2 B
> >
> > Thanks again,
> >
> > Ted
>
> select name, max(col1), min(col2)
> from mytable
> group by name
> union all
> select name, min(col1), min(col2)
> from mytable
> group by name
> order by name
>
 
 
SB





PostPosted: Mon Dec 31 04:06:27 CST 2007 Top

SQL Server >> SQL query help

> You query wont work for the following data.
>
> Jane Doe =A0 =A0 =A01 =A0 =A0 =A0 A
> Jane Doe =A0 =A0 =A05 =A0 =A0 =A0 Z
> Jane Doe =A0 =A0 =A03 =A0 =A0 =A0 K
>
> It will return the following rows, which are not present in the table !!
> Jane Doe =A03 =A0A
> Jane Doe =A01 =A0Z
>
> - Sha Anand
>
>
>


> > > Hello all,
> > > I'm trying to write a query that limits the number of rows returned fo=
r each
> > > unique person in the table. =A0For this particular query, I would like=
to
> > > limit the results to 2. =A0So John Doe may have 10 rows in the table b=
ut I
> > > only want to display 2 rows in the results. =A0I also don't care which=
2 rows
> > > are returned for each person. =A0I have provided the table below and t=
he
> > > results expected. =A0Any help is always appreciated!
>
> > > Example of rows in the table:
>
> > > Name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col1 =A0 =A0 =A0 =A0Col2
> > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0A
> > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0A
> > > John Smith =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 A
> > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 A
> > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 B
> > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > > John Smith =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0B
> > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0B
> > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0B
> > > John Smith =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0B
> > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0B
>
> > > Results expected, once again, I don't care which two rows are return f=
or
> > > each Name.
>
> > > Name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col1 =A0 =A0 =A0 =A0Col2
> > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0A
> > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 B
> > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0A
> > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > > John Smith =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 A
> > > John Smith =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 A
> > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0B
>
> > > Thanks again,
>
> > > Ted
>
> > select name, max(col1), min(col2)
> > from mytable
> > group by name
> > union all
> > select name, min(col1), min(col2)
> > from mytable
> > group by name
> > order by name- Hide quoted text -
>
> - Show quoted text -

Here is the table:

Jane Doe 1 A
Jane Doe 2 B
Jane Doe 3 B
Jane Doe 1 A
Jane Doe 5 Z
Jane Doe 3 K
Jill Smith 3 B
Jill Smith 2 B
Jill Smith 1 A
Joe Doe 2 B
Joe Doe 3 B
Joe Doe 1 A
John Smith 3 B
John Smith 2 B
John Smith 1 A


Here is the result:

Jane Doe 1 A
Jane Doe 5 Z
Jill Smith 1 A
Jill Smith 3 B
Joe Doe 1 A
Joe Doe 3 B
John Smith 1 A
John Smith 3 B

Query:
select mytable.*
from mytable,
(select name, col1=3Dmax(col1)
from mytable
group by name) a
where mytable.name =3D a.name
and mytable.col1 =3D a.col1
union
select mytable.*
from mytable,
(select name, col1=3Dmin(col1)
from mytable
group by name) a
where mytable.name =3D a.name
and mytable.col1 =3D a.col1
order by name
 
 
David





PostPosted: Mon Dec 31 04:19:53 CST 2007 Top

SQL Server >> SQL query help

> David,
> Thanks for the reply! The version of SQL is 2000. Currently there is no
> key on the table. Is appears the Cross Apply isn't a feature in SQL 2000,
> what other options do I have. Thanks again.
>

Then first determine what the key(s) should be and create the appropriate
constraints. There is no point trying to solve query problems before you
have completed the design.

--
David Portas


 
 
ShaAnand





PostPosted: Mon Dec 31 04:32:01 CST 2007 Top

SQL Server >> SQL query help Did you try running the Query ???. It does not give the right results !!!





> > You query wont work for the following data.
> >
> > Jane Doe 1 A
> > Jane Doe 5 Z
> > Jane Doe 3 K
> >
> > It will return the following rows, which are not present in the table !!
> > Jane Doe 3 A
> > Jane Doe 1 Z
> >
> > - Sha Anand
> >
> >
> >


> > > > Hello all,
> > > > I'm trying to write a query that limits the number of rows returned for each
> > > > unique person in the table. For this particular query, I would like to
> > > > limit the results to 2. So John Doe may have 10 rows in the table but I
> > > > only want to display 2 rows in the results. I also don't care which 2 rows
> > > > are returned for each person. I have provided the table below and the
> > > > results expected. Any help is always appreciated!
> >
> > > > Example of rows in the table:
> >
> > > > Name Col1 Col2
> > > > Joe Doe 1 A
> > > > Jane Doe 1 A
> > > > John Smith 1 A
> > > > Jill Smith 1 A
> > > > Joe Doe 2 B
> > > > Jane Doe 2 B
> > > > John Smith 2 B
> > > > Jill Smith 2 B
> > > > Joe Doe 3 B
> > > > Jane Doe 3 B
> > > > John Smith 3 B
> > > > Jill Smith 3 B
> >
> > > > Results expected, once again, I don't care which two rows are return for
> > > > each Name.
> >
> > > > Name Col1 Col2
> > > > Joe Doe 1 A
> > > > Joe Doe 2 B
> > > > Jane Doe 1 A
> > > > Jane Doe 2 B
> > > > John Smith 1 A
> > > > John Smith 2 B
> > > > Jill Smith 1 A
> > > > Jill Smith 2 B
> >
> > > > Thanks again,
> >
> > > > Ted
> >
> > > select name, max(col1), min(col2)
> > > from mytable
> > > group by name
> > > union all
> > > select name, min(col1), min(col2)
> > > from mytable
> > > group by name
> > > order by name- Hide quoted text -
> >
> > - Show quoted text -
>
> Here is the table:
>
> Jane Doe 1 A
> Jane Doe 2 B
> Jane Doe 3 B
> Jane Doe 1 A
> Jane Doe 5 Z
> Jane Doe 3 K
> Jill Smith 3 B
> Jill Smith 2 B
> Jill Smith 1 A
> Joe Doe 2 B
> Joe Doe 3 B
> Joe Doe 1 A
> John Smith 3 B
> John Smith 2 B
> John Smith 1 A
>
>
> Here is the result:
>
> Jane Doe 1 A
> Jane Doe 5 Z
> Jill Smith 1 A
> Jill Smith 3 B
> Joe Doe 1 A
> Joe Doe 3 B
> John Smith 1 A
> John Smith 3 B
>
> Query:
> select mytable.*
> from mytable,
> (select name, col1=max(col1)
> from mytable
> group by name) a
> where mytable.name = a.name
> and mytable.col1 = a.col1
> union
> select mytable.*
> from mytable,
> (select name, col1=min(col1)
> from mytable
> group by name) a
> where mytable.name = a.name
> and mytable.col1 = a.col1
> order by name
>
 
 
SB





PostPosted: Mon Dec 31 04:48:53 CST 2007 Top

SQL Server >> SQL query help

> Did you try running the Query ???. It does not give the right results !!!
>
>
>



> > > You query wont work for the following data.
>
> > > Jane Doe =A0 =A0 =A01 =A0 =A0 =A0 A
> > > Jane Doe =A0 =A0 =A05 =A0 =A0 =A0 Z
> > > Jane Doe =A0 =A0 =A03 =A0 =A0 =A0 K
>
> > > It will return the following rows, which are not present in the table =
!!
> > > Jane Doe =A03 =A0A
> > > Jane Doe =A01 =A0Z
>
> > > - Sha Anand
>


> > > > > Hello all,
> > > > > I'm trying to write a query that limits the number of rows returne=
d for each
> > > > > unique person in the table. =A0For this particular query, I would =
like to
> > > > > limit the results to 2. =A0So John Doe may have 10 rows in the tab=
le but I
> > > > > only want to display 2 rows in the results. =A0I also don't care w=
hich 2 rows
> > > > > are returned for each person. =A0I have provided the table below a=
nd the
> > > > > results expected. =A0Any help is always appreciated!
>
> > > > > Example of rows in the table:
>
> > > > > Name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col1 =A0 =A0 =A0 =A0Col2
> > > > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0A
> > > > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0A
> > > > > John Smith =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 A
> > > > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 A=

> > > > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 B=

> > > > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > > > > John Smith =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > > > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =
=A0B
> > > > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0B=

> > > > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0B=

> > > > > John Smith =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 =A0 =A0 =A0 =A0B
> > > > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =
=A0B
>
> > > > > Results expected, once again, I don't care which two rows are retu=
rn for
> > > > > each Name.
>
> > > > > Name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Col1 =A0 =A0 =A0 =A0Col2
> > > > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0A
> > > > > Joe Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =A0 B=

> > > > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0A
> > > > > Jane Doe =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > > > > John Smith =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 A
> > > > > John Smith =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0B
> > > > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 A=

> > > > > Jill Smith =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 =A0 =A0 =A0 =
=A0B
>
> > > > > Thanks again,
>
> > > > > Ted
>
> > > > select name, max(col1), min(col2)
> > > > from mytable
> > > > group by name
> > > > union all
> > > > select name, min(col1), min(col2)
> > > > from mytable
> > > > group by name
> > > > order by name- Hide quoted text -
>
> > > - Show quoted text -
>
> > Here is the table:
>
> > Jane Doe =A0 1 =A0 =A0 =A0 A
> > Jane Doe =A0 2 =A0 =A0 =A0 B
> > Jane Doe =A0 3 =A0 =A0 =A0 B
> > Jane Doe =A0 1 =A0 =A0 =A0 A
> > Jane Doe =A0 5 =A0 =A0 =A0 Z
> > Jane Doe =A0 3 =A0 =A0 =A0 K
> > Jill Smith 3 =A0 =A0 =A0 B
> > Jill Smith 2 =A0 =A0 =A0 B
> > Jill Smith 1 =A0 =A0 =A0 A
> > Joe Doe =A0 =A02 =A0 =A0 =A0 B
> > Joe Doe =A0 =A03 =A0 =A0 =A0 B
> > Joe Doe =A0 =A01 =A0 =A0 =A0 A
> > John Smith 3 =A0 =A0 =A0 B
> > John Smith 2 =A0 =A0 =A0 B
> > John Smith 1 =A0 =A0 =A0 A
>
> > Here is the result:
>
> > Jane Doe =A0 1 =A0 =A0 =A0 A
> > Jane Doe =A0 5 =A0 =A0 =A0 Z
> > Jill Smith 1 =A0 =A0 =A0 A
> > Jill Smith 3 =A0 =A0 =A0 B
> > Joe Doe =A0 =A01 =A0 =A0 =A0 A
> > Joe Doe =A0 =A03 =A0 =A0 =A0 B
> > John Smith 1 =A0 =A0 =A0 A
> > John Smith 3 =A0 =A0 =A0 B
>
> > Query:
> > select mytable.*
> > from mytable,
> > (select name, col1=3Dmax(col1)
> > from mytable
> > group by name) a
> > where mytable.name =3D a.name
> > and mytable.col1 =3D a.col1
> > union
> > select mytable.*
> > from mytable,
> > (select name, col1=3Dmin(col1)
> > from mytable
> > group by name) a
> > where mytable.name =3D a.name
> > and mytable.col1 =3D a.col1
> > order by name- Hide quoted text -
>
> - Show quoted text -

No I was waiting for you to run it! :)