select query

[amuluri]

hi am raj!
am a asp , sql server 2000 programmer

i need a sql query to select unique rows based on noofcards,versionno,id here am giving the database
-------------------------------------------
id email ccno vno nc
-------------------------------------------
41 [e-mail] f 2465 1 1
41 [e-mail] f 9898 1 3
41 [e-mail] a 9898 1 2
41 [e-mail] f 2465 2 1
41 [e-mail] e 6226 2 2
41 [e-mail] s 2465 3 1
------------------------------------------
ccno = creditcardno
vno = versionno
no = noofcard

the o/p i should get is
-------------------------------------------
id email ccno vno nc
-------------------------------------------
41 [e-mail] f 3554 1 3
41 [e-mail] e 6226 2 2
41 [e-mail] s 2465 3 1
------------------------------------------

i tryed this query
select * from creditcardinfo where versionno =(select max(versionno) from creditcardinfo where id =41)

but am getting only one row with the maxversionno

41 [e-mail] s 2465 3 1

so, pleas help me in getting the row i 've specified above.

any help will be appreciated.

[Ramy Mahrous]

SELECT distinct(ccno) FROM creditcardinfo

Anything just ask!