Select first 10 rows of table
-
Dear all,
I have the following table:
field name (of varchar(10))
field date (of datetime, default getdate())
Each value of name can come up more than once in the table.
I am looking for a tsql statement to give me the following result:
the first 10 rows of a table, for each of the names in the table.
I hope I have explained it well enough.
regards,
Hans
-
Actually there is no such thing as "the first 10 rows". If I understand you right, you have two alternatives:
USE PUBS
GO
SET ROWCOUNT 10
SELECT au_lname FROM authors ORDER BY au_lname
SET ROWCOUNT 0
--or
SELECT TOP 10 au_lname FROM authors ORDER BY au_lname
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thank you for your answer Frank. Indeed what I ment was the top 10 of the table, but then per name.
For the following example not to take up too much space, I limit the amount to 2 rows per name.
Table1 consists of the foillowing data:
name date
Arley 02-3-2004
Arley 10-3-2004
Arley 12-3-2004
Arley 01-3-2004
Ben 10-3-2004
Ben 17-3-2004
Ben 03-3-2004
I need a query that gives the following result:
name date
Arley 12-3-2004
Arley 10-3-2004
Ben 17-3-2004
Ben 10-3-2004
So for each name present the query should show the top 2 rows (ordered by date desc)
I hope this examples clarifies the question a bit.
regards,
Hans
-
Okay, what about:
set nocount on
create table hans
(
name varchar(10)
, date datetime default getdate()
)
insert into hans (name, date) values('Arley','20040302')
insert into hans (name, date) values('Arley','20040310')
insert into hans (name, date) values('Arley','20040312')
insert into hans (name, date) values('Arley','20040301')
insert into hans (name, date) values('Ben','20040310')
insert into hans (name, date) values('Ben','20040317')
select
t1.name
, t1.date
from
hans t1
where
t1.date in
(
select top 2 with ties
t2.date
from
hans t2
where
t2.name = t1.name
order by
t2.date desc
)
order by
t1.name
, t1.date desc
drop table hans
set nocount off
name date
---------- ------------------------------------------------------
Arley 2004-03-12 00:00:00.000
Arley 2004-03-10 00:00:00.000
Ben 2004-03-17 00:00:00.000
Ben 2004-03-10 00:00:00.000
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Oh, and may I add that you shouldn't use use words as name and date as identifiers for column?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes, I will check on this.
Frank, how would the query be if I have more than 2 different names? If the number of different names is dynamic, can I still use the construction that you showed me?
regards,
Hans
-
No need to worry!
Have a play with it and you'll see
set nocount on
create table hans
(
name varchar(10)
, date datetime default getdate()
)
insert into hans (name, date) values('Arley','20040302')
insert into hans (name, date) values('Arley','20040310')
insert into hans (name, date) values('Arley','20040312')
insert into hans (name, date) values('Arley','20040301')
insert into hans (name, date) values('Frank','20040310')
insert into hans (name, date) values('Ben','20040317')
select
t1.name
, t1.date
from
hans t1
where
t1.date in
(
select top 2 with ties
t2.date
from
hans t2
where
t2.name = t1.name
order by
t2.date desc
)
order by
t1.name
, t1.date desc
drop table hans
set nocount off
name date
---------- ------------------------------------------------------
Arley 2004-03-12 00:00:00.000
Arley 2004-03-10 00:00:00.000
Ben 2004-03-17 00:00:00.000
Frank 2004-03-10 00:00:00.000
This should work on as many distinct names as you have in your table.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
how does the "with ties" work in this situation? I've yet to ever find a need for that feature, but I am wondering if perhaps I've just found more difficult ways of acheiving the same solution.
Cheers,
Quentin
-
The WITH TIES is not really needed in this situation, I think, because of the combination of ...IN... TOP 2 ... WITH TIES. If you have two or more rows with the same date and this date falls within the TOP 2 it will be handled by the IN condition anyway. Here's a maybe better example of what WITH TIES does:
SET NOCOUNT ON
DECLARE @TTT TABLE
(
THE_ID INT NOT NULL
, Name varchar(50)
)
INSERT INTO @TTT VALUES(1,'A');
INSERT INTO @TTT VALUES(2,'BB');
INSERT INTO @TTT VALUES(2,'B');
INSERT INTO @TTT VALUES(3,'C');
INSERT INTO @TTT VALUES(3,'D');
INSERT INTO @TTT VALUES(3,'DD');
INSERT INTO @TTT VALUES(3,'DDD');
INSERT INTO @TTT VALUES(3,'DDDD');
INSERT INTO @TTT VALUES(4,'E');
INSERT INTO @TTT VALUES(5,'F');
INSERT INTO @TTT VALUES(6,'G');
SET NOCOUNT OFF
SELECT TOP 4
THE_ID
FROM
@TTT
ORDER BY
1;
SELECT TOP 4 WITH TIES
THE_ID
FROM
@TTT
ORDER BY
1;
SELECT DISTINCT TOP 4
THE_ID
FROM
@TTT
ORDER BY
1;
Got this from a posting here by Len Esterhuyse.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
But what if the dates are so randomly distributed that no date is the same?
Anyway, this concept worked like a charm for the statement I was looking for.
May I thank you very much Frank
high regards,
Hans
-
>But what if the dates are so randomly distributed that no date is the same?
In this case you'll find that you get two rows per name as requested.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
But only if I use 'WITH TIES'?!
The other thing is that TOP 2 could also be TOP 5, or TOP 20 (I want to fill in a number for this, to make the query dynamic).
Hans
-
But only if I use 'WITH TIES'?!
No, sorry if I confused you. Forget the WITH TIES in your case. It is redundant.
As for making it dynamic:
See http://www.sommarskog.se/dynamic_sql.html and http://www.sommarskog.se/dyn-search.html for very detailed informations and examples on how to accomplish this.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
--Generate some data
--Generate some data
SELECT d.*
,IDENTITY (INT, 1,1) AS Row
INTO #Tmp
FROM (
SELECT CONVERT(VARCHAR(10),'A') AS Name,CONVERT(DATETIME,DATEADD(d,1,GETDATE())) AS TheDate UNION ALL
SELECT 'A',DATEADD(d,2,GETDATE()) UNION ALL
SELECT 'A',DATEADD(d,2,GETDATE()) UNION ALL
SELECT 'A',DATEADD(d,4,GETDATE()) UNION ALL
--SELECT 'A',DATEADD(d,5,GETDATE()) UNION ALL
SELECT 'B',DATEADD(d,1,GETDATE()) UNION ALL
--SELECT 'B',DATEADD(d,2,GETDATE()) UNION ALL
--SELECT 'B',DATEADD(d,3,GETDATE()) UNION ALL
--SELECT 'B',DATEADD(d,4,GETDATE()) UNION ALL
--SELECT 'B',DATEADD(d,5,GETDATE()) UNION ALL
SELECT 'C',DATEADD(d,1,GETDATE()) UNION ALL
--SELECT 'C',DATEADD(d,2,GETDATE()) UNION ALL
--SELECT 'C',DATEADD(d,3,GETDATE()) UNION ALL
SELECT 'C',DATEADD(d,4,GETDATE()) UNION ALL
SELECT 'C',DATEADD(d,5,GETDATE())
) AS d
ORDER BY Name,TheDate
--If many rows
CREATE INDEX INDXTmp ON #Tmp(Name,Row)
--Get first three rows per name
SELECT t.Name,t.TheDate
FROM #Tmp t
WHERE t.ROW BETWEEN (SELECT MIN(tt.Row) FROM #Tmp tt WHERE tt.Name=t.Name)
AND (SELECT MIN(ttt.Row) FROM #Tmp ttt WHERE ttt.Name=t.Name)+2
ORDER BY t.Name,t.TheDate
DROP TABLE #Tmp
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply