March 13, 2006 at 5:17 am
how to count rows with "where"
i have this code
---------------
SELECT (SELECT COUNT(*) FROM tb_hiter e2 WHERE e2.sn <= e.sn)
AS rownumber, sn
FROM tb_hiter e ORDER BY sn
-----------------------------------
how to do this ????????????
--------------------------------
SELECT (SELECT COUNT(*) FROM tb_hiter e2 WHERE e2.sn <= e.sn)
AS rownumber, sn
FROM tb_hiter e ORDER BY sn
WHERE (na = 5940)
---------------------------------------------------
thnks
ilan
March 13, 2006 at 5:57 am
Are you trying to get the row number or get a count?
This will get you a count - sql(2k) does not have the concept of rownumber. sql2k5 does have a ROW_NUMBER() function.
SELECT
COUNT(*)
FROM
tb_hiter e2
WHERE
e2.sn <= e.sn
AND e.na = 5940
March 13, 2006 at 6:02 am
Apply the where clause before the order by..something like:
use pubs
go
select a.au_fname, a.au_lname, a.state,
(select count(1) from authors where au_fname <= a.au_fname and au_lname <= a.au_lname)
from authors a
where state = 'CA'
order by a.au_fname, a.au_lname
March 13, 2006 at 6:53 am
no it not work ok
i get
1
1
2
2
3
4
4
--------------------------------
i need to cout the rows in the table
like this
1
2
3
4
5
6
---------------------------
March 13, 2006 at 11:47 pm
so
no one
know
to count rows with "Where "
thnks
ilan
March 14, 2006 at 2:07 am
Perhaps your question is not understood. I think you are trying to get a row number. Is that correct? If you post some sample data I'm sure someone will help.
March 14, 2006 at 5:16 am
yes i need to see the row number
but how to do this with "WHERE" ??
like this
-----------------
select a.au_fname, a.au_lname, a.state,
(select count(1) from authors where au_fname <= a.au_fname and au_lname <= a.au_lname)
from authors a
where state = 'CA'
order by a.au_fname, a.au_lname
-------------------------------------
and get the row number ???
how
thnks ilan
March 14, 2006 at 5:51 am
Hi Ilan,
SQL server 2k does not have the concept of a row number (unlike Oracle or Access). Sql 2005 has a ROW_NUMBER() function that outputs the row number of the result set.
The usual trick used is to create a temporary table with an IDENTITY column. Insert the data into trhe temp table and then read data from the temp table.
Something like
CREATE TABLE #Authors
([ID] INT IDENTITY,
au_fname VARCHAR(40),
au_Lname VARCHAR(40),
state CHR(2))
INSERT INTO
#Authors
(au_fname,
au_lname,
state)
select a.au_fname, a.au_lname, a.state,
(select count(1) from authors where au_fname <= a.au_fname and au_lname <= a.au_lname)
from authors a
where state = 'CA'
order by a.au_fname, a.au_lname
Then to get row 99
SELECT * FROM #Authors WHERE ID = 99
March 14, 2006 at 6:42 am
SELECT (SELECT COUNT(*) FROM tb_hiter e2 WHERE e2.sn <= e.sn AND e2.na = 5940)
AS rownumber, sn
FROM tb_hiter e
WHERE e.na = 5940
ORDER BY sn
Far away is close at hand in the images of elsewhere.
Anon.
March 14, 2006 at 7:01 am
yes yes
it work
thnks for all the wonderful group in this forum
ilan
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply