Need Help on SQL statement

ontology · #1 (**permalink**) 05-05-07, 23:22

blindman · #2 (**permalink**) 05-06-07, 00:28

This is the general method. Things get a little more complex if CustID and ComplainDate do not represent a unique key.

Code:

Select	[YourTable].CustID,
	[YourTable].Name,
	[YourTable].ComplainDate,
	[YourTable].Status,
	count(*) as Rank
from	[YourTable]
	inner join [YourTable] Ordinal
		on [YourTable].CustID = Ordinal.CustID
		and [YourTable].ComplainDate >= Ordinal.ComplainDate
group by [YourTable].CustID,
	[YourTable].Name,
	[YourTable].ComplainDate,
	[YourTable].Status

ontology · #3 (**permalink**) 05-06-07, 22:36

thanks for your solution man

aschk · #4 (**permalink**) 05-08-07, 09:33

There is a better way to do this without using the count() function that SQL provides. Imagine if you had to do a count for 10,000 rows

It requires that you use an incrementing variable integer.
Give me a day and i'm sure I can work it out... it's a tricky process..

stolze · #5 (**permalink**) 05-08-07, 11:12

What's wrong with the COUNT? The system has to process all rows anyway. With a suitable execution plan, it will process rows by group already and then the count is trivially computed along the way.

Another alternative is to use the SQL RANK or DENSE_RANK window functions.

blindman · #6 (**permalink**) 05-08-07, 12:05

aschk, if he just wanted an ordinal value across the entire dataset he could select it into a temporary table with an auto-incrementing Identity. I think this is what you are thinking of. But the poster wants ordinal values across groups within the data, so that technique won't work.

stolze · #7 (**permalink**) 05-09-07, 03:17

Right, using GENERATED ALWAYS/DEFAULT identity columns or sequences would be applicable to the whole table. But there is another drawback for identity columns: you would have to materialize the temp data. Subselects also produce (logical) temp tables, but no materialization is necessary.

aschk · #8 (**permalink**) 05-09-07, 06:51

As an example of what i was going to demonstrate :

Code:

SELECT CustID,Name,ComplainDate,Status
      ,IF(@custno = CustID
      ,@rank := @rank + 1
      ,@rank := 1 + least(0,@custno := CustID)
     ) Rank
FROM test AS t, (select (@custno:=0),(@rank:=0)) x
ORDER BY CustID

No count

stolze · #9 (**permalink**) 05-09-07, 12:04

Besides, the above syntax not being valid SQL, you have another major problem: That won't work at all because it assumes that the ORDER BY is executed before the SELECT list. But SQL states that first the SELECT list is evaluated and then any ORDER BY applied. Thus, the rows may come in any order first, where the rank thing is computed. Then the whole thing is sorted by CustID. Obviously, you have no guarantee for specific Rank values.

So your only options (with standard SQL) are COUNT, RANK/DENSE_RANK, ROW_NUMBER, or common table expressions.