SQL Query Issue

[Nayan Ambaliya]

Dear all, I have this sort of weird question in terms of sql data and query. Consider the dataset below. I have this results in to a table variable (SQL 2008)

EmailAddress ID

abc@test.com 1000
def@test.com 1001
ppp@test.com 1002
iii@test.com 1002
ttt@test.com 1000

Now, what I want out of this table variable is the below result

EmailAddress ID

abc@test.com 1000
def@test.com 1001
ppp@test.com 1002

What I want is to have first available unique ID and their email address. Can anyone please write a query or direct me. Thanks.

[Mycroft Holmes]

There are a number of solutions to this one. Use a sub query select the top 1 from table order by ID Or you can use the Row_Number() and partition grouped and ordered by the ID and an outer query that selected row 1 for each ID. There is also a CTE solution I have seen posted but don't know the details.

Never underestimate the power of human stupidity RAH

[vvashishta]

You can do this by using Row_Number functionality with 'Partition By' on ID Column. Then put in Where Claue with condition as RowNum =1.

- Happy Coding - Vishal Vashishta

[Lost User]

SELECT ID, MIN(EmailAddress)
FROM TEST
GROUP BY ID

Bastard Programmer from Hell :suss:

[Pete OHanlon]

Are you sure you've got that query right?

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

[Lost User]

Is there are reason that you want me to doubt the post?

Bastard Programmer from Hell :suss:

[Pete OHanlon]

Look at the field in the min as a hint.

*pre-emptive celebratory nipple tassle jiggle* - Sean Ewington

"Mind bleach! Send me mind bleach!" - Nagy Vilmos

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

[Lost User]

I did. He wants the ID's unique, and the first mailadress for that ID. Did I miss something? :)

Bastard Programmer from Hell :suss: