Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
amuluri
Starting Member
8 Posts |
 Posted - 2007-11-27 : 02:09:33
|
| hi am raj!am a asp , sql server 2000 programmeri need a sql query to select unique rows based on noofcards,versionno,id here am giving the database-------------------------------------------id email ccno vno nc-------------------------------------------41 b@r.com f 2465 1 141 b@r.com f 9898 1 341 b@r.com a 9898 1 241 b@r.com f 2465 2 141 b@r.com e 6226 2 241 b@r.com s 2465 3 1------------------------------------------ccno = creditcardnovno = versionnono = noofcardthe o/p i should get is-------------------------------------------id email ccno vno nc-------------------------------------------41 b@r.com f 3554 1 341 b@r.com e 6226 2 241 b@r.com s 2465 3 1------------------------------------------i tryed this queryselect * from creditcardinfo where versionno =(select max(versionno) from creditcardinfo where id =41)but am getting only one row with the maxversionno41 b@r.com s 2465 3 1so, pleas help me in getting the row i 've specified above.any help will be appreciated. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-27 : 02:16:08
|
your sample data title have 5 columns but the data have 6. Please clarify.Please post your table DDL in create table statement and sample data in insert table .. format. It will make things easier. .
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
amuluri
Starting Member
8 Posts |
Posted - 2007-11-27 : 07:27:12
|
| thanq for ur advice khtan !here am sending a neat formated table-------------------------------------------id email name ccno vno nc-------------------------------------------41 b@r.com f 2465 1 141 b@r.com f 9898 1 341 b@r.com a 9898 1 241 b@r.com f 2465 2 141 b@r.com e 6226 2 241 b@r.com s 2465 3 1------------------------------------------and the o/p i should get is-------------------------------------------id email name ccno vno nc-------------------------------------------41 b@r.com f 3554 1 341 b@r.com e 6226 2 241 b@r.com s 2465 3 1------------------------------------------please help me in getting this.thanq ones again Khtan |
|
|
|
amuluri
Starting Member
8 Posts |
Posted - 2007-11-27 : 07:32:41
|
| its table statement is create table ccinfo (id int,emailid varchar(100),name varchar(100),ccno nvarchar(25),vno int,nc int) where id,vno,nc are primarykeysthis is the insert queryinsert into ccinfo values (41,'b@r.com','h','1234',1,4) |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2007-11-27 : 07:36:14
|
| I think the data samples you showed are incorrect and can only assume you either (a) want to get distinct records or (b) summarize data for distinct records. Check out the BOL or Google (a) the DISTINCT keyword and/or (b) the GROUP BY clause and aggregates. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-27 : 09:17:08
|
[code]-------------------------------------------id email name ccno vno nc-------------------------------------------41 b@r.com f 3554 1 341 b@r.com e 6226 2 241 b@r.com s 2465 3 1------------------------------------------[/code]can you explain how to obtain the 3554 ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
Sql Select Query