select @rownum:=@rownum+1 as ID , c.cnt_cp_id,msisdn , case
mode_of_subscription when 1 then 'IVR' when 2 then 'SMS' when 3 then
'WEB' when 4 then 'WAP' else 'RENUAL' end as
mode,date_format(submit_time,'%d-%m-%y %r'),trans_id,sa_id,charge/100
as charge,(select count(*) from tbl_IN_response t1 where
keyword='SONG' and in_response = 0 and submit_time between '2011-07-15
20:31:00' and '2011-07-15 20:31:50' ) as total from tbl_IN_response
t,tbl_cms_content c,(select @rownum:=0)r where c.cnt_song_id =
t.sa_idand keyword='SONG' and in_response = 0 and submit_time between
'2011-07-15 20:31:00' and '2011-07-15 20:31:50'


it will give the uplicate data plzz help me

On Oct 11, 3:12*pm, TulsiPravin Majithia
<tulsipravin.majit... (AT) pyrogroup (DOT) com> wrote:
Oh my goodness. I have no idea what "duplicates" you mean, but have
you tried "SELECT DISTINCT..."?

"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

What you did post was bad SQL. Bit flags?, improper data element
names? Formatting data for display in the database? Scalar subqery in
a SELECT list? A magic generic "id"? The three-letter strings for
subscription mode are shorter than an INTEGER and readable. Do yu
really need the time fields in the timestamps?

These are all classic SQL programming errors.