Database

SELECT TOP(10) * FROM TABLE ORDER BY NEWID();

create table test (d varchar(10))
insert into test (d) values
('Test 1'), ('Test 2'), ('Test 3'), ('Test 4'), 
('Test 5'), ('Test 6'), ('Test 7'), ('Test 8')
DELETE from test WHERE Id = 3

Id	d
1	Test 1
2	Test 2
4	Test 4
5	Test 5
6	Test 6
7	Test 7
8	Test 8

SELECT * FROM test where id % 2 = 1

create table test2 (Id int, d varchar(10))
insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4'), 
(2,'Test 5'), (2,'Test 6'), (3,'Test 7'), (3,'Test 8')

Id	d
1	Test 1
1	Test 2
1	Test 3
2	Test 4
2	Test 5
2	Test 6
3	Test 7
3	Test 8

;with CTE AS
(
	select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
	FROM Test2
)
SELECT * FROM CTE WHERE r % 2 = 1

SELECT * FROM CTE WHERE rn % 2 = 1

insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4')

select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
	FROM Test2

Id	d	rn	r
1	Test 1	1	1
1	Test 1	2	1
1	Test 2	3	3
1	Test 2	4	3
1	Test 3	5	5
1	Test 3	6	5
2	Test 4	7	7
2	Test 4	8	7
2	Test 5	9	9
2	Test 6	10	10
3	Test 7	11	11
3	Test 8	12	12

;with CTE AS
(
	select *, ROW_NUMBER() OVER (ORDER BY d) as rn
	FROM Test2
)
SELECT * FROM CTE WHERE rn % 2 = 1

CHill60 wrote:

You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?

Quote:

SQL

select  count(1) as num from(select  * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo  and SendSAPStatus='0'  and v1.approvalManCode='zhouyx') )t

it returns 22 items

SELECT COUNT(*) --or COUNT(1), because both statements are equal!
FROM View_paymentApproval v1
WHERE EXISTS (
    SELECT 1
    FROM payment_Approval
    WHERE contractNo=v1.contractNo AND payNo=v1.payNo AND SendSAPStatus='0' AND v1.approvalManCode='zhouyx'
)

SELECT * 
FROM View_paymentApproval v1 
WHERE Exists
(
    SELECT 1 
    FROM payment_Approval a1
    WHERE a1.contractNo = v1.contractNo 
    And a1.payNo = v1.payNo
    And a1.SendSAPStatus = '0'
    And v1.approvalManCode = 'zhouyx'
)

a1.contractNo = v1.contractNo

contractNo = v1.contractNo

v1.contractNo = v1.contractNo