Remove a value from a union

Question

Post reply

Remove a value from a union

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Meatloaf (6/24/2015)

Hi Alvin,

It is close to the first query. What is different is my first query will show all letters regardless if there is a lastname.

For example, my current query shows the follwing:

A

Apple, Bob

Apricot, Jane

B

C

Carrot,John

Corn, Jon

E

Eggplant, Ed

F

G

Grape,Sam

H

I

J

K

L

Lettuce, Lou

...

Oops, my mistake. I thought you wanted all 26 letters regardless of whether or not a last name existed.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

sgmunson (6/24/2015)

Meatloaf (6/23/2015)

Hi,

I have created a phone list and am using a union to be able to display letter category. However, what I would like to do is only show the letter category if their is an employee with the corresponding last name.

For example, if someone does not have a last name starting with "Z", then "Z" should not show up on my report.

SELECT LastName, FirstName, Dept, Phone

UNION ALL

SELECT v.letter,NULL,NULL,NULL,NULL

FROM (VALUES('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I'),('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R'),('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z')) AS v(letter)

ORDER BY vchLastName, vchFirstName

Any help is appreciated.

How about this:

SELECT LastName, FirstName, Dept, Phone FROM SomeTable UNION ALL SELECT DISTINCT UPPER(LEFT(LastName, 1)), NULL, NULL, NULL FROM SomeTable ORDER BY LastName, FirstName

This query works me my data, with one minor change. See below:



SELECT LastName, FirstName, Dept, Phone

FROM SomeTable

UNION ALL

SELECT DISTINCT UPPER(LEFT(LastName, 1))

, ISNULL(NULL, '')

, ISNULL(NULL, '')

, ISNULL(NULL, '')

FROM SomeTable

ORDER BY LastName, FirstName

I had to remove the word "NULL" from my output.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Dohsan SSCrazy Points: 2972 More actions · Answer 3

Not sure if the requirements here are getting mixed, hoping the two choices here will clear up what you want



USE FF_Winning_Together;

DECLARE @BaseData TABLE

(

	LastName VARCHAR(20),

	FirstName VARCHAR(20)

)

INSERT INTO @BaseData(LastName,FirstName)

VALUES	('Apple','Bob'),

		('Apricot','Jane'),

		('Carrot','John'),

		('Corn','Jon'),

		('Eggplant','Ed'),

		('Grape','Sam');

--Omit Letters when no lastname begins with it

SELECT	BD.LastName,

		BD.FirstName

FROM	@BaseData AS BD

UNION ALL

SELECT	DISTINCT UPPER(LEFT(BD.LastName, 1)),

		NULL

FROM	@BaseData AS BD

ORDER	BY BD.LastName,

		BD.FirstName;

--Include all letter headers regardless

SELECT	BD.LastName,

		BD.FirstName

FROM	@BaseData AS BD

UNION ALL

SELECT	CHAR(n),

		NULL

FROM	dbo.GetNums(65,90)

ORDER	BY BD.LastName,

		BD.FirstName;

mister.magoo SSC-Forever Points: 47068 More actions · Answer 4

All these UNION queries will be scanning the table twice, yes?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

mister.magoo (6/24/2015)

All these UNION queries will be scanning the table twice, yes?

Each SELECT statement will cause the data to be read from the table.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

Jeff Moden (6/24/2015)

Alvin Ramard (6/24/2015)

Deprecated? Why didn't I know that? Oh yes. I'm stuck on older versions of SQL Server.

Ah... hang on a minute while I get the egg off my face.

I read about ORDER BY ordinal being deprecated on the official MS deprecation list (IIRC) for 2005 years ago. I couldn't figure out why someone like you wouldn't know that such a widely used feature had been deprecated so I went back in all the deprecation lists from 2005 through 2012 and quit. I then went to ORDER BY in 2016 (https://msdn.microsoft.com/en-us/library/ms188385.aspx) and here's what is says...

order_by_expression

Specifies a column or expression on which to sort the query result set. A sort column can be specified as a name or column alias, [font="Arial Black"]or a nonnegative integer representing the position of the column in the select list[/font].

There are also NO deprecation warnings on that page.

This isn't the first time that I've read a supposedly final deprecation list and then find out that they had changed it and I don't think to look again even years later.

Here's my 3 cents worth .... Deprecated or not, it should not be used in Production unless there's not other choice. I can't think of any cases right now where there's no other choice.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 7

Alvin Ramard (6/24/2015)

mister.magoo (6/24/2015)

All these UNION queries will be scanning the table twice, yes?

Each SELECT statement will cause the data to be read from the table.

Yes, to be pedantic, but all the UNIONs have two SELECTS (Not sure you could avoid that), whereas the solution I posted does not, and if we had some test data, we could see what method was more efficient.

Maybe the table is small and it would be hard to measure....

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Meatloaf SSCrazy Points: 2564 More actions · Answer 8

Meatloaf

SSCrazy

Points: 2564

June 24, 2015 at 9:30 am

#1807800

Thank you all, the problem has been solved. What I am using is:

SELECT * FROM #ContactList

UNION ALL

SELECT DISTINCT UPPER(LEFT(vchLastName,1)),NULL,NULL,NULL,NULL FROM #ContactList

ORDER BY vchLastName, vchFirstName

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 9

Meatloaf (6/24/2015)

Thank you all, the problem has been solved. What I am using is:

SELECT * FROM #ContactList

UNION ALL

SELECT DISTINCT UPPER(LEFT(vchLastName,1)),NULL,NULL,NULL,NULL FROM #ContactList

ORDER BY vchLastName, vchFirstName

Great!

You're welcome.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Dohsan SSCrazy Points: 2972 More actions · Answer 10

mister.magoo (6/24/2015)

Alvin Ramard (6/24/2015)

mister.magoo (6/24/2015)

All these UNION queries will be scanning the table twice, yes?

Each SELECT statement will cause the data to be read from the table.

Yes, to be pedantic, but all the UNIONs have two SELECTS (Not sure you could avoid that), whereas the solution I posted does not, and if we had some test data, we could see what method was more efficient.

Maybe the table is small and it would be hard to measure....

Here's a start



SET NOCOUNT ON

DECLARE @Timer DATETIME;

CREATE TABLE #BaseData

(

	LastName VARCHAR(20),

	FirstName VARCHAR(20)

)

INSERT INTO #BaseData(LastName,FirstName)

SELECT	CHAR(ABS(CHECKSUM(NEWID()))%26+65)

		+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

		+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),

		CHAR(ABS(CHECKSUM(NEWID()))%26+65)

		+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

		+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

FROM	dbo.GetNums(1,10000)

SET STATISTICS IO ON

SET @Timer = GETDATE();

SELECT	DISTINCT CA.LastName,

		CA.FirstName

FROM	#BaseData AS BD

CROSS

APPLY (

		SELECT	BD.LastName,

				BD.FirstName

		UNION ALL

		SELECT	UPPER(LEFT(BD.LastName, 1)),

				NULL

		) AS CA

ORDER	BY CA.LastName,

		CA.FirstName;

PRINT '#1 in ' + CAST(DATEDIFF(MS,@Timer,GETDATE()) AS VARCHAR(10)) + 'ms';

SET @Timer = GETDATE();

SELECT	BD.LastName,

		BD.FirstName

FROM	#BaseData AS BD

UNION ALL

SELECT	DISTINCT UPPER(LEFT(BD.LastName, 1)),

		NULL

FROM	#BaseData AS BD

ORDER	BY BD.LastName,

		BD.FirstName;

PRINT '#2 in ' + CAST(DATEDIFF(MS,@Timer,GETDATE()) AS VARCHAR(10)) + 'ms';

SET STATISTICS IO OFF

DROP TABLE #BaseData;

#2 seems to be circa 40-50ms on my machine, #1 170-180ms

Execution plan puts #2 at a higher cost of the batch

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Dohsan (6/24/2015)

Execution plan puts #2 at a higher cost of the batch

Just a bit of a sidebar here. The execution plan is a wonderful research tool for improving the performance of queries but "cost of batch" comparisons should never be used to determine which is the best of two or more queries because it's sometimes flat out wrong for which is actually best.

I'll provide an example tonight but if you want to make one of your own, write an rCTE that counts from 1 to a million and then write a CROSS JOIN method to do the same. I guarantee that the rCTE will appear to be the obvious choice to use according to the "cost of batch" when the exact opposite is the real truth.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Dohsan SSCrazy Points: 2972 More actions · Answer 12

Jeff Moden (6/24/2015)

Dohsan (6/24/2015)

Execution plan puts #2 at a higher cost of the batch

Just a bit of a sidebar here. The execution plan is a wonderful research tool for improving the performance of queries but "cost of batch" comparisons should never be used to determine which is the best of two or more queries because it's sometimes flat out wrong for which is actually best.

I'll provide an example tonight but if you want to make one of your own, write an rCTE that counts from 1 to a million and then write a CROSS JOIN method to do the same. I guarantee that the rCTE will appear to be the obvious choice to use according to the "cost of batch" when the exact opposite is the real truth.

Yes, the reason I mentioned it was how counter-intuitive it seemed to the results. That and I was not expecting it to come out on top, certainly one of the many reasons to run some tests!