How to Select every 2nd row with condition 1 column has a same value

[Octo Siburian]

Hii all,

i have a problem to get every second row in a table with condition 1 column has a same value,

example: i have 1 table is called 'HP'
with column id,nik,hp,statu s_aktif

id | nik | hp | status_aktif
1 | 2010010269 | 08123456789 | 1
2 | 2010010269 | 08567891011 | 1
3 | 2010010271 | 08112347891 | 1
4 | 2010010271 | 08568391020 | 1

can anyone help me,
select 2nd row with condition 1 column has a same value with 'QUERY SELECT'
result:
2 | 2010010269 | 08567891011 | 1
4 | 2010010271 | 08568391020 | 1


Regards,
Octo

[colintis]

Should be something like this

SELECT id, nik, max(hp), status_aktif
FROM [Your table here]
GROUP BY nik, id, hp, status_aktif

Only works if:
1. you only have 2 records with the same nik
2. your 2nd hp value is bigger than the 1st one

[Octo Siburian]

Thanx for your reply Mr.colintis..

It's not working. all records is show in result.
1. There's many records in this table
2. It's random 'hp' value between 1st hp value and 2nd hp value

[colintis]

Try this one if it can, as this is the closest one I can think of.

SELECT TOP 1 id, nik, hp, status_aktif
FROM [Your table here]
WHERE id NOT IN (SELECT TOP 1 * FROM [Your table here] GROUP BY nik)
GROUP BY nik, id, hp, status_aktif

[nico5038]

You can use a DCount() function with in the WHERE parameter a test for nik being equal, status_aktif = 1 and hp <=.

This will add a sequence row numbering for each nik and allows you to filter for value 2.

Getting the idea ?

Nico