Unnecesary subquery and top?

Question

Unnecesary subquery and top?

Luis Cazares

SSC Guru

Points: 183706
More actions
May 21, 2012 at 2:03 pm

#274320

Hi,
I found this code made by someone else in the company (he does not work here anymore) and it seems to me it has some unnecesary elements. Here is the code:
SELECT CAST('1900-01-01' AS smalldatetime) AS RecordAdded,
'Import New Submissions' AS Label
UNION
SELECT RecordAdded, Label
FROM (SELECT DISTINCT TOP 100 PERCENT
RecordAdded,
CAST(RecordAdded AS varchar(20)) AS Label
FROM Mytable
ORDER BY RecordAdded DESC) AS derivedtbl_1
And I figured that it would be better to rewrite it as this:
SELECTCAST('1900-01-01' AS smalldatetime) AS RecordAdded,
'Import New Submissions' AS Label
UNION
SELECT DISTINCT
RecordAdded,
CAST(RecordAdded AS varchar(20)) AS Label
FROMMytable
ORDER BY RecordAdded DESC
Is there any reason I should leave the subquery and the top 100 percent? It just seems to me that it's worthless and resource consuming.
Note: RecordAdded is a nullable smalldatetime column that shows when was the record added to the table.
Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?
How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150467 More actions · Answer 1

It seems the original coder misunderstood how ORDER BY works with UNIONs. Anyway, I might go a little further (apologies for things that are just my own personal style, I write that way out of habit):

SELECT

RecordAdded = CONVERT(smalldatetime, '1900-01-01', 121),

Label = CONVERT(varchar(22), 'Import New Submissions')

UNION ALL

SELECT DISTINCT

mt.RecordAdded,

Label = CONVERT(varchar(22), RecordAdded, 121)

FROM dbo.Mytable AS mt

ORDER BY

mt.RecordAdded DESC;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Thank you Paul and Celko, I appreciate the effort and time you made on answering this going even with more details.

I didn't beleive that DDL would be necessary as the code uses just one column, next time I'll be sure to include it.

The database or even all the server is a mess and it wasn't well designed. There are no constraints or references declared. I must have mentioned this is on a SQL Server 2000 sp 2 (I still can't believe it), and so I can't use DATE.

I'm rewriting the code entirely, but I was inspecting the original code to find out what it does. I've never used COBOL but I'm sorry that I confused the terms rows and records (english is not my native language).

Last question:

Where can I find the Books in Celko Series for Morgan-Kaufmann Publishing?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Paul White SSC Guru Points: 150467 More actions · Answer 3

Luis Cazares (5/22/2012)
I didn't beleive that DDL would be necessary as the code uses just one column, next time I'll be sure to include it.

Joe writes some good stuff in books (or so I'm told - I won't buy or read them on principle) but his manner on the forum is appalling. DDL is not needed to understand and answer your question; Joe uses copy-n-paste paragraphs to respond to people, and then lectures them about politeness. His answers are occasionally useful (though not today) and are generally about selling more of his books.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

drew.allen SSC Guru Points: 76987 More actions · Answer 4

CELKO (5/22/2012)
Rows are not records. Again, this is COBOL and not SQL. Time for a total re-write .. sorry.

Wikipedia disagrees with you

Row (database)
In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields. Each row in a table represents a set of related data, and every row in the table has the same structure.

You're not going to change common usage by fiat. If you want to convince people, your going to need to provide reasons.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Ed B Hall of Fame Points: 3666 More actions · Answer 5

That looks like code I have written in the past to return a data set for a report parameter list.

If the TOP 100% was a TOP N, then I think it would make sense.

I think this returns the same dataset as the original

SELECTCAST('1900-01-01' AS smalldatetime) AS RecordAdded,

'Import New Submissions' AS Label

UNION

SELECT RecordAdded,

CAST(RecordAdded AS varchar(20)) AS Label

FROMMytable

I wonder if the intent was for it to look like this

SELECTCAST('1900-01-01' AS smalldatetime) AS RecordAdded,

'Import New Submissions' AS Label,

1 AS Grouper

UNION

SELECT RecordAdded,

CAST(RecordAdded AS varchar(20)) AS Label,

2 AS Grouper

FROM@MyTable

ORDERBY Grouper ASc, RecordAdded DESC