Prev: Need to return more than one record from sub-query -- why doesn't this work?
Next: Help
From: Rich on 21 Jul 2008 15:36
I need to select the count of the first 3 rows per each group (xID) up to a
count of 3 rows (top 3 rows - order by column x1) where the stat column is
not null. if a group contains more than 3 rows where stat is not null -
ignore the extra rows - only count the first 3 rows in each group

CREATE TABLE #tmp1(rowID int Identity(1,1), xID int, x1 int, stat varchar(1))

INSERT INTO #tmp1
SELECT 1, 1, 'f'
UNION ALL SELECT 1, 2, 'f'
UNION ALL SELECT 1, 3, 'f'
UNION ALL SELECT 1, 4, 'f'
UNION ALL SELECT 2, 1, 'f'
UNION ALL SELECT 2, 2, null
UNION ALL SELECT 2, 3, 'f'
UNION ALL SELECT 2, 4, 'f'
UNION ALL SELECT 3, 1, 'f'
UNION ALL SELECT 3, 2, 'f'
UNION ALL SELECT 3, 3, null

Resulting output to look like this

xID countOfRows, Stat
1 3 'f'
2 2 'f'
3 2 'f'

I have tried a self join but not working out - maybe I don't need a self
join? How can I accomplish this?

Thanks,
Rich
From: SQL Menace on 21 Jul 2008 15:57
What about this?

select xID,case when count(*) > 3 then 3 else count(*) end
countOfRows ,[stat]
from #tmp1
where stat is not null
group by xID,[stat]



Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

On Jul 21, 3:36 pm, Rich <R...(a)discussions.microsoft.com> wrote:
> I need to select the count of the first 3 rows per each group (xID) up to a
> count of 3 rows (top 3 rows - order by column x1) where the stat column is
> not null.  if a group contains more than 3 rows where stat is not null -
> ignore the extra rows - only count the first 3 rows in each group
>
> CREATE TABLE #tmp1(rowID int Identity(1,1), xID int, x1 int, stat varchar(1))
>
> INSERT INTO #tmp1
> SELECT 1, 1, 'f'
> UNION ALL SELECT 1, 2, 'f'
> UNION ALL SELECT 1, 3, 'f'
> UNION ALL SELECT 1, 4, 'f'
> UNION ALL SELECT 2, 1, 'f'
> UNION ALL SELECT 2, 2, null
> UNION ALL SELECT 2, 3, 'f'
> UNION ALL SELECT 2, 4, 'f'
> UNION ALL SELECT 3, 1, 'f'
> UNION ALL SELECT 3, 2, 'f'
> UNION ALL SELECT 3, 3, null
>
> Resulting output to look like this
>
> xID   countOfRows,   Stat
> 1           3                 'f'
> 2           2                 'f'
> 3           2                 'f'
>
> I have tried a self join but not working out - maybe I don't need a self
> join?  How can I accomplish this?
>
> Thanks,
> Rich

From: --CELKO-- on 21 Jul 2008 16:07
If we get rid of IDENTITY and go with a proper relational design,
would your table look like this? The constraints and a real key are
important.

CREATE TABLE Foobar
(x_id INTEGER NOT NULL,
x1 INTEGER NOT NULL,
PRIMARY KEY (x_id, x1),
stat VARCHAR(1));

>> I need to select the count of the first 3 rows per each group (x_id) up to a count of 3 rows (top 3 rows - order by column x1) where the stat column is not null. if a group contains more than 3 rows where stat is not null - ignore the extra rows - only count the first 3 rows in each group <<

SELKCT *
FROM (SELECT x_id, x1,
ROW_NUMBER() OVER (PARTITION BY x_id ORDER BY x1)
FROM Foobar
WHERE stat IS NOT NULL)
AS X(x_id, x1, x_place)
WHERE x_place <= 3;

From: Rich on 21 Jul 2008 16:06
That worked perfectly! Thank you very much.

"SQL Menace" wrote:

> What about this?
>
> select xID,case when count(*) > 3 then 3 else count(*) end
> countOfRows ,[stat]
> from #tmp1
> where stat is not null
> group by xID,[stat]
>
>
>
> Denis The SQL Menace
> http://www.lessthandot.com/
> http://sqlservercode.blogspot.com
> http://sqlblog.com/blogs/denis_gobo/default.aspx
>
> On Jul 21, 3:36 pm, Rich <R...(a)discussions.microsoft.com> wrote:
> > I need to select the count of the first 3 rows per each group (xID) up to a
> > count of 3 rows (top 3 rows - order by column x1) where the stat column is
> > not null. if a group contains more than 3 rows where stat is not null -
> > ignore the extra rows - only count the first 3 rows in each group
> >
> > CREATE TABLE #tmp1(rowID int Identity(1,1), xID int, x1 int, stat varchar(1))
> >
> > INSERT INTO #tmp1
> > SELECT 1, 1, 'f'
> > UNION ALL SELECT 1, 2, 'f'
> > UNION ALL SELECT 1, 3, 'f'
> > UNION ALL SELECT 1, 4, 'f'
> > UNION ALL SELECT 2, 1, 'f'
> > UNION ALL SELECT 2, 2, null
> > UNION ALL SELECT 2, 3, 'f'
> > UNION ALL SELECT 2, 4, 'f'
> > UNION ALL SELECT 3, 1, 'f'
> > UNION ALL SELECT 3, 2, 'f'
> > UNION ALL SELECT 3, 3, null
> >
> > Resulting output to look like this
> >
> > xID countOfRows, Stat
> > 1 3 'f'
> > 2 2 'f'
> > 3 2 'f'
> >
> > I have tried a self join but not working out - maybe I don't need a self
> > join? How can I accomplish this?
> >
> > Thanks,
> > Rich
>
>
From: Rich on 21 Jul 2008 16:59
Yes, but in my case the test table is really the detail table and xID is the
foreign key. However, your suggestion is correct about proper relational
design. I should adopt this method.

Thank you for the suggestion.

"--CELKO--" wrote:

> If we get rid of IDENTITY and go with a proper relational design,
> would your table look like this? The constraints and a real key are
> important.
>
> CREATE TABLE Foobar
> (x_id INTEGER NOT NULL,
> x1 INTEGER NOT NULL,
> PRIMARY KEY (x_id, x1),
> stat VARCHAR(1));
>
> >> I need to select the count of the first 3 rows per each group (x_id) up to a count of 3 rows (top 3 rows - order by column x1) where the stat column is not null. if a group contains more than 3 rows where stat is not null - ignore the extra rows - only count the first 3 rows in each group <<
>
> SELKCT *
> FROM (SELECT x_id, x1,
> ROW_NUMBER() OVER (PARTITION BY x_id ORDER BY x1)
> FROM Foobar
> WHERE stat IS NOT NULL)
> AS X(x_id, x1, x_place)
> WHERE x_place <= 3;
>
>
 | 
Pages: 1
Prev: Need to return more than one record from sub-query -- why doesn't this work?
Next: Help