SQL Server Performance Forum – Threads Archive
number seq
Hi have a TSQL question. select box_id, max(si.seq_no) as seq_nofrom table Resutls looks like this
box_id seq_no 1 2
1 6
1 9
2 7
2 3
2 5 but i want the result to ignore the max and just put a sequence number for each box type box_id seq_no 1 1
1 2
1 3
2 1
2 2
2 3 any help will be much appreciated. Ajjee
USE Northwind
SELECT CustomerID, OrderID, Number =
(SELECT COUNT(*)
FROM Orders t2
WHERE t2.CustomerID= t1.CustomerID
AND t2.OrderID <= t1.OrderID
)
FROM Orders t1
ORDER BY CustomerID, OrderID It might be better handling this at the client. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
Hi Frank Thanks for you reply , still a bit confuse i have all the final result in one large temp table. i dont have to join it with any other data how would i represent the data from a single temp table with the sequence value instead of the the orignal value per box type. thanks in advance. Ajjee
Ajjee, doesn’t seem like you’ve tested Frank’s script: Frank added a subquery that, for each row, returns the number of rows that have the same ClientId but a lower OrderId. Translated to your table: select t1.box_id, t1.seq_no,
(select count(*) from table t2
where t2.box_id = t1.box_id and t2.seq_no <= t1.seq_no)
from table t1
order by t1.box_id, t1.seq_no
You might check if this performs better … select t1.box_id, t1.seq_no,
(select count(*) + 1 from table t2
where t2.box_id = t1.box_id and t2.seq_no < t1.seq_no)
from table t1
order by t1.box_id, t1.seq_no … but the effect is probably marginal, if there is any to speak of.
Adriaan Thanks for your help i will try and reflect
Where do you want to show these data? Madhivanan Failing to plan is Planning to fail
Where do you need this ouput..
quote:Originally posted by ajjee
Hi have a TSQL question.
select box_id, max(si.seq_no) as seq_no
from table Resutls looks like this
box_id seq_no 1 2
1 6
1 9
2 7
2 3
2 5 but i want the result to ignore the max and just put a sequence number for each box type box_id seq_no 1 1
1 2
1 3
2 1
2 2
2 3 any help will be much appreciated. Ajjee
from table Resutls looks like this
box_id seq_no 1 2
1 6
1 9
2 7
2 3
2 5 but i want the result to ignore the max and just put a sequence number for each box type box_id seq_no 1 1
1 2
1 3
2 1
2 2
2 3 any help will be much appreciated. Ajjee
SURYA
Thanks guys Plz see the update at the bottom of the follwing link http://www.sqlteam.com/item.asp?ItemID=1491 i will try the unique column and see if i get the right results but i think i have figured it out now… special thanks to Adriaan…
Ajjee
]]>