Re: Sequential integer column in view
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 03/11/04
- Next message: Dr. StrangeLove: "Re: A Query Question"
- Previous message: Greg O: "Re: Crosstab query in SQL"
- In reply to: alien2_51: "Re: Sequential integer column in view"
- Next in thread: alien2_51: "Re: Sequential integer column in view"
- Reply: alien2_51: "Re: Sequential integer column in view"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 11 Mar 2004 16:19:48 -0600
select c1.CustomerID, (
select count(*) as rownum
from customers c2
where c1.CustomerID >= c2.CustomerID)
from customers c1
Yes, it does require uniqueness. What is going on is that for every row in
customers c1, you are running a correlated query taking the value of the
current customerId and you are looking to see how many rows in the full
table have a customerId >= the current one. If you are at the top of the
set (order wise, since there is no real ordering to a table, but there is
ordering to the values, based on sort collations), only 1 row will be
greater, if you are at the end of the set, every row will be greater.
You should note that when you use this view, the values may come out in a
wierd order:
create view test2
as
select c1.CustomerID, (
select count(*) as rownum
from customers c2
where c1.CustomerID >= c2.CustomerID) as rownum
from customers c1
On my machine (a laptop):
select * from test2
Returns:
CustomerID rownum
---------- -----------
ALFKI 1
ANATR 2
ANTON 3
AROUT 4
BERGS 5
BLAUS 6
BLONP 7
BOLID 8
BONAP 9
BSBEV 11
CACTU 12
CENTC 13
CHOPS 14
CONSH 16
DRACD 17
DUMON 18
EASTC 19
ERNSH 20
FISSA 22
FOLIG 23
FOLKO 24
FRANK 25
FRANR 26
FRANS 27
FURIB 28
GALED 29
GODOS 30
KOENE 39
LACOR 40
LAMAI 41
LEHMS 44
MAGAA 49
MAISD 50
MORGK 52
NORTS 53
OCEAN 54
OTTIK 56
PARIS 57
PERIC 58
PICCO 59
PRINI 60
QUICK 63
RANCH 64
REGGC 66
RICSU 68
ROMEY 69
SANTG 70
SEVES 72
SIMOB 73
SPECD 74
SUPRD 76
TOMSP 79
TORTU 80
VAFFE 83
VICTE 84
VINET 85
WANDK 86
WARTH 87
WILMK 90
WOLZA 91
OLDWO 55
BOTTM 10
LAUGB 42
LETSS 45
HUNGO 37
GROSR 33
SAVEA 71
ISLAT 38
LILAS 46
THECR 78
RATTC 65
LINOD 47
GREAL 32
HUNGC 36
LONEP 48
THEBI 77
MEREP 51
HANAR 34
QUEDE 61
RICAR 67
COMMI 15
FAMIA 21
GOURL 31
QUEEN 62
TRADH 81
WELLI 88
HILAA 35
LAZYK 43
TRAIH 82
WHITC 89
SPLIR 75
Note that this is not in any order that makes sense. You will still need to
use an order by clause to get the data returned in the order you want:
select * from test2 order by customerId
So, really you are not adding a sequential integer to the view, you are
adding a rank to the order of the customer. If the user sorts by a different
column (instead of customerId, assuming you have other columns) the
sequential nature of the value will be lost, and you will just end up
wasting clock cycles.
Hence, if you actually need the rank of the customer in the table for
additional calculations, this may make sense, but otherwise this kind of
thing is usually best done on the client
-- ---------------------------------------------------------------------------- ----------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote in message news:eZ6T6N7BEHA.2576@TK2MSFTNGP11.phx.gbl... > Duh.. Please excuse... CustomerID is an nchar... What would realoly help me > now would be an explanation as to how that works and under what condition it > will work... Does it require you to compare on a unique column...? > > "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote > in message news:eLWHCM7BEHA.2060@TK2MSFTNGP12.phx.gbl... > > Hi Karl, > > > > Thanks for your reply... Thats a very interesting example... It does what > I > > had asked for... I'm not really sure what going on there but it seems that > > it requires an underlying identity column for the where clause (where > > c1.CustomerID >= c2.CustomerID).. Is that correct...? How would this work > > say if your table had a char as a primary key...? > > > > "Karl Gram" <NOSPAMkarl@gramonline.nl> wrote in message > > news:e%23qsQ%236BEHA.892@TK2MSFTNGP09.phx.gbl... > > > Hi, > > > > > > Here's a sample using the Nothwind Customers table: > > > > > > Use NorthWind > > > > > > select c1.CustomerID, ( > > > select count(*) as rownum > > > from customers c2 > > > where c1.CustomerID >= c2.CustomerID) > > > from customers c1 > > > > > > > > > > > > -- > > > HTH > > > > > > Karl Gram > > > > > > "alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> > > wrote > > > in message news:u089Xz6BEHA.2804@tk2msftngp13.phx.gbl... > > > > I want to add a "Calculated" column to a view that basically auto > > > increments > > > > for every row returned from the view starting from 1 and incremetning > by > > > 1, > > > > does someone know if this can be done in a T-SQL statement... I was > > > thinking > > > > of maybe cast something as an "int identity" but I'm not sure what to > > > > cast... I don't want to create a temp table or anything like that... A > > > > sample would be AWESOME!! and very much appreciated... > > > > > > > > TIA.... > > > > > > > > Dan > > > > > > > > > > > > > > > > > > > >
- Next message: Dr. StrangeLove: "Re: A Query Question"
- Previous message: Greg O: "Re: Crosstab query in SQL"
- In reply to: alien2_51: "Re: Sequential integer column in view"
- Next in thread: alien2_51: "Re: Sequential integer column in view"
- Reply: alien2_51: "Re: Sequential integer column in view"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
