How to get Top 3 records in the group level?

Hi all,

I have a query which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. I tried using 'select top 3....', but the result is not what I wanted. It doesn't consider the grouping. Is there any way to achieve this task?

Please let me know if some body has any idea.

Thx.

The answer depends on whether or not you have SQL Server 2005 or SQL Server 2000. If you are using SQL Server 2005 you can use the ROW_NUMBER function. What is also needed is to know what field you are ordering your groups by (to get the top 3).

Dave

|||Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

How about this:

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(3), text_data NVARCHAR(50));

INSERT INTO t1 VALUES ('A', 'Text for A1');
INSERT INTO t1 VALUES ('A', 'Text for A2');
INSERT INTO t1 VALUES ('A', 'Text for A3');
INSERT INTO t1 VALUES ('A', 'Text for A4');
INSERT INTO t1 VALUES ('B', 'Text for B1');
INSERT INTO t1 VALUES ('B', 'Text for B2');
INSERT INTO t1 VALUES ('B', 'Text for B3');
INSERT INTO t1 VALUES ('B', 'Text for B4');
INSERT INTO t1 VALUES ('C', 'Text for C1');
INSERT INTO t1 VALUES ('C', 'Text for C2');
INSERT INTO t1 VALUES ('C', 'Text for C3');
INSERT INTO t1 VALUES ('C', 'Text for C4');

-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)

|||

Amde:

I set up a mock-up to compare Lucky's query with a query based on ROW_NUMBER. I created the T1 table with 32767 distinct R_ID entries and 30 distinct TEXT_DATA entries for each R_ID -- nearly 1,000,000 rows. When I ran Lucky's query with the table without any indexes it took about 102 seconds with 6.6 million logical IOs. I then added an index based on R_ID only and repeated the run. Again, it took about 102 seconds with 6.6 million logical IOs. I then looked at the SHOWPLAN and verified that the plan was still using a TABLE scan and was not using the index. This is expected because use of the index would require a prohibitive amount of bookmark lookups. I lastly dropped that index and added a cover index based on both R_ID and TEXT_DATA and repeated the run. This time it took 17 seconds with 3.1 million logical IOs.

Next, I repeated this using a query based on ROW_NUMBER. I first ran the query without any indexes. It took 7263 ms with 7709 logical IOs. I added an index based on R_ID only and it again took about 7600 ms with 7709 logical IOs. I then dropped that index and added an index based on both R_ID and TEXT_DATA. This query then took 2406 ms with 7706 logical IOs. This is the script I used for testing:

set nocount on
use tempdb
drop table t1
go

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(5), text_data NVARCHAR(50));
--exec sp_help t1

insert into t1
select convert (nvarchar (5), i.iter),
'Text for ' + convert (nvarchar(5), i.iter)
+ '-' + convert (nchar(2), j.iter)
from mugambo.dbo.small_iterator (nolock) i
inner join mugambo.dbo.small_iterator (nolock) j
on j.iter <= 30
--select * from t1

--drop index t1.t1_ndx
--create index t1_ndx on t1(r_id)
create index t1_ndx on t1(r_id, text_data)
update statistics t1
go

declare @.begDt datetime set @.begDt = getdate()

/*
--insert into @.receiver
-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)
*/

set statistics io on

select r_id,
text_data
from ( select r_id,
row_number () over
(partition by R_ID order by text_data)
as seq,
text_data
from t1
) x
where seq <= 3

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- r_id text_data
-- -- --
-- 1 Text for 1-1
-- 1 Text for 1-10
-- ...
-- 9999 Text for 9999-11

-- Table 't1'. Scan count 1, logical reads 7706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2220

|||

Hi,

keeping same table as Lucky_p has dipicted , just try this

select *from

(SELECT i.r_id, i.text_data,

DENSE_RANK() OVER (PARTITION BY i.r_id order by text_data) as RANK

FROM t1 i) A where rank<=3

Madhu

|||

That's all nice and i agree my solution is not fast when the table is large.

Do you have a fast solution for SQL 2000 too? All your solutions are for SQL 2005.

|||

Lucky:

I cannot come up with a method for SQL Server 2000 that is any faster than your version. Hopefully, one of the senior members can shed some light; I struck out.

Dave

|||

I found an alternative that might be of some use:

declare @.top3 table
( r_id nvarchar (5) not null,
seq integer not null,
text_data nvarchar (50) not null,

primary key (r_id, seq)
)

insert into @.top3
select r_id,
1,
min(text_data)
from t1
group by r_id

insert into @.top3
select r_id,
2,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a

insert into @.top3
select r_id,
3,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a
where seq = 2

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- Table '#33ADF722'. Scan count 0, logical reads 66286, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 1, logical reads 7640, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 97120, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98558, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 100528, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98552, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 2766

|||

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

|||

Lucky P wrote:

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

again with little modification u can fine tune this script.. eliminate Temp table

Select *from

(SELECT top 100 R_ID , text_data,

(SELECT COUNT(*) FROM T1 AS b

WHERE b.R_ID = a.R_ID

AND (b.text_data < a.text_data

OR (b.text_data = a.text_data))) AS rownum

FROM T1 AS a

ORDER BY R_ID, text_data) A

where rownum<4

Madhu

|||

I tested these other queries too with my "million-row" mockup and here is what I think:

If running SQL Server 2005 use either Madhu's or my first query|||

Your getting a partial result because of these line:

(SELECT top 100 R_ID , text_data,

If You change it to

(SELECT top 100 PERCENT R_ID , text_data,

You should get all the rows....

|||

Thanks for catching that lucky; I tested this one too and your original query is still much faster.

Dave

|||

ms sql server

data:

idno Email Category
1 a@.a.com c
2 b@.b.com c
3 b@.b.com c
4 b@.b.com c

only unique email address record are requierd.
1 a@.a.com c
2 b@.b.com c

what could be the query for such requirements.

How to get Top 3 records in the group level?

Hi all,

I have a query which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. I tried using 'select top 3....', but the result is not what I wanted. It doesn't consider the grouping. Is there any way to achieve this task?

Please let me know if some body has any idea.

Thx.

The answer depends on whether or not you have SQL Server 2005 or SQL Server 2000. If you are using SQL Server 2005 you can use the ROW_NUMBER function. What is also needed is to know what field you are ordering your groups by (to get the top 3).

Dave

|||Doing this in a report wouldn′t be the best way as you would get all the data from the server and then only would display a part of it. better filter the data on the server and send only back the appropiate results, then you would have no problem on the client / reporting service. Anway, if you want to do this, you might have a look at the RowNumber() property in Reporting Services. You could set the Visibility of the row to Visible=True if the RowNumber("GroupName") equals or is less than 3 =IIF(Rownumber("GroupName") <= 3;True;False)

Doing this on the server depends on which server version you are using. SQL Server 2005 probably would enable you to use ROW_NUMBER() on the server side. SQL Server 2k does not implement this new function, so you would have to use something else instead.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

How about this:

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(3), text_data NVARCHAR(50));

INSERT INTO t1 VALUES ('A', 'Text for A1');
INSERT INTO t1 VALUES ('A', 'Text for A2');
INSERT INTO t1 VALUES ('A', 'Text for A3');
INSERT INTO t1 VALUES ('A', 'Text for A4');
INSERT INTO t1 VALUES ('B', 'Text for B1');
INSERT INTO t1 VALUES ('B', 'Text for B2');
INSERT INTO t1 VALUES ('B', 'Text for B3');
INSERT INTO t1 VALUES ('B', 'Text for B4');
INSERT INTO t1 VALUES ('C', 'Text for C1');
INSERT INTO t1 VALUES ('C', 'Text for C2');
INSERT INTO t1 VALUES ('C', 'Text for C3');
INSERT INTO t1 VALUES ('C', 'Text for C4');

-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)

|||

Amde:

I set up a mock-up to compare Lucky's query with a query based on ROW_NUMBER. I created the T1 table with 32767 distinct R_ID entries and 30 distinct TEXT_DATA entries for each R_ID -- nearly 1,000,000 rows. When I ran Lucky's query with the table without any indexes it took about 102 seconds with 6.6 million logical IOs. I then added an index based on R_ID only and repeated the run. Again, it took about 102 seconds with 6.6 million logical IOs. I then looked at the SHOWPLAN and verified that the plan was still using a TABLE scan and was not using the index. This is expected because use of the index would require a prohibitive amount of bookmark lookups. I lastly dropped that index and added a cover index based on both R_ID and TEXT_DATA and repeated the run. This time it took 17 seconds with 3.1 million logical IOs.

Next, I repeated this using a query based on ROW_NUMBER. I first ran the query without any indexes. It took 7263 ms with 7709 logical IOs. I added an index based on R_ID only and it again took about 7600 ms with 7709 logical IOs. I then dropped that index and added an index based on both R_ID and TEXT_DATA. This query then took 2406 ms with 7706 logical IOs. This is the script I used for testing:

set nocount on
use tempdb
drop table t1
go

-- sample table / data
CREATE TABLE t1 (R_ID nvarchar(5), text_data NVARCHAR(50));
--exec sp_help t1

insert into t1
select convert (nvarchar (5), i.iter),
'Text for ' + convert (nvarchar(5), i.iter)
+ '-' + convert (nchar(2), j.iter)
from mugambo.dbo.small_iterator (nolock) i
inner join mugambo.dbo.small_iterator (nolock) j
on j.iter <= 30
--select * from t1

--drop index t1.t1_ndx
--create index t1_ndx on t1(r_id)
create index t1_ndx on t1(r_id, text_data)
update statistics t1
go

declare @.begDt datetime set @.begDt = getdate()

/*
--insert into @.receiver
-- Retrieve TOP(3) Entries for Each R_ID
SELECT R_ID, text_data
FROM t1 AS a
WHERE text_data IN
(SELECT TOP(3) text_data
FROM t1 AS b
WHERE a.R_ID = b.R_ID
ORDER BY text_data)
*/

set statistics io on

select r_id,
text_data
from ( select r_id,
row_number () over
(partition by R_ID order by text_data)
as seq,
text_data
from t1
) x
where seq <= 3

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- r_id text_data
-- -- --
-- 1 Text for 1-1
-- 1 Text for 1-10
-- ...
-- 9999 Text for 9999-11

-- Table 't1'. Scan count 1, logical reads 7706, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2220

|||

Hi,

keeping same table as Lucky_p has dipicted , just try this

select *from

(SELECT i.r_id, i.text_data,

DENSE_RANK() OVER (PARTITION BY i.r_id order by text_data) as RANK

FROM t1 i) A where rank<=3

Madhu

|||

That's all nice and i agree my solution is not fast when the table is large.

Do you have a fast solution for SQL 2000 too? All your solutions are for SQL 2005.

|||

Lucky:

I cannot come up with a method for SQL Server 2000 that is any faster than your version. Hopefully, one of the senior members can shed some light; I struck out.

Dave

|||

I found an alternative that might be of some use:

declare @.top3 table
( r_id nvarchar (5) not null,
seq integer not null,
text_data nvarchar (50) not null,

primary key (r_id, seq)
)

insert into @.top3
select r_id,
1,
min(text_data)
from t1
group by r_id

insert into @.top3
select r_id,
2,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a

insert into @.top3
select r_id,
3,
( select min (b.text_data) from t1 b
where a.r_id = b.r_id
and b.text_data > a.text_data
)
from @.top3 a
where seq = 2

set statistics io off

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Output:

-- Table '#33ADF722'. Scan count 0, logical reads 66286, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 1, logical reads 7640, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 97120, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98558, physical reads 0, read-ahead reads 0.
-- Table '#33ADF722'. Scan count 1, logical reads 100528, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 1, logical reads 33468, physical reads 0, read-ahead reads 0.
-- Table 't1'. Scan count 32767, logical reads 98552, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 2766

|||

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

|||

Lucky P wrote:

I have an additional solution for SQL 2000 which uses a Partition calculation. But I'm not sure if its faster than the other 2000 compatible approach.

-- Create a Temporary Table using an additional column
-- Row-Number Partitioned by R_ID
SELECT R_ID , text_data,
(SELECT COUNT(*) FROM T1 AS b
WHERE b.R_ID = a.R_ID
AND (b.text_data < a.text_data
OR (b.text_data = a.text_data))) AS rownum
INTO #T1
FROM T1 AS a
ORDER BY R_ID, text_data;

-- Get the results from Temporary Table
SELECT R_ID, text_data FROM #T1
WHERE rownum <= 3

again with little modification u can fine tune this script.. eliminate Temp table

Select *from

(SELECT top 100 R_ID , text_data,

(SELECT COUNT(*) FROM T1 AS b

WHERE b.R_ID = a.R_ID

AND (b.text_data < a.text_data

OR (b.text_data = a.text_data))) AS rownum

FROM T1 AS a

ORDER BY R_ID, text_data) A

where rownum<4

Madhu

|||

I tested these other queries too with my "million-row" mockup and here is what I think:

If running SQL Server 2005 use either Madhu's or my first query|||

Your getting a partial result because of these line:

(SELECT top 100 R_ID , text_data,

If You change it to

(SELECT top 100 PERCENT R_ID , text_data,

You should get all the rows....

|||

Thanks for catching that lucky; I tested this one too and your original query is still much faster.

Dave

|||

ms sql server

data:

idno Email Category
1 a@.a.com c
2 b@.b.com c
3 b@.b.com c
4 b@.b.com c

only unique email address record are requierd.
1 a@.a.com c
2 b@.b.com c

what could be the query for such requirements.

how to get this out put

hi all

this is my quary

SELECT RFVDM_CODE,RFVAL_REFNO,DESCRIPTION
FROM REFERENCEVALUES
GROUP BY RFVDM_CODE,RFVAL_REFNO,DESCRIPTION
ORDER BY RFVDM_CODE

this quary provde a out put like

RFVDM_CODE RFVAL_REFNO DESCRIPTION
ACTDF 2004485 Caseload
ACTDF 2004486 Assessment
ACTDF 2004487 Outpatient appointment
ACTDF 2004488 Contact
ACTDF 2004620 Not Specified
ACTIV 7 Not Specified
ACTIV 217 Book Outpatient Appointment.
ACTPE 8 Not Specified
ACTPE 221 Contact
ACTPE 222 Care Plan Review

i need to get this out put like this

RFVDM_CODE RFVAL_REFNO DESCRIPTION
ACTDF 2004485 Caseload

2004486 Assessment

2004487 Outpatient appointment

2004488 Contact

2004620 Not Specified

ACTIV 7 Not Specified

217 Book Outpatient Appointment.

ACTPE 8 Not Specified

221 Contact

222 Care Plan Review

can i do this

any idea

thanx

its not possible in any single statement. Yes you can do it in SQL Server but not advisable. These kind of formating should be the responsiblity of FE. Do it in FE and its much more flexible.

Madhu

|||

Hi,

Try this one:

select case num when 1 then RFVDM_CODE else '' end RFVDM_CODE, RFVAL_REFNO, DESCRIPTION

from

(select *,

ROW_NUMBER() OVER(Partition By RFVDM_CODE ORDER BY RFVDM_CODE asc) num

from ReferenceValues) s

Although i agree that it should be done on the front end, becuase it's a user layout thing, this table actually has a diferent meaning than the one with the repeated values.

Regards,

Gert-Jan

|||

Gert solution is possible only in SQL Server 2005. mind it.. Thought there are method ,i strongly feel that this should be handled at the FE.

Madhu

|||

Hi thank you.

that is working finely. if there any duplicate values then how we sort out that from this quary.

regards
Nirangasql

How to get the value of number of rows in groupfooter

I want to show how many records populated in the groupby clause, i want to show the number of rows value in the group footer.

Thank you very much.

If you put this in the group footer, you should see the count for each group.

=CountRows()

Hope this helps.

Jarret

sql

how to get the sum of a group and not the whole column?

?can you explain further? any sample data with desired output will help|||

example:

Account Sales

New

John Doe 1,000,000

George Bush 2,000,000

Juan Luna 3,000,000

6,000,000

Old

Michael Tell 5,000,000

Billy Banks 2,000,000

7,000,000

where Account and Sales are table columns, New and Old are group names.

i want to display the total of the group.

|||anyone? something like a GroupSum() function.|||i have no Reporting services installed on my system but in Crystal Reports, you can do this by creating a Running Total field...|||

You can do this by using a matrix, and put two row groups on it: the first grouping by account age (new/old), the second by name.

Or you can put them in a list control and apply a group to the list, and sum the sales inside the list.

sluggy

|||there's a runningValue function but I'm getting exceptions when I use it. I just want to display the subtotal not the Total of the sales column.|||

CryptoKnight wrote:

i have no Reporting services installed on my system but in Crystal Reports, you can do this by creating a Running Total field...

Account Sales

New

John Doe 1,000,000

George Bush 2,000,000

Juan Luna 3,000,000

6,000,000

Old

Michael Tell 5,000,000

Billy Banks 2,000,000

13,000,000

this is the output when I use the running value.

|||

icemart525 wrote:

there's a runningValue function but I'm getting exceptions when I use it. I just want to display the subtotal not the Total of the sales column.

is there a reset on change of group property? try setting it to reset after every change of group if there's one...|||nope I can't find one.|||I've seen a lot of topics about subtotals in the web but they did not mention how to do it. should I add a new dataset for this?|||

icemart525 wrote:

I've seen a lot of topics about subtotals in the web but they did not mention how to do it. should I add a new dataset for this?

You are confusing me.... i gave you the answer how to do this several replies ago, did you not see it?

And to answer the other question: if you apply a group expression to a container, then any aggregation type functions (sum, avg, etc.) are done within the scope of only that group.

sluggy

|||I'm using a table, my total returns the Total of the column and not the group. I have not tried a list or a matrix though.|||

icemart525 wrote:

I'm using a table, my total returns the Total of the column and not the group. I have not tried a list or a matrix though.

Okay, for this scenario just put your table inside a list, and apply the group expression to the list. The list (with its table) then gets (automatically) repeated for every group there is, and the table will only "see" data that is in that group. Hope that helps

sluggy

|||okay i'll try your solution later, i'll let you know the results as soon as i get it.

How to get the subtotal of a group?

I am new and I browsed through trying to find the answer but got no
luck. Everyone here says that to get a subtotal of a group just right
click on the group and get the subtotal. I tried so many times but
still can not find out how to do it. Where can i get subtotal of a
column?
thanks a lot.Cheers,
I am assuming you are using a matrix. Right click on the textbox that
you want to subtotal on and there should be an option for it (called
'Subtotal')...click on that and presto...it does it for you. Hope that
helps!
if you are not using a matrix, let me know and i will hopefully give
you correct direction on that or find some tech artical for you.
Thanks and have a great day, Kerrie
cheers...@.hotmail.com wrote:
> I am new and I browsed through trying to find the answer but got no
> luck. Everyone here says that to get a subtotal of a group just right
> click on the group and get the subtotal. I tried so many times but
> still can not find out how to do it. Where can i get subtotal of a
> column?
> thanks a lot.|||Hi Kerrie,
No i am working on a table, there is one column I need to get the
subtotal on. It is not a matrix, do you have any clue on tables?
thanks
Cheers
Kerrie wrote:
> Cheers,
> I am assuming you are using a matrix. Right click on the textbox that
> you want to subtotal on and there should be an option for it (called
> 'Subtotal')...click on that and presto...it does it for you. Hope that
> helps!
> if you are not using a matrix, let me know and i will hopefully give
> you correct direction on that or find some tech artical for you.
> Thanks and have a great day, Kerrie
>
> cheers...@.hotmail.com wrote:
> > I am new and I browsed through trying to find the answer but got no
> > luck. Everyone here says that to get a subtotal of a group just right
> > click on the group and get the subtotal. I tried so many times but
> > still can not find out how to do it. Where can i get subtotal of a
> > column?
> >
> > thanks a lot.|||You can copy that field and put it in the table footer or group footer.
then put sum in front of it. It will look like this. sum(field). Thats
it.
<cheers_ye@.hotmail.com> wrote in message
news:1156792934.941494.202400@.m73g2000cwd.googlegroups.com...
> Hi Kerrie,
> No i am working on a table, there is one column I need to get the
> subtotal on. It is not a matrix, do you have any clue on tables?
> thanks
> Cheers
>
> Kerrie wrote:
>> Cheers,
>> I am assuming you are using a matrix. Right click on the textbox that
>> you want to subtotal on and there should be an option for it (called
>> 'Subtotal')...click on that and presto...it does it for you. Hope that
>> helps!
>> if you are not using a matrix, let me know and i will hopefully give
>> you correct direction on that or find some tech artical for you.
>> Thanks and have a great day, Kerrie
>>
>> cheers...@.hotmail.com wrote:
>> > I am new and I browsed through trying to find the answer but got no
>> > luck. Everyone here says that to get a subtotal of a group just right
>> > click on the group and get the subtotal. I tried so many times but
>> > still can not find out how to do it. Where can i get subtotal of a
>> > column?
>> >
>> > thanks a lot.
>|||On that, you would use your group footer for the subtotals...
ReportHeader....column headings (optional)
Group1Header...data (EX. Departments to your work)
Group2Header...data (EX. People in that dept. from Grp1Hdr)
GroupDetail......data (EX. Dates and Hours that each person worked
from Grp2Hdr)
Group2Footer....data (EX. The SUM(Hours) for that each person worked
from Grp2Hdr)
Group1Footer....data (EX. The Sum(Hours) for that each person worked
from Grp2Hdr in that Department Grp1Hdr)
ReportFooter.....data (EX. The SUM(Hours) for the hole report)
I hope this makes since, if not let me know and I will try to explain
it another way.
Thanks, Kerrie
cheers...@.hotmail.com wrote:
> Hi Kerrie,
> No i am working on a table, there is one column I need to get the
> subtotal on. It is not a matrix, do you have any clue on tables?
> thanks
> Cheers
>
> Kerrie wrote:
> > Cheers,
> >
> > I am assuming you are using a matrix. Right click on the textbox that
> > you want to subtotal on and there should be an option for it (called
> > 'Subtotal')...click on that and presto...it does it for you. Hope that
> > helps!
> >
> > if you are not using a matrix, let me know and i will hopefully give
> > you correct direction on that or find some tech artical for you.
> >
> > Thanks and have a great day, Kerrie
> >
> >
> >
> > cheers...@.hotmail.com wrote:
> > > I am new and I browsed through trying to find the answer but got no
> > > luck. Everyone here says that to get a subtotal of a group just right
> > > click on the group and get the subtotal. I tried so many times but
> > > still can not find out how to do it. Where can i get subtotal of a
> > > column?
> > >
> > > thanks a lot.|||Great, it works!!!!!!!!
so guys, keep in mind, in the future don't use expression like
= sum(field!*****, 'datatset1') for a group subtotal
use this instead --> = sum(field!*****)
hope everyone get it. It is that simple (don't let it waste more of
your time)
Have a good afternoon,
cheers
Ben Watts wrote:
> You can copy that field and put it in the table footer or group footer.
> then put sum in front of it. It will look like this. sum(field). Thats
> it.
> <cheers_ye@.hotmail.com> wrote in message
> news:1156792934.941494.202400@.m73g2000cwd.googlegroups.com...
> > Hi Kerrie,
> >
> > No i am working on a table, there is one column I need to get the
> > subtotal on. It is not a matrix, do you have any clue on tables?
> >
> > thanks
> > Cheers
> >
> >
> > Kerrie wrote:
> >> Cheers,
> >>
> >> I am assuming you are using a matrix. Right click on the textbox that
> >> you want to subtotal on and there should be an option for it (called
> >> 'Subtotal')...click on that and presto...it does it for you. Hope that
> >> helps!
> >>
> >> if you are not using a matrix, let me know and i will hopefully give
> >> you correct direction on that or find some tech artical for you.
> >>
> >> Thanks and have a great day, Kerrie
> >>
> >>
> >>
> >> cheers...@.hotmail.com wrote:
> >> > I am new and I browsed through trying to find the answer but got no
> >> > luck. Everyone here says that to get a subtotal of a group just right
> >> > click on the group and get the subtotal. I tried so many times but
> >> > still can not find out how to do it. Where can i get subtotal of a
> >> > column?
> >> >
> >> > thanks a lot.
> >sql

How to get specific number of rows from each group

I need a SQL query to get 2 items from each catergory.
And if possible, 2 items with price < $100 and 1 item with price >= $100
from each caterogy.
Table: tblItems
Fields: ItemID, CategoryID, ItemName, ItemPrice
A stored procedure with multiple queries also works.
Thanks,
--
GeeviSelect * From Table
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price
Union
Select Top 1 PK From Table
Where Price >- 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||Sorry, Union won't work because you can;t use Order by in parts of a Union..
.
So you have to use separate In Predicate conditions...
Select * From Table
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price)
Or PK In
(Select Top 1 PK From Table
Where Price >= 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||aaaghhh!! Forgot to alias the first Table...
Select * From Table As T -- Left off the "As T" Before
Where PK In
(Select Top 2 PK From Table
Where Price < 100
And Category = T.Category
Order By Price)
Or PK In
(Select Top 1 PK From Table
Where Price >= 100
And Category = T.Category
Order By Price Desc)
"Geevi" wrote:

> I need a SQL query to get 2 items from each catergory.
> And if possible, 2 items with price < $100 and 1 item with price >= $100
> from each caterogy.
> Table: tblItems
> Fields: ItemID, CategoryID, ItemName, ItemPrice
> A stored procedure with multiple queries also works.
> Thanks,
> --
> Geevi
>|||Burying the TOP .. ORDER BY should work:
Select * From Table as T
Where PK In (
select PK from (
Select Top 2 PK From Tbl
Where Price < 100
And Category = T.Category
Order By Price
) PartA
union
select PK from (
Select Top 1 PK From Tbl
Where Price >= 100
And Category = T.Category
Order By Price Desc
) PartB
)
-- I don't know how to get 2 items, of which 2 have prices < $100
-- and one has price >= $100.
Steve Kass
Drew University
CBretana wrote:
> Sorry, Union won't work because you can;t use Order by in parts of a Union
..
> So you have to use separate In Predicate conditions...
>
> Select * From Table
> Where PK In
> (Select Top 2 PK From Table
> Where Price < 100
> And Category = T.Category
> Order By Price)
> Or PK In
> (Select Top 1 PK From Table
> Where Price >= 100
> And Category = T.Category
> Order By Price Desc)
>
> "Geevi" wrote:
>|||Great! It works!
I could not use the Order By "Price" as "ORDER BY items must appear in the
select list if the statement contains a UNION operator."
But this is a big time saver for me.
Thanks CBretana, for the solution!
"CBretana" wrote:
> Select * From Table
> Where PK In
> (Select Top 2 PK From Table
> Where Price < 100
> And Category = T.Category
> Order By Price
> Union
> Select Top 1 PK From Table
> Where Price >- 100
> And Category = T.Category
> Order By Price Desc)
>
>
>
> "Geevi" wrote:
>

How to get permissions on a SQL databse table for a user group defined in AD

Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.

there is one function in SQL : “SELECT * FROM fn_my_permissions('TableName', 'OBJECT')”

This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.

Is tehre any way to acheive that?

-Mani

The best (and probably only) way is to login as a member of the AD Group and run the query on sys.fn_my_permissions.

HTH,

-Steven Gott

SDE/T

SQL Server

How to get permissions on a SQL databse table for a user group defined in AD

Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.

there is one function in SQL :“SELECT*FROM fn_my_permissions('TableName','OBJECT')”

This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.

Is tehre any way to acheive that?

-Mani

The best (and probably only) way is to login as a member of the AD Group and run the query on sys.fn_my_permissions.

HTH,

-Steven Gott

SDE/T

SQL Server

How to get number of pages per group

I am creating an invoice print using Reporting Services and have
managed to figure out how to reset the Page number for each group
change.
Now, I need to figure out how many pages will print for each group
change. The report is rendered for many invoices (each invoice is a
group change) and each invoice may or may not have multiple pages.
I need to hide the page footer until the last page of the invoice.
Any ideas on how to do that?
Thanks!can't you adapt your sql query ? add an extra field (that uses a count by
grouping). And add that count field in your group footer...
just a mind spinning ;)
"Sher" wrote:
> I am creating an invoice print using Reporting Services and have
> managed to figure out how to reset the Page number for each group
> change.
> Now, I need to figure out how many pages will print for each group
> change. The report is rendered for many invoices (each invoice is a
> group change) and each invoice may or may not have multiple pages.
> I need to hide the page footer until the last page of the invoice.
> Any ideas on how to do that?
> Thanks!
>|||can't you adapt your sql query ? add an extra field (that uses a count by
grouping). And add that count field in your group footer...
just a mind spinning ;)
"Sher" wrote:
> I am creating an invoice print using Reporting Services and have
> managed to figure out how to reset the Page number for each group
> change.
> Now, I need to figure out how many pages will print for each group
> change. The report is rendered for many invoices (each invoice is a
> group change) and each invoice may or may not have multiple pages.
> I need to hide the page footer until the last page of the invoice.
> Any ideas on how to do that?
> Thanks!
>

How to get last records in grouped query.

Hi,
I want to create query where I could group records by quarters, and get
the last record in each group.

e.g
Create Table MyTable
(
Value [float],
date[datetime]
)
Insert into MyTable (value, [date]) values (100, '1-1-2000')
Insert into MyTable (value, [date]) values (110, '1-2-2000')
Insert into MyTable (value, [date]) values (120, '1-3-2000')
Insert into MyTable (value, [date]) values (130, '1-4-2000')
Insert into MyTable (value, [date]) values (140, '1-5-2000')
Insert into MyTable (value, [date]) values (150, '1-6-2000')
Insert into MyTable (value, [date]) values (160, '1-7-2000')

Now I would like to get this data grouped by quarter, and get the last
value from each quarter. So here I would like to get result like this
(120, q1 -2000)
(150, q2 -2000)
(160, q3 -2000)
I know how to create aggregate functions but I have problem with getting
that last record from each group.

*** Sent via Developersdex http://www.developersdex.com ***I have solution unless somebody has better one:
Create temp table with dates only, grouped by quarter and do right join
query on MyTable.

Create Table #temp1
(
IdTemp1 int not null Primary key identity ,
[date] datetime
)
Insert into #temp1
(
[date]
)

Select
Max(date)
from MyTable
group by Year([date]), DatePart (quarter,[date])
order by Year([date]), DatePart (quarter,[date])

select * from MyTable right join #temp1 on mytable.date = #temp1.date

*** Sent via Developersdex http://www.developersdex.com ***|||SELECT A.Value, B.Yr, B.Qtr
FROM MyTable as A
JOIN (SELECT Yr = datepart(year, date),
Qtr = datepart(quarter, date),
max(date) as LastDate
FROM MyTable
GROUP BY datepart(year, date),
datepart(quarter, date)) as B
ON A.date = B.LastDate

Roy Harvey
Beacon Falls, CT

On 23 Jan 2007 06:57:11 GMT, schapopa <schapopawrote:

Quote:

Originally Posted by

>Hi,
>I want to create query where I could group records by quarters, and get
>the last record in each group.
>
>e.g
>Create Table MyTable
>(
>Value [float],
>date[datetime]
>)
>Insert into MyTable (value, [date]) values (100, '1-1-2000')
>Insert into MyTable (value, [date]) values (110, '1-2-2000')
>Insert into MyTable (value, [date]) values (120, '1-3-2000')
>Insert into MyTable (value, [date]) values (130, '1-4-2000')
>Insert into MyTable (value, [date]) values (140, '1-5-2000')
>Insert into MyTable (value, [date]) values (150, '1-6-2000')
>Insert into MyTable (value, [date]) values (160, '1-7-2000')
>
>Now I would like to get this data grouped by quarter, and get the last
>value from each quarter. So here I would like to get result like this
>(120, q1 -2000)
>(150, q2 -2000)
>(160, q3 -2000)
>I know how to create aggregate functions but I have problem with getting
>that last record from each group.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

How to get Last record

Hi all
How to get last record
when i group with date i am getting number of records but in those records need to get last record
Is there any function like this
i am working with Crstal reports 8.5
Thanks a lotCreate a formula having this code

if NextIsNull({Field})=True then
{Field}|||Thanks