How can we get alternate records from table?

In your section 2 you have put Identity column in bold suggesting that it is someway relevant to identifying alternate records - it is not, as I stated earlier.

Table of Contents Show

How can we fetch alternate records from a table?
How do I fetch odd records in SQL?
How to fetch alternate records from a table for both odd and even row number?
How do I select unique records from a table in SQL?

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

Your final comment I think, is saying that the Rank function has an option to order columns - all Window functions have the potential for ORDER BY and/or PARTITION BY, that's why they are sometimes referred to as OVER functions. You can use Window functions on any table, not just derived tables or CTEs.

Here is an example of why Identity Column is not appropriate: Consider this sample data

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

The contents of the table are

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

Note the missing Id 3.
So I would expect to return rows where Id = 1, 4, 6 and 8. But if I just use the Identity Column

SELECT * FROM test where id % 2 = 1

I only get rows where id = 1, 5 and 7. Incorrect.

An example where RANK is inappropriate. Consider the following test data

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')

The table contains the data

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

So I would expect to return the rows where d is Test... 1, 3, 5, 7.
If I try to use Rank like this

;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

I get the correct answer. But I could just have easily used

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

0as both RANK and ROW_NUMBER return the same value in this instance. I contend that using ROW_NUMBER is clearer and less prone to risk - what if someone changes it to use a PARTITION …

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

1 … you're going to get the rows where Test is … 1, 3, 4, 6, 7. Incorrect again.

Even if partition is not used, RANK can fail depending on the data being returned. Try adding some more data to test2 e.g.

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

1Note that test2 now contains duplicate rows so the query that forms the CTE

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

2returns the following values

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

3Expected results would be Test 1, Test 2, Test 3, Test 4, Test 5, Test 7 but if I re-run the CTE I actually get Test 1, 1, 2, 2, 3, 3, 4, 4, 5, and 7. Incorrect again.

However using ROW_NUMBER will always work, regardless of the data contents

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

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

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

2 is a ranking function; I suspect that's what Santosh meant.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

I was trying to make him aware that his post was as clear as mud.
I'd just had a session with a user group from whom I'm trying to get some requirements. They (the group and the requirements!) are woollier than a woolly mammoth.
I wasn't in the best of moods.

You mean there can be requirements that aren't that woolly?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

We cannot share same solution..this site for sharing our knowledge and experience to find best and alternate solution to reach OP's expectations.I always try to understand others i hope u too

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

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

6
it returns 22 items
but when I use statement query in parentheses:

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

7
it returns 20 items
how can this possible?I'm confused.

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

it returns 22 items

As to the semantic used in your statement: it does NOT return 22 items. It returns a number which should be equal to the count of records returned by the view.

As to the strange issue... I can't reproduce your issue, but i can recommend to test this statement:

When you have a query that involves multiple tables, it's a good idea to prefix every column with the table name / alias:

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

9
Not only does it make it easier to work out which table the column comes from, it can also prevent bugs.

For example, if the column

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

3 existed in

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

4, but not in

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

5, then:

How can you get the alternate records from the table in the SQL?

0would produce an error, whereas:

How can you get the alternate records from the table in the SQL?

1would produce incorrect results, since it would be equivalent to:

How can you get the alternate records from the table in the SQL?

2which would always be true.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

it's right.Thank you,dude.The column 'contractNo' has null value.
Could someone who knows MySql well take a look at my question here?

Much appreciated.

[Editor: Moved]

modified 17-Apr-19 14:03pm.

Please don't do this. All questions get looked at in turn. Just think what the Lounge would be like if everyone followed your example.

This is now an older question and that phase has passed without much of value so this is the post of last resort.

You still have options:
- edit the question and add further information.
- reply to the person(s) who have already responded with some feedback or requests for more assistance.
I am trying retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql with query below.. however I am encountering below.. unable to find the missing paranethesis

select owner, table_name, column_name,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(distinct "' || column_name || '") as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as distinct_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when (' || column_name || ' = ' ' ) then 0 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as null_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when "' || column_name || '" is not null then 1 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as notnull_count
from all_tab_columns
where owner = 'JAMES'
and table_name = 'TEST'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2',
'NCHAR', 'NVARCHAR2');

How can you get the alternate records from the table in the SQL?

Please can u help

Start with the first left parenthesis and find its matching right one. Then go on to the next left, and so on until you find the missing one. It should not take more than a couple of minutes. You would also probably be better using less complex queries.

I am still unable to resolve

Maybe you should rewrite this query into its separate clauses so you can see the breaks more clearly. You have a number of parts where you are concatenating text and variable fields so it may be that you have unbalanced quote characters.
I am trying to work through a table of linked server names to get their version and productversion

Here is the code I'm using but I need to be able to get the values and use them elsewhere. I cannot get them into parameters. Also despite it populating the output with a list it isn't for each server. I mean its exactly the same info for every server. But its wrong its showing

How can we fetch alternate records from a table?

We can use rownum and MOD function to retrieve the alternate records from a table..

To get Even number records:SELECT * FROM (SELECT rownum, ID, Name. FROM Employee) WHERE MOD(rownum,2)=0..

To get Odd number records:SELECT * FROM (SELECT rownum, ID, Name. FROM Employee) WHERE MOD(rownum,2)=1..

How do I fetch odd records in SQL?

SELECT * FROM table_name WHERE mod(column_name,2) = 0; This syntax will find rows where our target column has odd values: SELECT * FROM table_name WHERE mod(column_name,2) <> 0; SQL Server does not have a MOD function.

How to fetch alternate records from a table for both odd and even row number?

Write a subquery with an ORDER BY clause. Along with the data columns, select the pseudocolumn rownum with an alias, say rn. In the outer query, reference the alias rn and use the mod function to get odd rows or even rows.

How do I select unique records from a table in SQL?

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.