SQL query help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Travis - 30 Dec 2007 21:50 GMT

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

Reply to this Message

David Portas - 30 Dec 2007 22:17 GMT

> Hello all,
> I'm trying to write a query that limits the number of rows returned for
[quoted text clipped - 36 lines]
>
> 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;

Signature

David Portas

Reply to this Message

Ted - 31 Dec 2007 00:41 GMT

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
[quoted text clipped - 61 lines]
> FROM tbl) t
> WHERE rn <=2;

Reply to this Message

Sha Anand - 31 Dec 2007 05:22 GMT

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
[quoted text clipped - 66 lines]
> > FROM tbl) t
> > WHERE rn <=2;

Reply to this Message

SB - 31 Dec 2007 09:29 GMT

On Dec 31, 11:22 am, Sha Anand <ShaAn...@discussions.microsoft.com>
wrote:

> The following query should work for you in SQL 2000.
>
[quoted text clipped - 88 lines]
>
> - Show quoted text -

Hi,
I think this maybe better:

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 all
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

Reply to this Message

David Portas - 31 Dec 2007 10:19 GMT

> 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.

Signature

David Portas

Reply to this Message

SB - 31 Dec 2007 08:45 GMT

> Hello all,
> I'm trying to write a query that limits the number of rows returned for each
[quoted text clipped - 36 lines]
>
> 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

Reply to this Message

Sha Anand - 31 Dec 2007 09:54 GMT

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
[quoted text clipped - 45 lines]
> group by name
> order by name

Reply to this Message

SB - 31 Dec 2007 10:06 GMT

On Dec 31, 3:54 pm, Sha Anand <ShaAn...@discussions.microsoft.com>
wrote:

> You query wont work for the following data.
>
[quoted text clipped - 59 lines]
>
> - 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

Reply to this Message

Sha Anand - 31 Dec 2007 10:32 GMT

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

> On Dec 31, 3:54 pm, Sha Anand <ShaAn...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 108 lines]
> and mytable.col1 = a.col1
> order by name

Reply to this Message

SB - 31 Dec 2007 10:48 GMT

On Dec 31, 4:32 pm, Sha Anand <ShaAn...@discussions.microsoft.com>
wrote:

> Did you try running the Query ???. It does not give the right results !!!
>
[quoted text clipped - 112 lines]
>
> - Show quoted text -

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

Reply to this Message