October 13, 2013 at 12:55 pm
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!
October 13, 2013 at 6:20 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 14, 2013 at 2:20 am
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;
October 14, 2013 at 2:23 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 14, 2013 at 9:02 am
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
2689222701Extra18106
2689322701Extra28106
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..
2689222701Extra18106
2689322701Extra28106
2689426892ExtraSub18106
Does this make more sense?
Thanks you!
October 14, 2013 at 9:14 am
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 following2689222701Extra18106
2689322701Extra28106
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..
2689222701Extra18106
2689322701Extra28106
2689426892ExtraSub18106
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/)[/url] and set up some DDL, consumable sample data and expected results based on your sample data ?
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply