Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script Help Expand / Collapse
Author
Message
Posted Sunday, October 13, 2013 12:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 19, 2014 6:08 PM
Points: 10, Visits: 60

Hello. I have a portion of a table that looks like what I've listed below. This is just a small subset of the data that I'm attempting to retrieve, but this displays what the structure looks like. What I would like to do is have a script that can pull all but the top two lines. So, I want to get everything that's below the line that contains "E-Mail". Hope this makes sense.

Right now, I can pull lines 3 and 4 by using the script below, but I would like also to capture the last line in my results. Any ideas?

select Column1, Column2, Column3, Column4
from Table
where Column2 in
(select Column1 from Table
where Column1 = '22701')


Column1 Column2 Column3 Column4
8106 NULL Top 8106
22701 8111 Test2 8106
26892 22701 Extra1 8106
26893 22701 Extra2 8106
26894 26892 ExtraSub1 8106

Thank you!
Post #1504283
Posted Sunday, October 13, 2013 6:20 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 3,648, Visits: 5,328
easy_goer (10/13/2013)

Hello. I have a portion of a table that looks like what I've listed below. This is just a small subset of the data that I'm attempting to retrieve, but this displays what the structure looks like. What I would like to do is have a script that can pull all but the top two lines. So, I want to get everything that's below the line that contains "E-Mail". Hope this makes sense.



I don't see a line that says E-Mail.

You'll find many more people willing to help if you start by providing DDL and consumable sample data for your problem. Like this:

DECLARE @T TABLE
(
Column1 INT
,Column2 INT
,Column3 VARCHAR(20)
,Column4 INT
);
INSERT INTO @T
SELECT 8106, NULL, 'Top', 8106
UNION ALL SELECT 22701,8111,'Test2',8106
UNION ALL SELECT 26892,22701,'Extra1',8106
UNION ALL SELECT 26893,22701,'Extra2',8106
UNION ALL SELECT 26894,26892,'ExtraSub1',8106


Without a better explanation of what you're trying to do, all I can do is take a shot in the relative dark.

SELECT *
FROM
(
SELECT *, rn=ROW_NUMBER() OVER (ORDER BY Column1)
FROM @T
) a
WHERE rn > 2;


This returns all of the rows after the second.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1504299
Posted Monday, October 14, 2013 2:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 2,443, Visits: 7,559
Thanks for the sample data Dwain.

Another option: -
SELECT *
FROM @T
EXCEPT
SELECT TOP 2 *
FROM @T
ORDER BY Column1;

In SQL Server 2012, you could use the OFFSET command, which I think is like this (untested, as I don't have access to SQL Server 2012 atm): -
SELECT *
FROM @T
ORDER BY Column1
OFFSET 2 ROWS;




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1504349
Posted Monday, October 14, 2013 2:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:25 AM
Points: 3,648, Visits: 5,328
Cadavre (10/14/2013)

In SQL Server 2012, you could use the OFFSET command, which I think is like this (untested, as I don't have access to SQL Server 2012 atm): -
SELECT *
FROM @T
ORDER BY Column1
OFFSET 2 ROWS;



Nice! Didn't know that one as I'm still playing at SQL 2012.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1504353
Posted Monday, October 14, 2013 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 19, 2014 6:08 PM
Points: 10, Visits: 60
Hello. I apologize that my original post was a bit unclear. Based on the table in my original post, I am able to pull the following

26892 22701 Extra1 8106
26893 22701 Extra2 8106

with this script..

select Column1, Column2, Column3, Column4
from Table
where Column2 in
(select Column1 from Table
where Column1 = '22701')


However, I would like to come up with a script that will display the following results..

26892 22701 Extra1 8106
26893 22701 Extra2 8106
26894 26892 ExtraSub1 8106


Does this make more sense?

Thanks you!

Post #1504454
Posted Monday, October 14, 2013 9:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 2,443, Visits: 7,559
easy_goer (10/14/2013)
Hello. I apologize that my original post was a bit unclear. Based on the table in my original post, I am able to pull the following

26892 22701 Extra1 8106
26893 22701 Extra2 8106

with this script..

select Column1, Column2, Column3, Column4
from Table
where Column2 in
(select Column1 from Table
where Column1 = '22701')


However, I would like to come up with a script that will display the following results..

26892 22701 Extra1 8106
26893 22701 Extra2 8106
26894 26892 ExtraSub1 8106


Does this make more sense?

Thanks you!



Both Dwain's and my scripts produce the results you are talking about: -
--Declare sample table
DECLARE @T TABLE
(
Column1 INT
,Column2 INT
,Column3 VARCHAR(20)
,Column4 INT
);
--Fill table with sample data
INSERT INTO @T
SELECT 8106, NULL, 'Top', 8106
UNION ALL SELECT 22701,8111,'Test2',8106
UNION ALL SELECT 26892,22701,'Extra1',8106
UNION ALL SELECT 26893,22701,'Extra2',8106
UNION ALL SELECT 26894,26892,'ExtraSub1',8106;

--Dwain's solution
SELECT *
FROM
(
SELECT *, rn=ROW_NUMBER() OVER (ORDER BY Column1)
FROM @T
) a
WHERE rn > 2;

/*
Returns : -

Column1 Column2 Column3 Column4 rn
----------- ----------- -------------------- ----------- --------------------
26892 22701 Extra1 8106 3
26893 22701 Extra2 8106 4
26894 26892 ExtraSub1 8106 5
*/

--Alternate solution
SELECT *
FROM @T
EXCEPT
SELECT TOP 2 *
FROM @T
ORDER BY Column1;

/*
Returns : -

Column1 Column2 Column3 Column4
----------- ----------- -------------------- -----------
26892 22701 Extra1 8106
26893 22701 Extra2 8106
26894 26892 ExtraSub1 8106
*/

If that isn't what you want, can you have a read through this link (http://www.sqlservercentral.com/articles/Best+Practices/61537/) and set up some DDL, consumable sample data and expected results based on your sample data ?

Thanks.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1504463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse