Help on something simple,i hope.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help on something simple,i hope.

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-13 : 13:09:04

Hi.
What i want to do is get-check the first 1000 rows of a table.So ok i'll use "top" but there is a catch.I want to get the rows according to the date created but i want also to be able to case them asc or desc according to date.So what i think may happen is that if i case rows on desc order then the last rows will be selected and possibly the 1000 rows from the last date.I don't want that.I want rows of desc to be shown but the criteria would always be the first 1000 rows according to the day created.
So if i have rows start with 1 Jan 2010 and last one is 1 March 2010 but the 1000 rows have been created till 1 Feb 2010, when i do desc i want the rows 1000 that will be selected to be till 1 Feb 2010(that will be the first record shown).
There is also another problem.The user can change the dates if he/she likes.This will be on a different column of course.So the 1000 rows will be selected from unchanged dates of a column,lets say it "daycreated" and the asc and desc would be on user changed columns, lets say it "userdate".

I'll try it myself on Monday (on my development PC) but i would like a first tip from you guys.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-13 : 13:12:24

sorry your scenario is not fully clear. can you post some sample data and explain?

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-13 : 16:16:36

Yes i know but i have not created the table yet.
In sort i want to get top 1000 rows from a table according to date added.
If the user sort by date desc i still want the 1000 first rows sorted.Because the user can change date i was thinking of a second column to manage the dates (desc asc), that the user can interact with and my initial column with the original dates(user cannot interact).
So sorting with the 1000 first dates of the column the user cannot interact but asc-desc according to the second column that the user can "play" with.
I want the user even if he change dates to see the 1000 rows that created first, the 1 column can have p.e. a created date of 1/1/10 but the user can change the date on the second column to 2/1/12.If the first column(1/1/10) is on the first 1000 columns created then it will be shown (as 2/1/12).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 03:55:24

sorry still scenario is not clear.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-14 : 20:59:49

If you do a top 1000 asc sort you get the first 1000 lines, right?But if you add date criteria you may get p.e. the first 100 lines then skip to lines between 800 and 900 and then maybe lines from 1100 to 1900.That's the top 1000 rows but with date criteria cuz the rows 1100 to 1900 may have a more recent date than lines let's say 200 to 799.
So i want to get the rows from the initial created date(cannot be changed).The user though can be able to sort THESE PREVIOUS CREATED LINES by her date column that he can change.
I cannot explain it more clearly,sorry :(

Hmmm.Just thought of something..Hmmm.If the created date is straightforward in time(from a server with always correct date-time) then the first 1000 row would actually be the exact same whether you use date as criteria or not.
So a top 1000 will give me the correct result.
The problem is that if i do sort by the rows that the user can change date then probably i won't get 1to1000 rows but may get row after the first 1000.

So if i do SELECT TOP 1000 * from tableX order by userdates Desc (usedates=dates the user can change)
I may get result beyond the first 1000 rows.I'm i correct?And i so, how can i avoid that?
I would have another date column "dateinserted" that will have the initial "cannot be changed" dates.
So in psevdo code.
Select first 1000 rows on dateinsert column from tableX and order Desc according to dateinserted column.Something like that?

Select top 1000 * dateinsert, userdates from tableX order by userdates Desc.Will this work?Sorry i don't have any kind of SQL machine to test it at the present.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 08:08:49

So if i do SELECT TOP 1000 * from tableX order by userdates Desc (usedates=dates the user can change)
I may get result beyond the first 1000 rows.I'm i correct?

Nope you're not
TOP 1000 only returns 1000 rows unless you use WITH TIES option

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-15 : 19:01:02

Ok.I got to the machine, so i think this sould be simple to understand...
So i got this:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [sp_ct]

as 
CREATE TABLE [dbo].[tableff](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[usernames] [nvarchar](50) NULL,
	[insertdate] [datetime] NULL,
	[userdate] [datetime] NULL,
 CONSTRAINT [PK_tableff] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

begin
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('a1',convert(nvarchar,'2/13/2010',113),convert(nvarchar,'3/11/2010',113))
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('b2',convert(nvarchar,'2/13/2010',113),convert(nvarchar,'5/5/2009',113))
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('c3',convert(nvarchar,'2/13/2010',113),convert(nvarchar,'2/13/2010',113)) 
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('d4',convert(nvarchar,'2/14/2010',113),convert(nvarchar,'1/1/2011',113))
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('e5',convert(nvarchar,'2/14/2010',113),convert(nvarchar,'2/14/2010',113))
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('f6',convert(nvarchar,'2/14/2010',113),convert(nvarchar,'2/13/2010',113)) 
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('g7',convert(nvarchar,'2/13/2010',113),convert(nvarchar,'2/13/2010',113))
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('h8',convert(nvarchar,'2/15/2010',113),convert(nvarchar,'1/1/2001',113))
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('i9',convert(nvarchar,'2/15/2010',113),convert(nvarchar,'2/15/2010',113)) 
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('j10',convert(nvarchar,'2/15/2010',113),convert(nvarchar,'2/3/2009',113))
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('k11',convert(nvarchar,'2/11/2010',113),convert(nvarchar,'2/15/2010',113)) 
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('l12',convert(nvarchar,'2/14/2010',113),convert(nvarchar,'2/3/2011',113)) 
INSERT INTO tableff (usernames,insertdate,userdate)
VALUES ('m13',convert(nvarchar,'2/13/2010',113),convert(nvarchar,'2/3/2009',113)) 


end

If i do select top 10 insertdate,usernames from tableff
Then i get the values in the correct order and the first 10 rows.
1st question.I get a1,b2,c3 etc.It's ok but the i would like to know if the is a way to to get top 10 according to insertdate.
P.E. here g7 row (2/13/10) should have been before d4,e5 and f6 (2/14/10)
2nd question.
However if i do: select top 10 insertdate,usernames from tableff order by userdate asc
or:select top 10 insertdate,usernames from tableff order by userdate desc
i get data from rows beyond the first 10.
So what i want is the first 10 rows (according to insertdate)

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 00:56:31

though you've got first 10 rows when you used top 10 it may not always be like that. There's no concept of first or last in sql table so unless you specify an explicit order by means of order by you cant guarantee the order of retrieval, it will be just random 10 rows that'll be returning (which may sometimes be first 10 rows as happened above). so as per your explanation what you want is


select top 10 insertdate,usernames from tableff order by insertdate asc

or

select top 10 insertdate,usernames from tableff order by insertdate desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-16 : 20:28:52

Hi.
This gives me lines beyond the 10 first insertdate rows even in asc.
I don't want that.
I want strictly the first 10 lines of insertdate and then to decide asc or desc according to userdate.
I was thinking of a #temp table but then again if multiple users use the sp will i have an error or "table already exists"?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 23:56:06

quote:
Originally posted by sapator

Hi.
This gives me lines beyond the 10 first insertdate rows even in asc.
I don't want that.
I want strictly the first 10 lines of insertdate and then to decide asc or desc according to userdate.
I was thinking of a #temp table but then again if multiple users use the sp will i have an error or "table already exists"?

as told earlier there's no concept of first or last here. so even if you want first 10 rows right from table you need to have a column to determine order (may be an identity column in your table)
if multiple users use sp then each will be in their own connection and since scope of temporary table is connection it wont cause any problem as each temporary table will be created in its own connection

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-17 : 21:14:24

So something like this might work?

create table #temp
(
	
	[usernames] [nvarchar](50) NULL,
	[insertdate] [datetime] NULL,
	[userdate] [datetime] NULL,
    [usernamex] [nchar](10) NULL)
insert into #Temp
select top 10 usernames,insertdate,userdate,usernamex from tableff
where usernamex ='cobra' order by insertdate asc 

select insertdate,usernames,usernamex,userdate from #temp order by userdate desc
drop table #temp

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-17 : 21:37:06

Or is it better not to create the table?

select top 10 usernames,insertdate,userdate,usernamex 
into #temp
From tableff
where usernamex ='cobra' order by insertdate asc 

select insertdate,usernames,usernamex,userdate from #temp order by userdate desc
drop table #temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 01:01:09

quote:

Originally posted by sapator

So something like this might work?

create table #temp
(
	
	[usernames] [nvarchar](50) NULL,
	[insertdate] [datetime] NULL,
	[userdate] [datetime] NULL,
    [usernamex] [nchar](10) NULL)
insert into #Temp
select top 10 usernames,insertdate,userdate,usernamex from tableff
where usernamex ='cobra' order by insertdate asc 

select insertdate,usernames,usernamex,userdate from #temp order by userdate desc
drop table #temp

this will definitely work
if you dont want to explicitly create a table for this use


select insertdate,usernames,usernamex,userdate
from
(
select top 10 usernames,insertdate,userdate,usernamex from tableff
where usernamex ='cobra' order by insertdate asc 
)t
order by userdate desc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-18 : 12:11:18

Hi.
Didn't know this technique you post.
So it's like you create a table inside the ( ) clauses and declare it as t , am i correct?
Very useful, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 12:13:08

quote:
Originally posted by sapator

Hi.
Didn't know this technique you post.
So it's like you create a table inside the ( ) clauses and declare it as t , am i correct?
Very useful, thanks!

yeah...exactly
its called derived table as you're deriving it on the fly from the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-18 : 19:20:28

Ok.Thanks for the help.
I'm probably on 90% knowledge on .net (forms) but ASP is giving me a huge problem with sqldatasource so i'm trying to go SQL all the way :) And a lot faster if you work on web sites.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 00:20:37

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-02-19 : 00:40:20

visakh16 posted a solution without explanation ....

Hi sapator, the main thing is about TOP.

TOP based on columnName specified on the ORDER BY. SQLSERVER uses the columnName to logically select TOP, then using the same columnName to sort the result (display the result in order). So we can NOT select TOP based on one column then display the result in order based on another column.

Solution is using derived tables to select the TOP, then sort the result by outside ORDER BY clause as visakh16 did. I think you can use VIEW, CTE or ITVF too. But perhaps, 1 solution is enough ...

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2010-02-19 : 04:15:04

thank you too

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources