May 12, 2012 at 2:04 am
I am having this SQL that return me 5 records,all I want only Distinct recipientEmail with all other fields , can you suggest me some best way to finish this scripts.
SELECT DISTINCT recipientEmail,firstName, lastName,senderEmail,[userId],L24InvitationsID FROM (SELECT TOP 5 firstName, lastName, recipientEmail,.[userId], senderEmail, invitationDate,L24InvitationsID FROM L24Invitations [A] INNER JOIN L24Users ON [A].senderEmail = .[Email] AND EmailFlag <> 'Sucess' AND EmailFlag <> 'MailBox' AND EmailFlag <> 'Syntax' AND success=0 ORDER BY invitationDate DESC ) AS T
This is my data:
Please check attach Image
I need only Unique recipientEmail in those records!
Thanks,
Shakti
Warm Regards,
Shakti Singh Dulawat
Before printing, think about the environment
Do the impossible, and go home early.
May 12, 2012 at 2:44 am
May 12, 2012 at 6:24 am
I also attach Create SQL Of Both table
CREATE TABLE [dbo].[L24Invitations](
[L24InvitationsID] [int] IDENTITY(1,1) NOT NULL,
[senderId] [int] NOT NULL,
[senderEmail] [nvarchar](50) NOT NULL,
[recipientEmail] [nvarchar](50) NOT NULL,
[invitationDate] [smalldatetime] NOT NULL,
[success] [bit] NOT NULL,
[invitationType] [varchar](100) NULL,
[EmailFlag] [varchar](50) NULL,
CONSTRAINT [PK_L24Invitations] PRIMARY KEY CLUSTERED
(
[L24InvitationsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SQL for User Table
CREATE TABLE [dbo].[L24Users](
[userId] [int] IDENTITY(1,1) NOT NULL,
[varchar](50) NOT NULL,
[password] [varchar](20) NOT NULL,
[firstName] [nvarchar](100) NOT NULL,
[IsFirstTime] [bit] NULL,
CONSTRAINT [PK_L24Users] PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
Warm Regards,
Shakti Singh Dulawat
Before printing, think about the environment
Do the impossible, and go home early.
May 13, 2012 at 10:07 pm
Thanks for posting the DDL of the tables. But you might as well do one last thing and that is post some sample data for the tables as a string of continuous Insert statements. So that people here can copy the insert statements and insert the sample data in the tables and then work out a solution for your requirement.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply