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 relation(Need Sql Query)?

Hi friends

I have one Table called tblCategory.

I have three Column CatID, CatName, ParentID

I have many records in this Table

CatID CatName ParentID

1 Cat1 0

2 Cat2 1

3 Cat3 1

4 Cat4 2

5 Cat5 2

6 Cat6 0

7 Cat7 6

8 Cat8 6

9 Cat9 7

10 Cat10 7

11 Cat11 8

Here I have Main Category which has ParentID 0 [ Cat1 and Cat6 ]

I Have Sub categories of Cat1 Which has ParentID 1(CatID 1 of Cat1) [ Cat2 and Cat 3 ]

Cat 2 has also sub category with ParentID 2 (CatID 2 of Cat2) [ Cat4 and Cat5]

I want result looks like as

Cat1 Cat6 ->>>>>ParentID 0
- Cat2 -Cat7 ->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
- - Cat4 - -Cat9 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- - Cat5 - -Cat10 ->>>>>PaerntID 2(CatID of Root Cat2) and ParentID 7(CatID of Root Cat7)
- Cat3 -Cat8 -->>>>>ParentID 1(CatID of Root Cat1) and ParentID 6(CatID of Root Cat6)
--No record --Cat11

Can anybody give me solution?

Thanks

See if this article on working out JOIN syntax will help:http://www.mikesdotnetting.com/Article.aspx?ArticleID=72

|||

There are two tables but in my case I have only one table.

|||

impathan:

There are two tables but in my case I have only one table.

So you have. Sorry, I saw the word Relation in the title of your post and thought you were after somerthing else.

Is what you are after something like the threaded view of a discussion board or similar? If so, one solution might be a recursive function. If not, could you explain the logic beind the diagram you have supplied? I can't, for example see the basis on which Cat3 and Cat8 on the penultimate line are related.

|||

impathan:

There are two tables but in my case I have only one table.

You can query a table twice in the same statement and join it to itself. :) You just need to give each usage of the table a different table alias.

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 total time of the records in a table

Hello,

I have one table that has a column called CallDuration. This columns has always the format "1/01/2000 12:01:38 AM". The date part "1/01/2000" I want to discard, and sum the time part to get a total time in my query. How can I do that?

Thxselect datediff(hour,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') Hours,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') % 60 Minutes,
datediff(minute,'1/01/2000 12:01:38 AM', '1/01/2000 11:10:38 AM') TotalMinutes|||select datediff(hour,min(callduration), max(callduration)) Hours,
datediff(minute,min(callduration), max(callduration)) % 60 Minutes,
datediff(minute,min(callduration), max(callduration)) TotalMinutes

How to get the Row Number per Distinct Records?

Row Number	Name	Phone Number
1	John Doe	(555) 123-1221
1	John Doe	(555) 144-9989
2	Smith	(666) 191-1201
3	Jane Doe	(555) 188-0191
3	Jane Doe	(555) 189-0192
3	Jane Doe	(555) 190-0193

Here are the records I get back using a Grouping on "Name". I would like to assign a Row Number for each "Distinct" row. I've tried all the possible aggregate functions with no luck! Can anybody help me with this? Thanks.

Please try something like this:

=RunningValue(Fields!Name.Value & Fields!PhoneNumber.Value ,CountDistinct, Nothing)

|||Thanks. I didn't know that Expression can take multiple values!!! Now I know!

how to get the max value from a table join

Hi all,

I have 2 table with one having customer info like personal details. The other table has sales records of each customer. I need join these 2 tables and retrive only the latest sales record per customer. How could I do this. Any help appriciated.

Many thanks,

Cheers,

Vije

You really need to describe your problem better. Information that ought to be included is (1) which version of SQL server you are using -- whether 2000 or 2005 or what, (2) the names of the tables, and (3) the names of the relevant columns.

One potential is something like:

Code Snippet

select column1,

column2,

...

columnN

from salesTable s

join customerTable c

on s.customerKey = c.customerKey

where s.salesDate

= ( select max(s.salesDate

from salesTable t

where t.customerKey = s.customerKey

)

This probably isn't the best guess, but without a better description of the target it is hard to hit the middle of the target.

|||

Here the sample,

Code Snippet

Create Table #salesdetails (

[CustomerId] int ,

[Date] DateTime ,

[Product] int ,

[qty] int

);

Insert Into #salesdetails Values('1','1/1/2007','1','10');

Insert Into #salesdetails Values('1','2/1/2007','2','10');

Insert Into #salesdetails Values('2','2/1/2007','2','10');

Insert Into #salesdetails Values('3','2/1/2007','3','10');

Insert Into #salesdetails Values('2','4/1/2007','2','10');

Insert Into #salesdetails Values('3','5/1/2007','3','10');

Insert Into #salesdetails Values('4','2/1/2007','2','10');

Create Table #customer (

[CustomerId] int ,

[Name] Varchar(100)

);

Insert Into #customer Values('1','Hari');

Insert Into #customer Values('2','Mani');

Insert Into #customer Values('3','Viji');

Insert Into #customer Values('4','Shiv');

Code Snippet

Select det.*,Cust.[Name] From #salesdetails det

Join (Select [CustomerId],Max([Date]) LastestDatefrom #salesdetails Group By [CustomerId]) as Latest

On Latest.[CustomerId]= Det.[CustomerId] and Latest.LastestDate = Det.[Date]

Join #customer Cust

On Cust.[CustomerId] = det.[CustomerId]

How to get the last occurence of rows containing disticnt value in one column

I have a table that tracks GPS records broadly speaking as follows

tsDATETIME
usernameVARCHAR(16)
...
GPS data
...

I want to select the most recent GPS data for each distinct user.
That is, the table will have may records for a given username, but I
only want the most recent for each one.

For a single user I know I can do

SELECT TOP 1 * from <table> order by ts desc

But I want a set of results that effectively does this for
all users, and I can't work out the necessary query/subquery I
should be using.

I'm sure I'm missing something fairly obvious, so usual newbie
disclaimers apply.

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/What is the primary key? I'll assume the key consists of (ts,
username), in which case the folllowing should do what you want:

SELECT ts, username, ... /* other columns */
FROM YourTable AS T
WHERE ts =
(SELECT MAX(ts)
FROM YourTable
WHERE username = T.username)

It really helps if you include DDL with questions like this (basically
a CREATE TABLE statement, including keys and constraints). The exact
table structure may make a big difference to the possible solutions.
The usual recommendation that you shouldn't use SELECT * in production
code also applies.

--
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Even pseudo-DDL is better than narratives. Is this
what you meant?

CREATE TABLE Foobar
(event_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
user_name VARCHAR(16) NOT NULL,
stuff_1 INTEGER NOT NULL,
stuff_2 INTEGER NOT NULL,
..
stuff_n INTEGER NOT NULL,
PRIMARY KEY (user_name, event_time));

SELECT F1.*
FROM Foobar AS F1
WHERE F1.event_time
= (SELECT MAX(f2.event_time)
FROM Foobar AS F2
WHERE F1.user_name = F2.user_name);
-- use column names in production code, not SELECT *.|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>What is the primary key? I'll assume the key consists of (ts,
>username), in which case the folllowing should do what you want:
>SELECT ts, username, ... /* other columns */
> FROM YourTable AS T
> WHERE ts =
> (SELECT MAX(ts)
> FROM YourTable
> WHERE username = T.username)

Thanks

>It really helps if you include DDL with questions like this (basically
>a CREATE TABLE statement, including keys and constraints). The exact
>table structure may make a big difference to the possible solutions.

Point noted.

>The usual recommendation that you shouldn't use SELECT * in production
>code also applies.

I realise this. That was just me being lazy (although for some of my
selects I do want the entire row).

--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||"--CELKO--" <jcelko212@.earthlink.net> wrote:

>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in your
>schema are. Sample data is also a good idea, along with clear
>specifications. Even pseudo-DDL is better than narratives. Is this
>what you meant?

Sorry. Will do in future.

Thanks for the solution.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/sql

how to get the duplicate file in ssis

is it possible that i can retrieve the list of all records that are duplicate and put them in a excel files

Hi There,

Use this query to get the duplicate records from a table, if you are using flat file as a source, load it to the database table and use this query:

select coulmn a from <tablename> where column a in (select column a from <tablename> group by column a having count(*) > 1). This query will give you the list of records which is duplicate. Later you can add an excel destination to load these duplicate records.

Regards,

Raju

|||

Hi

thanks for the reply, It work nice, somehow it disappoint me a bit (not on your answer), cause i thought i won't use the query anymore to disect the data and I thought ssis has tool for this. Anyway thanks!!!

Jagapathi Raju wrote:

Hi There,

Use this query to get the duplicate records from a table, if you are using flat file as a source, load it to the database table and use this query:

select coulmn a from <tablename> where column a in (select column a from <tablename> group by column a having count(*) > 1). This query will give you the list of records which is duplicate. Later you can add an excel destination to load these duplicate records.

Regards,

Raju

how to get the date 2years before

Hi
Can anyone please help me to get the date that is 2 years before .
So that i can pull put the records tht are 2 years older...

i want to use it in a query ...

Thanks in advanceUse this select dateadd(yy, -2, getdate())

or in quesry

where date < dateadd(yy, -2, getdate())

Good Luck.sql

How to get the count of source records doing UPDATE with joined tables.

I have an sql like this in my stored procedure.
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
So, COL1 of T1 table is modified when T1 finds matching records in T2
table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
there are multiple matching records from T2. I want to make T1 table
be updated when there is exactly one matching record in T2.
Of course I can check the count of the matching records in T2 before
doing the above but T2 is actually from openquery interface(so dynamic
sql) to a remote server and I don't know exactly how I can get a
cursor with a dynamic sql.(I guess I should search this soon.)
If there is a way to find out the count of matching source records for
the above sql, it will help me a lot. Thanks..I think this should work
UPDATE T1
SET COL1 = T2.COL1
FROM T1, T2
WHERE T1.COL2=T2.COL2
And (Select Count(*) From T2 Where T2.COL2=T1.COL2)=1
Dmitriy
"Yi, Dong-ryon" <feeva@.hanmail.net> wrote in message
news:bf8bb96c.0503131958.770f80ea@.posting.google.com...
>I have an sql like this in my stored procedure.
> UPDATE T1
> SET COL1 = T2.COL1
> FROM T1, T2
> WHERE T1.COL2=T2.COL2
> So, COL1 of T1 table is modified when T1 finds matching records in T2
> table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
> there are multiple matching records from T2. I want to make T1 table
> be updated when there is exactly one matching record in T2.
> Of course I can check the count of the matching records in T2 before
> doing the above but T2 is actually from openquery interface(so dynamic
> sql) to a remote server and I don't know exactly how I can get a
> cursor with a dynamic sql.(I guess I should search this soon.)
> If there is a way to find out the count of matching source records for
> the above sql, it will help me a lot. Thanks..|||On 13 Mar 2005 19:58:20 -0800, Yi, Dong-ryon wrote:

>I have an sql like this in my stored procedure.
>UPDATE T1
>SET COL1 = T2.COL1
>FROM T1, T2
>WHERE T1.COL2=T2.COL2
>So, COL1 of T1 table is modified when T1 finds matching records in T2
>table with T1.COL2=T2.COL2. The problem is T1.COL1 is updated when
>there are multiple matching records from T2. I want to make T1 table
>be updated when there is exactly one matching record in T2.
>Of course I can check the count of the matching records in T2 before
>doing the above but T2 is actually from openquery interface(so dynamic
>sql) to a remote server and I don't know exactly how I can get a
>cursor with a dynamic sql.(I guess I should search this soon.)
>If there is a way to find out the count of matching source records for
>the above sql, it will help me a lot. Thanks..
Hi Yi,
As an alternative to the suggestion made by Dmitriy, here's a version
that refers to the T2 table in only one place:
UPDATE T1
SET Col1 = T2a.Col1
FROM T1
INNER JOIN (SELECT Col2, MIN(Col1) AS Col1
FROM T2
GROUP BY Col2
HAVING COUNT(*) = 1) AS T2a
ON T2a.Col2 = T1.Col2
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to Get the 2nd the 2nd Record AND DISPLAY IN SINGLE ROW ?

Can you please assist me on how to get the 2nd record in case there are

3 or more records of an employee, the query below gets the MAX and MIN
BasicSalary. However, my MIN Basic Salary is wrong because I should get

the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
more records and not the last Basic Salary of the Last Record.

How to GET the 2nd Row of Record in Case that There are 3 or more
records IN A SINGLE ROW ?

-----------------------*--

This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.

Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...

SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,

(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate DESC) AS NEWBASIC,

******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ?

(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate ASC) AS OLDBASIC

FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @.FromDate AND
@.ToDate
ORDER BY E.LastNameheri (heri.carandang@.acspacific.com) writes:
> Can you please assist me on how to get the 2nd record in case there are
> 3 or more records of an employee, the query below gets the MAX and MIN
> BasicSalary. However, my MIN Basic Salary is wrong because I should get
> the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
> more records and not the last Basic Salary of the Last Record.
>
> How to GET the 2nd Row of Record in Case that There are 3 or more
> records IN A SINGLE ROW ?

SELECT TOP 1 val
FROM (SELECT TOP 2 val
FROM tbl
ORDER BY val DESC) AS x
ORDER BY val ASC

Gives you the second highest value of val.

If you want to do this for a set values, this may be more practical:

SELECT s.empid, secondest = MAX(s.salary)
FROM salaries s
JOIN (SELECT empid, maxsalary = MAX(salary)
FROM salaries
GROUP BY empid) AS m ON s.empid = m.empid
WHERE s.salary < m.maxsalary

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||or

Select min(col) from
(
select top N col from table Order by col DESC
) T

Madhivanan

How to get table records position in comparison to other records based on numeric field?

Hi,

Let's say I have 1000 registered users in database table and each of them has numeric ranking value.

How can I get the position of each user in comparison to other users ranking value?

If you're using SQL Server 2005, you can use the row_number() function. Are you?

Don

|||Hi,|||

Hi,

Yes, I am. Can you post some example code?

|||maybe you can post your table DDL, some sample data and the result that you want ?|||

Hi,

Solution found! Here's the code:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>"

SelectCommand="SELECT [usernickname], ROW_NUMBER() OVER(ORDER BY userrankavg DESC) AS 'position' FROM [dbusers]"
</asp:SqlDataSource>

|||

Hi,

That doesn't actually solve my problem.

The code does display position for each record when all records are selected from table, but doesn't when WHERE attribute is specified.

How can I get position for a specific record that I specify with WHERE attribute?

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
Ashish
You can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish
|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:

> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

how to get sql output in an XML file?

Hello
i have one query
In Query Analyzer i executeed sql query like "Select * from tmp"
this retrives 10 records now i want to save these 10 record into XML file on
my specified location
Is it possible in single SQL command to save retrived data into XML file at
specified location
Regards
AshishYou can save the output of a query in QA using the
Query--> Results to file option.
You can get the output of a query in XML using the FOR XML clause
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ashish Deshpande" <Ashish Deshpande@.discussions.microsoft.com> wrote in
message news:5CCE3835-43D8-4901-85D6-D09976510AF5@.microsoft.com...
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file
> on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file
> at
> specified location
> Regards
> Ashish|||Hi
You can create XML with the FOR XML option in SELECT statements. If you want
to save the data to a file, look at DTS as SQL Server does not do user file
handling itself.
Regards
Mike
"Ashish Deshpande" wrote:
> Hello
> i have one query
> In Query Analyzer i executeed sql query like "Select * from tmp"
> this retrives 10 records now i want to save these 10 record into XML file on
> my specified location
> Is it possible in single SQL command to save retrived data into XML file at
> specified location
> Regards
> Ashish

How to get rows user defined range?

Hello,
I need a keywork like LIMIT(in oracle) which let me to get rows, I defined.
For exaple I want to show records from 100. record to 200. record!
In oracle I could do like this
Select * from Customer limit 100, 100
I could do a complex query so it let me to get what I want, but I don't
think it has a performance.
Is there a key word like LIMIT in Sql Server?http://www.aspfaq.com/show.asp?id=2120
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
> defined. For exaple I want to show records from 100. record to 200.
> record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||Generate a quota query with a ranking value. You can do all sorts of range
related tricks with such a value. Search the archives of this newsgroup for
some examples.
Anith|||Is LIMIT X,Y part of ANSI-SQL ?
I had a look, but I could only find it as a keyword, not actually defined
what it was for, syntax etc.
"s" <ss@.hotmail.com> wrote in message
news:OUYvbqM0FHA.3408@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I need a keywork like LIMIT(in oracle) which let me to get rows, I
defined.
> For exaple I want to show records from 100. record to 200. record!
> In oracle I could do like this
> Select * from Customer limit 100, 100
> I could do a complex query so it let me to get what I want, but I don't
> think it has a performance.
> Is there a key word like LIMIT in Sql Server?
>|||>> Is LIMIT X,Y part of ANSI-SQL ?
No, I think it is a MySQL dialect. Most prominent SQL variants have some
syntax that partially support quota queries.
Anith

how to get rid of old records

I have a patient reviewing database
Master:
ID, Name, Address, etc.
Detail:
ID, Interviewing date, symptom, etc.

And I collect info monthly.
When I want to count how many patients have suffered from the symptom and classify them by period, which I only need the latest info, lots of old records of the same ID emerges. I am new in TSQL and have trouble in retrieving the up-to-date info. PL Help me!
Thank u.

Try this one:

declare @.symtom nvarchar(100)
set @.symtom = 'headache'

select
count(distinct ID) as count,
year([Interviewing date]) as year,
month([Interviewing date]) as month
from Detail
where symptom = @.symtom
group by year([Interviewing date]), month([Interviewing date])

How to get records (In this week) only?

Hi,

How to get records from MSSQL database which only recorded from the biging of this week?

in example my week begin from Sunday..

You might want to be more specific. Is this for a single table or for all tables? Does/do your table/s have timestamp columns?|||

Only for one table, and yes the table have timestamp column.

I'm also trying to use this query to get the number of records inserted on this month:

SELECT COUNT(*)

FROM MyTable

WHERE DateTimeColumn > MONTH(1)

It doesnt return the correct value for records inserted on Feb!!

|||

You're comparing against a constant - MONTH(1)

Try this instead:

WHERE MONTH(DateTimeColumn) > 1

Note, though, that this may not be optimal on large tables, since the function wrapped around DateTimeColumn will likely prevent an index to be used, if there is any on that column..

/Kenneth

|||

Thanks KeWin, this format is working perfectly..

Whats the optimal solution for Larg Tables?

And what about (In this week)? how can I get it?

|||Hi,

SELECT*FROM

SomeTable

WHEREDATENAME(wk,SomeDateColumn)=DATENAME(wk,GETDATE())AND

YEAR(SomeDateColumn)=YEAR(GETDATE())

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens K. Suessmeyer,

it worked :)

|||

Weeknumbers are probably the trickiest part of a date.

While this works well for US-style weeknumbers, just be aware that it doesn't work as well for those of us who use the ISO week numbering scheme. That is, weeknumbers are dependant on which country's calendar it's applyed against.

/Kenneth

How to get records (In this week) only?

Hi,

How to get records from MSSQL database which only recorded from the biging of this week?

in example my week begin from Sunday..

You might want to be more specific. Is this for a single table or for all tables? Does/do your table/s have timestamp columns?|||

Only for one table, and yes the table have timestamp column.

I'm also trying to use this query to get the number of records inserted on this month:

SELECT COUNT(*)

FROM MyTable

WHERE DateTimeColumn > MONTH(1)

It doesnt return the correct value for records inserted on Feb!!

|||

You're comparing against a constant - MONTH(1)

Try this instead:

WHERE MONTH(DateTimeColumn) > 1

Note, though, that this may not be optimal on large tables, since the function wrapped around DateTimeColumn will likely prevent an index to be used, if there is any on that column..

/Kenneth

|||

Thanks KeWin, this format is working perfectly..

Whats the optimal solution for Larg Tables?

And what about (In this week)? how can I get it?

|||Hi,

SELECT*FROM

SomeTable

WHEREDATENAME(wk,SomeDateColumn)=DATENAME(wk,GETDATE())AND

YEAR(SomeDateColumn)=YEAR(GETDATE())

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens K. Suessmeyer,

it worked :)

|||

Weeknumbers are probably the trickiest part of a date.

While this works well for US-style weeknumbers, just be aware that it doesn't work as well for those of us who use the ISO week numbering scheme. That is, weeknumbers are dependant on which country's calendar it's applyed against.

/Kenneth

How to get records (In this week) only?

Hi,

How to get records from MSSQL database which only recorded from the biging of this week?

in example my week begin from Sunday..

You might want to be more specific. Is this for a single table or for all tables? Does/do your table/s have timestamp columns?|||

Only for one table, and yes the table have timestamp column.

I'm also trying to use this query to get the number of records inserted on this month:

SELECT COUNT(*)

FROM MyTable

WHERE DateTimeColumn > MONTH(1)

It doesnt return the correct value for records inserted on Feb!!

|||

You're comparing against a constant - MONTH(1)

Try this instead:

WHERE MONTH(DateTimeColumn) > 1

Note, though, that this may not be optimal on large tables, since the function wrapped around DateTimeColumn will likely prevent an index to be used, if there is any on that column..

/Kenneth

|||

Thanks KeWin, this format is working perfectly..

Whats the optimal solution for Larg Tables?

And what about (In this week)? how can I get it?

|||Hi,

SELECT * FROM

SomeTable

WHERE DATENAME(wk,SomeDateColumn) = DATENAME(wk,GETDATE()) AND

YEAR(SomeDateColumn) = YEAR(GETDATE())

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens K. Suessmeyer,

it worked :)

|||

Weeknumbers are probably the trickiest part of a date.

While this works well for US-style weeknumbers, just be aware that it doesn't work as well for those of us who use the ISO week numbering scheme. That is, weeknumbers are dependant on which country's calendar it's applyed against.

/Kenneth