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.

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 = 3The contents of the table areId d 1 Test 1 2 Test 2 4 Test 4 5 Test 5 6 Test 6 7 Test 7 8 Test 8Note 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 ColumnSELECT * FROM test where id % 2 = 1I 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 dataId d 1 Test 1 1 Test 2 1 Test 3 2 Test 4 2 Test 5 2 Test 6 3 Test 7 3 Test 8So 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 = 1I get the correct answer. But I could just have easily usedSELECT [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 CTESELECT [MyReallyLongColumnName] AS ShorterName FROM [MyDB].[dbo].[MyTable]2returns the following valuesSELECT [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]4

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]5SELECT [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]8

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?3

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?

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?