May 24, 2010 at 2:45 am
I have a table called tbl_Mesasges. It has field 'phone_number'.
Now I want to select records only with Distinct phone_number. If there are two records with the same number, the record which has recent time stamp should be selected.
Please help me out. its urgent.
May 24, 2010 at 2:51 am
Please havea look at ROW_NUMBER(). Use it with 'phone_number' ordered desc and select the results with rwn=1.
[sarcasm ON] Since you marked it as "urgent" I replied the fastest way I could: plain text. Otherwise I would have taken the time asking you for some ready to use sample data to convert it into tested SQL code... 😀
[sarcasm OFF]
May 24, 2010 at 2:55 am
I would suggest using ROW_NUMBER:
CREATE TABLE #tbl_Messages (
phone_number varchar(50),
time_stamp datetime
)
;WITH messages AS (
SELECT phone_number,
time_stamp,
Row_Num = ROW_NUMBER() OVER(PARTITION BY phone_number ORDER BY time_stamp DESC)
FROM #tbl_Messages
)
SELECT phone_number
FROM messages
WHERE Row_Num = 1
You should post table definition, sample data and desired output based on your sample data for a better answer.
Take a look at the article linked in my signature and you'll find out how to do it effectively.
Regards
Gianluca
-- Gianluca Sartori
May 24, 2010 at 3:13 am
Gianluca,
Thanks for replyong so fast.
another problem is I have to get time stamp value from table tbl_Mobile. And primary key is SWID for tbl_Mobile table. I also have SWID field in tbl_Messages.
May 24, 2010 at 3:17 am
I'll be glad to help if you at least try to post some table script and sample data.
-- Gianluca Sartori
May 24, 2010 at 3:21 am
script for Tbl_Messages
CREATE TABLE [dbo].[tbl_Messages](
[pbuserid] [uniqueidentifier] NOT NULL,
[mobile_number] [varchar](20) NULL,
[SWID] [varchar](50) NULL,
[message] [varchar](200) NULL,
[characterID] [int] NULL,
[tacticID] [int] NULL,
[destID] [int] NULL,
[subject_ln] [varchar](50) NULL,
[body] [varchar](50) NULL,
[campaign_code] [varchar](50) NULL
) ON [PRIMARY]
Script for tbl_Mobile
CREATE TABLE [dbo].[tbl_Mobile](
[SWID] [varchar](50) NOT NULL,
[hshold_id] [varchar](50) NULL,
[gst_id] [varchar](50) NULL,
[addr_lctr_id] [varchar](50) NULL,
[eml_lctr_id] [varchar](50) NULL,
[gst_frst_nm] [varchar](200) NULL,
[gst_lst_nm] [varchar](200) NULL,
[addr_ln_1] [varchar](200) NULL,
[cty_nm] [varchar](100) NULL,
[st_cd] [varchar](100) NULL,
[addr_pstl_cd] [varchar](50) NULL,
[cntry_nm] [varchar](100) NULL,
[phn_nb] [varchar](50) NULL,
[gst_eml_addr_nm] [varchar](200) NULL,
[favorite_character] [varchar](200) NULL,
[night_day_flg] [varchar](50) NULL,
[arvl_dt] [datetime] NULL,
[dprt_dt] [datetime] NULL,
[res_nb] [varchar](50) NULL,
[destination] [varchar](20) NULL,
[wdpro_opt_in] [char](1) NULL,
[wdpro_opt_dts] [datetime] NULL,
[mobile_opt_in] [varchar](50) NULL,
[mobile_opt_dts] [datetime] NULL,
[prpty_flg] [varchar](20) NULL,
[fac_prod_cd] [varchar](50) NULL,
[afflnt_flg] [char](1) NULL,
[gst_brth_dt] [datetime] NULL,
[hhold_lfstg_seg_nm] [varchar](50) NULL,
[oldst_chld_age] [int] NULL,
[rsrt_typ] [varchar](200) NULL,
[rsrt_ctgy] [varchar](50) NULL,
[actv_dvc_in] [varchar](50) NULL,
[update_YN] [char](1) NULL,
[matching_core_primary_YN] [char](1) NULL,
[matching_primary_criteria] [varchar](100) NULL,
[matching_primary_date] [datetime] NULL,
[matching_core_secondary_YN] [char](1) NULL,
[core_arrvl_dt] [datetime] NULL,
[core_dprt_dt] [datetime] NULL,
[final_opt_in] [char](1) NULL,
[core_status] [varchar](50) NULL,
[send_email_YN] [char](1) NULL,
[email_type] [varchar](100) NULL,
[group_id] [varchar](50) NULL,
[primary_guest_YN] [char](1) NULL,
[recieve_schedule_msgs_YN] [char](1) NULL,
[wait_time_YN] [char](1) NULL,
[character_call_YN] [char](1) NULL,
[core_hshold_id] [varchar](50) NULL,
[bumped_YN] [char](1) NULL,
[bumped_type] [varchar](50) NULL,
[overlap_YN] [char](1) NULL,
[final_optout_type] [varchar](50) NULL,
[mob_num_update_YN] [char](1) NULL,
[group_update_YN] [char](1) NULL,
[end_program_YN] [char](1) NULL,
[new_registrant_indicator_YN] [char](1) NULL,
[created_date] [datetime] NULL,
[created_by] [varchar](100) NULL,
CONSTRAINT [PK_tbl_Mobile] PRIMARY KEY CLUSTERED
(
[SWID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
mobile_opt_in is used as time stamp.
May 24, 2010 at 3:24 am
Great. Now post some sample data and you're done.
Be sure to post it as INSERT statement upon the tables you just scripted.
See the article in my signature if you're in doubt.
-- Gianluca Sartori
May 24, 2010 at 3:31 am
I dont know how to do it.
May 24, 2010 at 3:38 am
May 24, 2010 at 3:45 am
You can try with the following code:
WITH messages AS
(
SELECT table_a.mobile_number,
table_b.mobile_opt_in,
Row_Num = ROW_NUMBER() OVER(PARTITION BY table_a.mobile_number ORDER BY table_b.mobile_opt_in DESC)
FROM tbl_Messages as table_a
Inner Join tbl_Mobile table_b
On table_b.SWID = table_a.SWID
)
SELECT mobile_number
FROM messages
WHERE Row_Num = 1
Regards
Sharath.Chalamgari
May 24, 2010 at 3:48 am
Try this:
;WITH messages AS (
SELECT mobile_number,
mobile_opt_in,
Row_Num = ROW_NUMBER() OVER(PARTITION BY mobile_number ORDER BY CAST(mobile_opt_in AS datetime) DESC)
FROM tbl_Messages AS A
INNER JOIN tbl_Mobile AS B
ON A.SWID = B.SWID
)
SELECT mobile_number
FROM messages
WHERE Row_Num = 1
It's not a good idea at all storing datetime values in a varchar(50) field. If possibile, fix it.
Hope this helps
-- Gianluca Sartori
May 24, 2010 at 4:00 am
I don't have proper data. My client will provide some test data tomorrow. Please try to help me out without data.
May 24, 2010 at 4:08 am
i didn't noticed that [mobile_opt_in] is varchar(50)
May 24, 2010 at 4:59 am
atikdesai (5/24/2010)
I don't have proper data. My client will provide some test data tomorrow. Please try to help me out without data.
How does this statement match with your first post?
Please help me out. its urgent.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply