Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mccracj
Starting Member
2 Posts |
 Posted - 2002-03-06 : 23:12:36
|
| Is there any simple way to get a count of X/Y out of a set?IE: Select count(*) .... will give you the total number of records.What if I want to pick a record in that set and know that it is 7/10 for instance? I usually calculate X (select count(*)) then do another query to count all those in the que above the record I'm looking for - and do some math to figure out where it is in the 'que'.Is there a simpler way to do this? It seems like it should be obtusely easy, but I guess I'm overlooking it.Jason |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-03-06 : 23:26:10
|
| When you say 7/10 . How will you determine the record is 7th record. if it is done on last insert then therez no way Sql server can tell you the order. because depending on your clusterindexes the order of insertion might be changed. if you have any field on which you can determine your order. then post your table schema. we should be able to help you.-------------------------------------------------------------- |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-03-07 : 06:11:21
|
| 'que'? |
|
|
|
mccracj
Starting Member
2 Posts |
Posted - 2002-03-07 : 08:33:10
|
| The fields that matter in this case are:CallID - 'Ticket Number', VarChar, My 'Unique Id', (00000001, 00000123)Priority - VarChar, (1-4)RecvdDate - VarChar (2002-19-02)RecvdTime - VarCharThis is a SQL back end to a help desk ticketing software. One of the things we put in are install requests. My boss would like a status that tells people - your request is # 7 out of 10 in the que. The sorting is Order by Priority, RecvdDate, RecvdTime. I suppose that Order by Priority, CallID would work just as well - since both those fields contain numbers (and call IDs are assigned in the order they are opened).Right now I'm using the first select count(*) to give me all records. Then I do a select count(*) where database.RecvdDate < MyRecord.RecvdDate. Then I add 1 to the 2nd statement (all the calls above it + 1 will give me its place in the que/list/order).So I can get my result that way - I guess I was just curious if anything existed to say Select PlaceInList(*) where <sort by this>. Only saves like a second or so off my query, but just mainly thought it would be a common thing to want to check for.Jason |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-07 : 18:30:03
|
| what about something like this:--create the temp tableselect * into #b from queue order by priority, recvdDate, recvdTime--add order columnalter table #b add id int identitygo--total number declare @count intselect @count = count(*) from #b--return select 'you are in position ' + cast(id as nvarchar(50)) + ' out of ' + CAST(@count as nvarchar(50))from #bwhere CallID = @callIddrop table #bIt's probably pretty much the same as what you have. You could encapsulate it into an sp pretty easily though.Also, I'd warn against 1. storing date and time separately2. storing date and time as nvarcharEspecially 1. I made that mistake once, and it seemed reasonable at the time. I think I probably spent about a month trying to get my code working before I reverted to storing things as datetime.But that's just me and I'm fuddy-duddy and old.....--I hope that when I die someone will say of me "That guy sure owed me a lot of money"Edited by - rrb on 03/07/2002 18:33:54 |
|
|
|
|
|
|
|
|
Count(*)