select query without first records...

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)

select query without first records...

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-03-26 : 11:10:34

Hi,

I want query which should not display first records BY CreatedDate

it means i want to query which must display all records without last inserted records.

Regards,
ASIF

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-26 : 11:17:28

Table(s) Structure(s0?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 11:27:01

[code]
CREATE TABLE #tmp (ID INT, CreatedDate DATETIME)
GO

INSERT INTO #tmp
SELECT 1, '2000-01-01'
UNION ALL SELECT 2, '2000-01-02'
UNION ALL SELECT 3, '2000-01-03'
UNION ALL SELECT 4, '2000-01-04'
UNION ALL SELECT 5, '2000-01-05'
UNION ALL SELECT 6, '2000-01-06'
UNION ALL SELECT 7, '2000-01-07'
GO

SELECT ID, CreatedDate
FROM #tmp
WHERE CreatedDate != (SELECT MAX(CreatedDate) FROM #tmp)
GO

DROP TABLE #tmp
GO
[/code]

Although, it might be better to do this:

[code]
DECLARE @MaxDate DATETIME

SELECT @MaxDate = MAX(CreatedDate) FROM #tmp

SELECT ID, CreatedDate
FROM #tmp
WHERE CreatedDate != @MaxDate
[/code]

Using the second method, you guarantee that the MAX(CreatedDate) is only executed once.

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 09:12:15

[code]
SELECT ID, CreatedDate
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY CreatedDate DESC) AS Seq,ID, CreatedDate
FROM #Tmp
)t
WHERE Seq>1
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 09:13:19

And if you've multiple records with max date value (batch inserts) and want to exclude all of them ,replace ROW_NUMBER by DENSE_RANK in above query

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources