CODEWITHSUNDEEP
sql-serversql-server-2008
Vikrant
Vikrant

Reputation: 5036

Select alternate rows from SQL Server table

I am working with SQL Server 2008. I have a table which does not contain any unique columns; how to get alternate rows from it?

SQL Server table:

+-----+--------+
|  id |  name  |
|-----+--------|
|  1  |  abc   |
|  2  |  pqr   |
|  2  |  pqr   |
|  3  |  xyz   |
|  4  |  lmn   |
|  5  |  efg   |
|  5  |  efg   |
+-----+--------+

As we've to come with at least one working suggestion with the question, I've tried below code; which is not so proper technique when fetching from a huge amount of data.

Trial:

create table #tmp
(
    id int, name varchar(10), srNo int
)

insert into #tmp
   select 
      id, name,
      ROW_NUMBER() OVER (ORDER BY id) % 2 as srNo   --,alternate rows
   from 
      Employee

select * 
from #tmp 
where srNo = 1 --or srNo = 0

Above query gives out alternate rows i.e. 1st, 3rd, 5th OR 2nd, 4th, 6th etc.

Please help me out with proper way without #tmp to achieve the goal!

Upvotes: 2

Views: 48212

Answers (7)

Mehdi Benkirane
Mehdi Benkirane

Reputation: 445

create table t (id bigint NOT NULL, input_1 boolean not null, data_gps timestamp(0) not null);

insert into t (id, input_1,data_gps) values 
  (1, false  , '2022-01-01 15:42:07'),
  (2, true   , '2022-01-02 15:42:07'),
  (3, true   , '2022-01-03  15:42:07'),
  (4, false   , '2022-01-04  15:42:07'),
  (5, true   , '2022-01-05  15:42:07'),
  (6, true   , '2022-01-06  15:42:07'),
  (7, true   , '2022-01-07  15:42:07'),
  (8, true   , '2022-01-08  15:42:07'),
  (9, false   , '2022-01-09  15:42:07'),
  (10 ,true   , '2022-01-10  15:42:07'),
  (11, true   , '2022-01-11  15:42:07'),
  (12, true   , '2022-01-12  15:42:07'),
  (13, false   , '2022-01-13  15:42:07'),
  (14, true   , '2022-01-14  15:42:07');

you will have

SELECT * FROM t

Here is the query that will group by value change

select input_1, min(data_gps) as mind, max(data_gps) as maxd
from (
select input_1, data_gps,
     row_number() over (order by data_gps) 
     - row_number() over (partition by input_1 order by data_gps) as grp
from t
) as tmp
group by input_1, grp
order by min(data_gps);

The results

Query results

DEMO https://dbfiddle.uk/6Ajy3H5O

Upvotes: 0

PRATHAMESH GIRI
PRATHAMESH GIRI

Reputation: 75

I'm taking student as a table name.

Here is my answer ->

  1. For Even Row Number -

> SELECT id from (SELECT rowno, id from student) where mod(rowno,2)=0

  1. For Odd Row Number -

> SELECT id from (SELECT rowno, id from student) where mod(rowno,2)=1

Upvotes: 1

Hussain
Hussain

Reputation: 39

declare  @t table
    (
        id int,
        name nvarchar(20)
    )
    insert into @t
    Select 1, 'abc'
    union all
    Select 2, 'pqr'
    union all
    Select 2, 'pqr'
    union all
    Select 3, 'xyz'
    union all
    Select 4, 'lmn'
    union all
    Select 5, 'efg'
    union all
    Select 2, 'efg'

    Select * from(
    Select *, row_number() over(order by id) as rnum from @t ) t where rnum % 2 <> 0

Upvotes: 0

Ankit Kumar Maurya
Ankit Kumar Maurya

Reputation: 1

You can just use your select statement as an in-line view. You don't need the #tblCities table.

select tbl1.CityID,tbl1.CityName from (select ROW_NUMBER() over(order by CityID asc) as row_no,CityID,CityName from tblCities) as tbl1  where tbl1.row_no%2=1

Upvotes: 0

1210
1210

Reputation: 41

--To fetch ALTERNATE records from a table (EVEN NUMBERED)

Select * from TableName where ColumnName % 2 = 0

For Eg : select * from HumanResources.Employee where BusinessEntityID % 2 = 0

--To fetch ALTERNATE records from a table (ODD NUMBERED)

Select * from TableName where ColumnName % 2 = 1

For Eg : select * from HumanResources.Employee where BusinessEntityID % 2 = 1

Upvotes: 4

Vikrant
Vikrant

Reputation: 5036

Same also can be achieved using having clause; but it adds group by task:

SELECT id, name
FROM (SELECT id, name, ROW_NUMBER()over(order by id) AS srNo FROM Employee) x
GROUP BY srNo, id, name
HAVING (srNo % 2) = 0

Upvotes: 0

Bob
Bob

Reputation: 1055

You can just use your select statement as an in-line view. You don't need the #tmp table.

select t.id, name
from (select id, name, ROW_NUMBER() over (order by id) as srNo from Employee) t
where (t.srNo % 2) = 1

SqlFiddle

Upvotes: 4

Related Questions