Query in SQL Server

Question

0.00/5 (No votes)

See more:

Hello,

Can anyone help me on how to write the query for the below.

I have a table Test where records are like...

Number Email Type
100 abc@t.com P
100 aaa@as.com S
101 qws@sd.com P
102 rtt@y.com P

I need the result like below.

Number Email Type
100 abc@t.com P
101 qws@sd.com P
102 rtt@y.com P

Means, No duplicates records should exist. So here for 100, I have type P and S. I need to just display P record with the corresponding Email.

Can Anyone help on this.

Thanks.

Posted 22-Feb-12 5:32am

vasini

Add a Solution

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Costica U · Answer 1 · 2012-02-22T23:06:00

For SQL2005

SQL

select  a.Number,
        b.Email,
        b.Type
from    (
        select  distinct
                Number
        from    Test
        ) as a
cross
apply   (
        select  top 1
                Email,
                Type
        from    Test
        where   Number = a.Number
        order
        by      case
                    when Type = 'P' then 1
                    when Type = 'S' then 2
                    when Type = 'I' then 3
                    else 10
                end
        ) as b

scottgp · Answer 2 · 2012-02-22T07:08:00

Solution 2

Your requirements are not completely clear - for example, are 'P' and 'S' the only "types" there might be? Is it safe to take the alphabetical first "type"?

If so, this works:

SQL

select * from test2
 inner join (select number,  min(type) as type
             from test2
             group by number) as v1
             on test2.number = v1.number
             and test2.type = v1.type

Scott

Posted 22-Feb-12 7:08am

scottgp

Comments

vasini 22-Feb-12 13:56pm

Sorry about the requirements. I have "type" as P, S and I. If Type is P and S, I have to take record for P. If Type is S and I then take S. In all other cases take P.

Thanks for your help.

Sarvesh Kumar Gupta · Answer 3 · 2012-02-22T05:55:00

Solution 1

Hi, Use below query

SQL

Select Number,Email,Type from TableName where Number in (Select min(Number) from TableName group by Number)

It will solve your problem.

Don't forget to mark, if it is your solution.:-)

Posted 22-Feb-12 5:55am

Sarvesh Kumar Gupta

Comments

vasini 22-Feb-12 12:46pm

It did not work out. It is displaying all the records.

Sarvesh Kumar Gupta 23-Feb-12 0:42am

OK, Sorry for that, i not checked. very very sorry

Nandakishore G N · Answer 4 · 2012-02-22T17:46:00

try..this stored procedure it works..
CREATE PROCEDURE Test_Test
@G1 CHAR(10),
@G2 CHAR(10),
@G3 CHAR(10)
AS

IF( @G1='P' AND @G2='S')
SELECT * FROM dbo.test_Now WHERE Groups='P'
ELSE IF( @G2='S' AND @G3='I')
SELECT * FROM dbo.test_Now WHERE Groups='S'
ELSE
SELECT * FROM dbo.test_Now WHERE Groups='P'
GO

rajdeep kumar · Answer 5 · 2012-02-22T18:04:00

Solution 4

SELECT DISTICT(Number),Email,Type
FROM [DB0].[TABLE_NAME]

Posted 22-Feb-12 18:04pm

rajdeep kumar