Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Returning record position / count together

Posted on 2004-11-21
6
Medium Priority
?
1,145 Views
Last Modified: 2012-05-05
Hi,

I want to return a record position and count as a varchar.  I can calculate the position by counting the records less that the current record, and the count by simply counting the records, but how i can i do this in 1 query and have them in seperate fields:

eg:      Pos      Count
      2      10

THere is also another criteria that needs to be applied im not sure if it will complicate issues. EG count will be SELECT COUNT(id) FROM tablename WHERE type=1;

Thanks,
Michael
0
Comment
Question by:Xavior2K3
  • 2
4 Comments
 
LVL 9

Expert Comment

by:paelo
ID: 12639940
SELECT y.pkfld, (SELECT COUNT(*) FROM dbo.yourtable WHERE pkfld<=y.pkfld) AS pos
FROM dbo.yourtable y
WHERE y.type=1


-Paul.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 total points
ID: 12639946
do you mean

select id,count(*)
from table
where type = 1
group by id
order by id

?

or
select pos,num as [count]
from (
Select a.id, Num,count(*) as pos
from Table as A
Inner Join (
select id,count(*) as Num
from table
where type = 1
group by id
) as b
on A.id <=b.id
group by a.id,num
) as c
order by pos

?
 
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12639949
sorry...

select convert(varchar(10),pos),convert(varchar(10),num) as [count]
from (
Select a.id, Num,count(*) as pos
from Table as A
Inner Join (
select id,count(*) as Num
from table
where type = 1
group by id
) as b
on A.id <=b.id
group by a.id,num
) as c
order by pos
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12640653
For the record:  The questioner chose to ignore over a month ago, a  reminder for this abandoned question:
http://www.experts-exchange.com/Web/Web_Languages/JavaScript/Q_21117933.html
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question