SQL Puzzle 0x0A: ordinal position 1

[vongrunt]

Take a look at this:

use northwind
select top 10 CustomerID, CompanyName, ContactName, Address
from Customers
where CustomerID >= 'B'
order by ContactName

Ordered set domesticus vulgaris, nothing unusual:

CustomerID CompanyName              ContactName         Address
----------.------------------------.-------------------.-------------------------
ROMEY      Romero y tomillo         Alejandra Camino    Gran Vía, 1
MORGK      Morgenstern Gesundkost   Alexander Feuer     Heerstr. 22
TRADH      Tradiçao Hipermercados   Anabela Domingues   Av. Ines de Castro, 414
GOURL      Gourmet Lanchonetes      André Fonseca       Av. Brasil, 442
EASTC      Eastern Connection       Ann Devon           35 King George
LAMAI      La maison d'Asie         Annette Roulet      1 rue Alsace-Lorraine
FAMIA      Familia Arquibaldo       Aria Cruz           Rua Orós, 92
SPLIR      Split Rail Beer & Ale    Art Braunschweiger  P.O. Box 555
QUEDE      Que Delícia              Bernardo Batista    Rua da Panificadora, 12
FRANR      France restauration      Carine Schmitt      54, rue Royale

Now, for any single specified primary key (CustomerID) calculate it's ordinal position within ordered set. Examples:

ROMEY - should return 1
SPLIR - 8
BLAH - NULL (does not exist)

No cursors, no identity tables and no SQL2005 ranking functions please.

And also - don't assume sorted column (ContactName) is unique.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[SQLDenis]

this?

select top 10 CustomerID, CompanyName, ContactName, Address ,
(select count(*) from Customers
where CustomerID >= 'B' and ContactName <=c.ContactName)
from Customers c
where CustomerID >= 'B'
order by ContactName

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Nope. Input: single PK value, output: integer or NULL. Plus:

And also - don't assume sorted column (ContactName) is unique.

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[earthandfire]

Probably not what you are looking for but ...

declare @custid varchar(5)
set @custid = 'morgk'

declare @tmp table (custid varchar(5), ndx int identity (1, 1))

insert into @tmp (custid) 
 select CustomerID from Customers order by ContactName

if (select count(ndx) from @tmp where custid = @custid) = 0
	select null
else
	select top 1 ndx  from @tmp where custid = @custid

 

[gmmastros]

Here's my shot at it. It works for all the stated criteria, but is probably not the most optimal since I had to select in to a variable to accomodate the NULL when the CustomerId is not in the table. Without the variable, I get no records returned instead of NULL (as requested).

Alter Procedure GetOrdinal_gmmastros
	@CustomerId VarChar(10)
As 
SET NOCOUNT ON
Declare @OrdinalPosition Integer

Select @OrdinalPosition = OrdinalPosition
From   (
        select top 100 percent
               (
               Select Count(*) 
               From   Customers 
               Where  CustomerId > 'B' 
                      And ContactName <=c.ContactName
               ) As OrdinalPosition,
               CustomerID
        From   Customers c
        Where  CustomerId > 'B'
        Order By ContactName
        ) A
Where   A.CustomerId = @CustomerId

Select @OrdinalPosition As OrdinalPosition

go

Exec GetOrdinal_gmmastros 'ROMEY'
Exec GetOrdinal_gmmastros 'SPLIR'
Exec GetOrdinal_gmmastros 'Blah'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Sorry, my solution does not work when there are duplicate contact names.

I thought I made a duplicate and tested it, but sadly, I made a mistake when attempting to create a duplicate.

Update Customers
Set ContactName = 'Yoshi Lattimer'
Where CustomerId = 'HUNGC'

This will make a duplicate ContactName.

Running this...

Exec GetOrdinal_gmmastros 'LAUGB'
Exec GetOrdinal_gmmastros 'HUNGC'

Will produce 85 for both records.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[earthandfire]

I've just noticed:

No cursors, no identity tables and no SQL2005 ranking functions please.


so my feeble attempt fails as well.

 

[gmmastros]

This one works (I think)

Alter Procedure GetOrdinal_gmmastros
	@CustomerId VarChar(10)
As 
SET NOCOUNT ON
Declare @OrdinalPosition Integer

Select @OrdinalPosition = OrdinalPosition
From   (
       select top 100 percent
              (
              Select Count(*) 
              From   Customers 
              Where  CustomerId > 'B' 
                     And ContactName + CustomerId <= c.ContactName + c.CustomerId
              ) As OrdinalPosition,
              CustomerId
       from   Customers c
       Where  CustomerId > 'B'
       Order By ContactName, CustomerId
       ) A
Where  A.CustomerId = @CustomerId

Select @OrdinalPosition As OrdinalPosition

go

Exec GetOrdinal_gmmastros 'ROMEY'
Exec GetOrdinal_gmmastros 'SPLIR'
Exec GetOrdinal_gmmastros 'Blah'
Exec GetOrdinal_gmmastros 'LAUGB'
Exec GetOrdinal_gmmastros 'HUNGC'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

Yup, works. Add primary key to ORDER BY and ordered columns will be always unique together. This is kind of necessary because sort on non-unique columns is generally speaking nondeterministic.

Still, I was thinkin' about something without changed ORDER BY...

(hint: @variables)

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why

 

[vongrunt]

Awright, star goes to George - his code works as specified and has no problems with duplicate sorted values. Honorable mention for everybody else who participated in this weirdo

FWIW I was thinking about something like this:

alter proc usp_getOrdinal( @PK varchar(10) )
as
set nocount on
declare @pos bigint; set @pos = 1

select @pos = @pos+case when CustomerID=@PK then @pos*0x10000 else 1 end
from Customers
where CustomerID >= 'B'
order by ContactName, CustomerID

select nullif(@pos/0x10000, 0)

go

exec usp_GetOrdinal 'ROMEY'
exec usp_GetOrdinal 'SPLIR'
exec usp_GetOrdinal 'Blah'
exec usp_GetOrdinal 'LAUGB'
exec usp_GetOrdinal 'HUNGC'

Btw. indirectly instructing optimizer to always do Sort before Compute Scalar operator is... sometimes tricky. For example, if we remove CustomerID from ORDER BY results may or may not go .

------
Theory: everybody knows everything, nothing works
Practice: everything works, nobody knows why