TOP vs Max/Min: Is there a difference?

Question

TOP vs Max/Min: Is there a difference?

December 19, 2018 at 2:15 am

This was removed by the editor as SPAM

Viewing 6 posts - 31 through 35 (of 35 total)

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

Mike Byrd Default port Points: 1458 More actions · Answer 1

Create code for Sales.SalesOrderHeaderBig is

USE AdventureWorks2012Big
GO
CREATE TABLE Sales.SalesOrderHeaderBig(
SalesOrderID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
RevisionNumber tinyint NOT NULL,
OrderDate datetime NOT NULL,
DueDate datetime NOT NULL,
ShipDate datetime NULL,
[Status] tinyint NOT NULL,
OnlineOrderFlag dbo.Flag NOT NULL,
SalesOrderNumber AS (isnull(N'SO'+CONVERT(nvarchar(23),SalesOrderID,(0)),N'*** ERROR ***')),
PurchaseOrderNumber dbo.OrderNumber NULL,
AccountNumber dbo.AccountNumber NULL,
CustomerID int NOT NULL,
SalesPersonID int NULL,
TerritoryID int NULL,
BillToAddressID int NOT NULL,
ShipToAddressID int NOT NULL,
ShipMethodID int NOT NULL,
CreditCardID int NULL,
CreditCardApprovalCode varchar(15) NULL,
CurrencyRateID int NULL,
SubTotal money NOT NULL,
TaxAmt money NOT NULL,
Freight money NOT NULL,
TotalDue AS (isnull((SubTotal+TaxAmt)+Freight,(0))),
Comment nvarchar(128) NULL,
rowguid uniqueidentifier ROWGUIDCOL NOT NULL,
ModifiedDate datetime NOT NULL,
CONSTRAINT PK_SalesOrderHeaderBig_SalesOrderID PRIMARY KEY CLUSTERED
(SalesOrderID ASC));
GO

CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderBig_rowguid ON Sales.SalesOrderHeaderBig
(rowguid ASC)
GO
CREATE UNIQUE NONCLUSTERED INDEX AK_SalesOrderHeaderBig_SalesOrderNumber ON Sales.SalesOrderHeaderBig
(SalesOrderNumber ASC)
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderBig_CustomerID ON Sales.SalesOrderHeaderBig
(CustomerID ASC)
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderBig_OrderDate ON Sales.SalesOrderHeaderBig
(OrderDate ASC)
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeaderBig_SalesPersonID ON Sales.SalesOrderHeaderBig
(SalesPersonID ASC)
GO

Mike Byrd

steve.powell 14027 SSC Veteran Points: 246 More actions · Answer 2

Lynn Pettis - Tuesday, December 18, 2018 2:09 PM
I can actually say that I have never used ORDER BY ordinal position in any code I have written.
If I want to order by a column or set of columns I will explicitly specify the column(s).

I would love to say I haven't used the ordinal position for ORDER BY, but I found instances (some linked tables and files for example) where I couldn't identify the column name so had to sort by a known data type (specifically a date) that I knew was in position x. For those instances, it's useful, but they are the exception, so yes, it needs to stay but used with caution.

rchantler SSCrazy Points: 2128 More actions · Answer 3

Sean Lange - Tuesday, December 18, 2018 2:39 PM
rchantler - Tuesday, December 18, 2018 2:16 PM
Jeff Moden - Tuesday, December 18, 2018 1:12 PM
Sean Lange - Tuesday, December 18, 2018 10:20 AM
neil-560592 - Tuesday, December 18, 2018 4:56 AM
Just tried:
select EntityID, secondcol, thirdcol
from dbo.Entity
order by 1+2
Doesn't order by thirdcol - gives a syntax error. So the number (1) is an alias for "first column"; it is not parsed as an integer.
It isn't an alias, it is the ordinal position of columns in the result set. It is a carry over from sysbase and is really dreadful in a real world scenario.
But it's great for quick "what if" and "proof of principle" code where the order and column names may change a lot or not even exist. My hopes that they never get rid of it are dashed because, IIRC, they've deprecated its use.
If MS keeps deprecating useful tools like this one, they'll eventually make SQL Server "idiot proof" and then only idiots will use it. 😀
Does deprecation ever happen? I thought SQL collations were going to be deprecated years ago. (maybe they have been??) in favor of Windows collations.
Sure stuff gets deprecated every release. Sadly most of them get removed, which they should. I have never heard that sql collations would be deprecated. Where did you read that? I highly doubt that would happen anyway. SQL Server now runs on Linux. If they used on Windows collations instead of their own that would prove to be nearly impossible.

If you google [deprecate SQL collation] you will get links, some of which point to SQL Server Central about this. These are from 10+ years ago. One of those provides a further link to Microsoft, but upon current examination that page does not support the idea of deprecating SQL collations. I think this idea was "in the wind" about 10 years ago but may have been abandoned. As you say, Linux is a good argument for the continued use of SQL collations.

kholliday 48617 Newbie Points: 1 More actions · Answer 4

I’m far from a SQL guru but have a question regarding the original code.

Should like 6 start with:

SET @MAXID ... rather than SET @MINID ... ?

Mike Byrd Default port Points: 1458 More actions · Answer 5

Hmmm, not sure how the code in the article got changed, but you are absolutely right.

Mike Byrd