September 8, 2015 at 1:48 pm
I have a requirement
We have a table Test
ServerName DatabaseName
D900 Test1
D200 Test1
D900 Test2
I need to write a query in such as way that the result set should not contain the first row values i.e., servername D900 and databasename Test1
How can I write my select query?
Please let me know
Thanks.
September 8, 2015 at 1:56 pm
Either you can write your query like this:
SELECT *
FROM Test
WHERE NOT( ServerName = 'D900'
AND DatabaseName = 'Test1')
Or you could tell us how do you define the first row. Remember that rows in a table have no particular order.
September 8, 2015 at 2:06 pm
I have so many NOT IN conditions like ::
Select * from backups
where
databasename not in (select databasename from backups where servername ='d900' and databasename like '%uat%')
AND databasename not in (select databasename from backup where servername ='d200' and databasename like '%uat%')
for the above asked question if I write
And databasename NOT IN (select databasename from backup where servername ='d900' and databasename ='test1')
It would take into consideration the D200 server too
I want the results of D200 server test1 database
I just want to skip d900 server test1 database.
September 8, 2015 at 2:20 pm
SQLAddict01 (9/8/2015)
I have so many NOT IN conditions like ::Select * from backups
where
databasename not in (select databasename from backups where servername ='d900' and databasename like '%uat%')
AND databasename not in (select databasename from backup where servername ='d200' and databasename like '%uat%')
for the above asked question if I write
And databasename NOT IN (select databasename from backup where servername ='d900' and databasename ='test1')
It would take into consideration the D200 server too
I want the results of D200 server test1 database
I just want to skip d900 server test1 database.
Is this what you are looking for?
-- Create Table
DECLARE @test-2 AS TABLE (ServerName varchar(50), DatabaseName varchar(50));
-- Insert Records
INSERT INTO @test-2 (ServerName, DatabaseName) VALUES ('D900','Test1'),('D200','Test1'),('D900','Test2');
--Verify Data
SELECT * FROM @test-2
--Return Everything but the first recored
SELECT *
FROM @test-2
WHERE ServerName + DatabaseName <> (SELECT TOP 1 ServerName + DatabaseName FROM @test-2)
(3 row(s) affected)
ServerName DatabaseName
-------------------------------------------------- --------------------------------------------------
D900 Test1
D200 Test1
D900 Test2
(3 row(s) affected)
ServerName DatabaseName
-------------------------------------------------- --------------------------------------------------
D200 Test1
D900 Test2
(2 row(s) affected)
September 8, 2015 at 3:27 pm
eccentricDBA (9/8/2015)
SQLAddict01 (9/8/2015)
I have so many NOT IN conditions like ::Select * from backups
where
databasename not in (select databasename from backups where servername ='d900' and databasename like '%uat%')
AND databasename not in (select databasename from backup where servername ='d200' and databasename like '%uat%')
for the above asked question if I write
And databasename NOT IN (select databasename from backup where servername ='d900' and databasename ='test1')
It would take into consideration the D200 server too
I want the results of D200 server test1 database
I just want to skip d900 server test1 database.
Is this what you are looking for?
-- Create Table
DECLARE @test-2 AS TABLE (ServerName varchar(50), DatabaseName varchar(50));
-- Insert Records
INSERT INTO @test-2 (ServerName, DatabaseName) VALUES ('D900','Test1'),('D200','Test1'),('D900','Test2');
--Verify Data
SELECT * FROM @test-2
--Return Everything but the first recored
SELECT *
FROM @test-2
WHERE ServerName + DatabaseName <> (SELECT TOP 1 ServerName + DatabaseName FROM @test-2)
(3 row(s) affected)
ServerName DatabaseName
-------------------------------------------------- --------------------------------------------------
D900 Test1
D200 Test1
D900 Test2
(3 row(s) affected)
ServerName DatabaseName
-------------------------------------------------- --------------------------------------------------
D200 Test1
D900 Test2
(2 row(s) affected)
The problem here is you are using top without an order by. This will work on this tiny sample of data but without an order by there is no way to ensure which row will be returned by top.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 8, 2015 at 3:38 pm
SQLAddict01 (9/8/2015)
I have so many NOT IN conditions like ::Select * from backups
where
databasename not in (select databasename from backups where servername ='d900' and databasename like '%uat%')
AND databasename not in (select databasename from backup where servername ='d200' and databasename like '%uat%')
for the above asked question if I write
And databasename NOT IN (select databasename from backup where servername ='d900' and databasename ='test1')
It would take into consideration the D200 server too
I want the results of D200 server test1 database
I just want to skip d900 server test1 database.
Then doesn't this give you what you want?
Select
*
from
backups
where
databasename not in (select databasename from backups where servername ='d900' and databasename like '%uat%');
September 9, 2015 at 6:48 am
Sean Lange (9/8/2015)
eccentricDBA (9/8/2015)
SQLAddict01 (9/8/2015)
I have so many NOT IN conditions like ::Select * from backups
where
databasename not in (select databasename from backups where servername ='d900' and databasename like '%uat%')
AND databasename not in (select databasename from backup where servername ='d200' and databasename like '%uat%')
for the above asked question if I write
And databasename NOT IN (select databasename from backup where servername ='d900' and databasename ='test1')
It would take into consideration the D200 server too
I want the results of D200 server test1 database
I just want to skip d900 server test1 database.
Is this what you are looking for?
-- Create Table
DECLARE @test-2 AS TABLE (ServerName varchar(50), DatabaseName varchar(50));
-- Insert Records
INSERT INTO @test-2 (ServerName, DatabaseName) VALUES ('D900','Test1'),('D200','Test1'),('D900','Test2');
--Verify Data
SELECT * FROM @test-2
--Return Everything but the first recored
SELECT *
FROM @test-2
WHERE ServerName + DatabaseName <> (SELECT TOP 1 ServerName + DatabaseName FROM @test-2)
(3 row(s) affected)
ServerName DatabaseName
-------------------------------------------------- --------------------------------------------------
D900 Test1
D200 Test1
D900 Test2
(3 row(s) affected)
ServerName DatabaseName
-------------------------------------------------- --------------------------------------------------
D200 Test1
D900 Test2
(2 row(s) affected)
The problem here is you are using top without an order by. This will work on this tiny sample of data but without an order by there is no way to ensure which row will be returned by top.
Sean you are correct. Here is the modification with the order by.
SELECT *
FROM @test-2
WHERE ServerName + DatabaseName <> (SELECT TOP 1 ServerName + DatabaseName FROM @test-2 ORDER BY ServerName, DatabaseName)
ORDER BY ServerName, DatabaseName
SQLAddict01 I'm unsure what you are looking for. Are you looking to always remove the first row or are you looking to remove the first row for each server?
September 9, 2015 at 7:24 am
Thank you for all your effort.
I just want to remove that particular record. It's not always on the first row, it can be anywhere in the table.
September 9, 2015 at 7:41 am
SQLAddict01 (9/8/2015)
I have so many NOT IN conditions like ::Select * from backups
where
databasename not in (select databasename from backups where servername ='d900' and databasename like '%uat%')
AND databasename not in (select databasename from backup where servername ='d200' and databasename like '%uat%')
for the above asked question if I write
And databasename NOT IN (select databasename from backup where servername ='d900' and databasename ='test1')
It would take into consideration the D200 server too
I want the results of D200 server test1 database
I just want to skip d900 server test1 database.
That's not the code I proposed, I never used IN.
Do you need to exclude several rows? How do you define which ones to exclude? If it's a single row with explicit values, I gave you a possibility. If not, please tell us how do you know which rows.
And NO, NOT IN is not the best option in this case because you need to compare 2 columns.
September 10, 2015 at 11:02 am
Lynn Pettis (9/8/2015)
SQLAddict01 (9/8/2015)
.Then doesn't this give you what you want?
Select
*
from
backups
where
databasename not in (select databasename from backups where servername ='d900' and databasename like '%uat%');
That would also eliminate the third row in the example because the databasename is the same as the row in the sub-select.
September 10, 2015 at 1:25 pm
There is an option that I haven't seen anyone else suggest. It does depend on you being able to specify an order such that the row to exclude always appears first. It uses syntax introduced in SQL-2012 for paging SQL results.
SELECT *
FROM Test
ORDER BY <your order criteria here>
OFFSET 1 ROW
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 10, 2015 at 2:34 pm
drew.allen (9/10/2015)
There is an option that I haven't seen anyone else suggest. It does depend on you being able to specify an order such that the row to exclude always appears first. It uses syntax introduced in SQL-2012 for paging SQL results.
SELECT *
FROM Test
ORDER BY <your order criteria here>
OFFSET 1 ROW
Drew
I wasn't aware of that. Thanks for sharing it.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply