how to select from two rows with same information but one clumn data is diffren

[nittin14]

Name designation promotedto newdestin
Dr Dharam Raj Singh Scientist 22 Jan 2008 Scientist S.S
Dr Dharam Raj Singh Scientist 22 Jan 2010 Scientist S.S

how i wl select one row from there two rows.
this is an eg. in mine table there is lots of rows of this type

thanks

[pbmods]

You're probably looking for something like this:

SELECT DISTINCT * FROM `tableName` GROUP BY `name`;
 

[nittin14]

sorry sir ur query is not working i think u haven't seen my query there is a column with disimilar data but name is the same.
pls
chek it and reply me

You're probably looking for something like this:

SELECT DISTINCT * FROM `tableName` GROUP BY `name`;
 

[pbmods]

sorry sir ur query is not working i think u haven't seen my query there is a column with disimilar data but name is the same.

I'm not sure I understand how your table is set up. I think it looks something like this:

Name                designation        promotedto        newdestin
-------------------        -----------        -----------        -------------
Dr Dharam Raj Singh        Scientist        22 Jan 2008        Scientist S.S
Dr Dharam Raj Singh        Scientist        22 Jan 2010        Scientist S.S
 

SELECT DISTINCT will only show one row when it encounters multiple rows that have the same value in the GROUP BY clause. So if you wanted to get unique names, you would use the query I provided in the last post (though you'd probably need to change `tableName` to the whatever your table is actually named).

If you had people with the same name, and you wanted to differentiate further, you might need to add another column to the GROUP BY clause (e.g., SELECT DISTINCT * FROM `tableName` GROUP BY `name`, `designation`).

[nittin14]

thanks brother for ur reply
but i think u don't understand my problem:
now i am paste my query and output here:

query:

Select title+' '+UPPER(SUBSTRING(FirstName,1,1)) + LOWER(SUBSTRING(FirstName,2,LEN(FirstName)))+' '+
UPPER(SUBSTRING(MidName,1,1)) + LOWER(SUBSTRING(MidName,2,LEN(MidName)))+' '+
UPPER(SUBSTRING(LastName,1,1)) + LOWER(SUBSTRING(LastName,2,LEN(LastName))) as name,d.desg_name as olddesignation,
CONVERT(CHAR(11),dateadd(yy,pd.yearsexp,p.dt_last_ prom),106) as promotiondue,
d1.desg_name as newdesignation
From personnel p,promotiondue pd,qualification q,designation d,designation d1
where p.Ser_Type= 'S' and p.I_code= 83
and p.desg_code=pd.desgcode and p.emp_code=q.emp_code and q.qual_Type=pd.educriteria
and pd.desgcode = d.desg_code and pd.promotedto = d1.desg_code
and getdate() < dateadd(yy,pd.yearsexp,p.dt_last_prom)
order by name


output:


name olddesgintaion promotedto newdesignation
Dr Dharam Raj Singh Scientist 22 Jan 2010 Scientist S.S
Dr Dharam Raj Singh Scientist 22 Jan 2008 Scientist S.S
Dr Rajender Parsad Sr.Scientist 23 Jan 2009 Principal Scientist
Dr Seema Jaggi Sr.Scientist 21 Jan 2009 Principal Scientist
Dr Sushila Kaul Sr.Scientist 29 May 2008 Principal Scientist
Mr Hukum Chandra Scientist S.S 19 Dec 2007 Scientist S.G
Smt Alka Arora Scientist S.S 27 Nov 2008 Scientist S.G


in this query dharam raj has done two degreee (msc and phd) .first record show on the basis of msc and second record show on the basis of phd. but i want that only phd record is show there not firstone.


pls help me yaar i m really confuse how to solve this problem.
reply me soon

I'm not sure I understand how your table is set up. I think it looks something like this:

Name                designation        promotedto        newdestin
-------------------        -----------        -----------        -------------
Dr Dharam Raj Singh        Scientist        22 Jan 2008        Scientist S.S
Dr Dharam Raj Singh        Scientist        22 Jan 2010        Scientist S.S
 

SELECT DISTINCT will only show one row when it encounters multiple rows that have the same value in the GROUP BY clause. So if you wanted to get unique names, you would use the query I provided in the last post (though you'd probably need to change `tableName` to the whatever your table is actually named).

If you had people with the same name, and you wanted to differentiate further, you might need to add another column to the GROUP BY clause (e.g., SELECT DISTINCT * FROM `tableName` GROUP BY `name`, `designation`).

[nittin14]

brother i had make new table check1
with field
name
id

data is
name id
amit 1 // i don't need this record
amit 3 // i need this record
ravi 5
anil 7


but when i exeucted ur query then ur query give error:
Column 'check1.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

ur query is what i executed :
select distinct * from check1 group by name

I'm not sure I understand how your table is set up. I think it looks something like this:

Name                designation        promotedto        newdestin
-------------------        -----------        -----------        -------------
Dr Dharam Raj Singh        Scientist        22 Jan 2008        Scientist S.S
Dr Dharam Raj Singh        Scientist        22 Jan 2010        Scientist S.S
 

SELECT DISTINCT will only show one row when it encounters multiple rows that have the same value in the GROUP BY clause. So if you wanted to get unique names, you would use the query I provided in the last post (though you'd probably need to change `tableName` to the whatever your table is actually named).

If you had people with the same name, and you wanted to differentiate further, you might need to add another column to the GROUP BY clause (e.g., SELECT DISTINCT * FROM `tableName` GROUP BY `name`, `designation`).

[pradeep kaltari]

brother i had make new table check1
with field
name
id

data is
name id
amit 1 // i don't need this record
amit 3 // i need this record
ravi 5
anil 7


but when i exeucted ur query then ur query give error:
Column 'check1.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

ur query is what i executed :
select distinct * from check1 group by name

Hi Nittin,
You are getting that error because you are doing a select distinct * on the table grouped by the name. The table has two different entries for the same name "amit" and the result after executing GROUP BY Name should give only one entry for each name, hence the error.

Please let us know if you have any other field in the table based on which you want the record for "amit" with id 3.

If you are using a GROUP BY clause then all the columns you want to SELECT (other than aggregate functions) need to be specified in the GROUP BY clause as well.

Regards,
Pradeep

[sudhaMurugesan]

Hi nittin,
I too had the same problem . you please check with exists function. see my replies in the following url
[HTML]http://www.thescripts.com/forum/thread641242.html[/HTML]

[nittin14]

helo brother


how r u .
i got the solution.
the solution is if there is two record of the same name then in asp.net use varreader. when they read one by one put if condition there then if condition is true store it in a datatable otherwise not atlast bind it with datalist.

if u need more ans. then tell me i wl give

thanks for ur corparatae

Hi nittin,
I too had the same problem . you please check with exists function. see my replies in the following url
[HTML]http://www.thescripts.com/forum/thread641242.html[/HTML]