Database

Kevin Marois wrote:

So what if I created a PK table on the sever:

DerekTP123 wrote:

Please, tell me my hosting provider is talking rubbish!

DerekTP123 wrote:

rather than just a later version. It's a real shame, because 15 years ago when I first started using them

SELECT a.*
FROM [MyDB].[dbo].[MyTable] AS a

SELECT [MyReallyLongColumnName] AS ShorterName
FROM [MyDB].[dbo].[MyTable]

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?