select distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select distinct

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-09-03 : 13:11:59

how can I select distinct records from registration based on distinct regtype,name,regcode

but i want to return all records from the registration table that are distinct as to the above?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 13:17:20

select distinct regtype, name, regcode
from table1

But I think you really want just one distinct record per regtype, right?

As told to you many times before, please post proper sample data and expected output.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75029

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-09-03 : 13:28:31

i have in the table

id, regtype,name,address,regcode, city,state,zip

i want to return all the fields but only lookinga at distinct regtype,name,regcode as the id will never be distinct (but needs to be returned in the recordset)

please advise

Kristen
Test

22859 Posts

Posted - 2007-09-03 : 13:38:50

So for a given regtype,name,regcode do you want the MIN ID, or the MAX?, or a List of all of them, or something else?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 13:40:13

Which means I was right. Twice.

SELECT RegType, Name, Address, RegCode, City, State, Zip
FROM (
SELECT RegType, Name, Address, RegCode, City, State, Zip, ROW_NUMBER() OVER (PARTITION BY RegType, Name, RegCode ORDER BY ID DESC) AS RecID FROM Table1
) AS d
WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-09-03 : 14:28:00

what does

ROW_NUMBER() OVER (PARTITION BY RegType,) do?

i'm just trying ot understand this

also seems highly complicated to add to my query - will it slow it down?
am i better off cleaning out the table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 14:31:11

What are you really trying to do?
I don't think it is fun to play guessing games with you all the time.

ROW_NUMBER() function is explained in Books Online, which should be your best friend after more than 580 posts.

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY RegType, Name, RegCode ORDER BY ID DESC) AS RecID FROM Table1
) AS f
WHERE RecID > 1

You asked the same question in april 2007
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82746
and here in march 2007
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79015
and here in march 2006
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63270

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-09-04 : 01:25:57

thanks for your help - i looked into this more

the problem is I need to return the ID of the distinct record.
(meaning it should be the distinct id but I need that record id)

can you help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 02:43:26

quote:
Originally posted by esthera

can you help?

YES

Please see the answer I gave at 09/03/2007 : 13:40:13

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-09-04 : 02:55:18

that didn't show the id for me but I got it working with a group by

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 03:04:59

How about you ADDED the ID column to the SELECT statement as

SELECT ID, RegType, Name, Address, RegCode, City, State, Zip
FROM (
SELECT RegType, Name, Address, RegCode, City, State, Zip,
       ROW_NUMBER() OVER (PARTITION BY RegType, Name, RegCode ORDER BY ID DESC) AS RecID, ID
FROM   Table1
) AS d
WHERE  RecID = 1

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-09-04 : 03:11:14

thanks :)

any difference between using this way or grouping by and selecting the min(id)

is one better then the other?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 03:17:01

Probably yes.
The only way to check this, is to compare the two execution plans and check the {CPU, DURATION, READS, WRITES} in SQL Profiler.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-04 : 04:02:13

"the problem is I need to return the ID of the distinct record"

This bit I don't understand.

You have, say, ten records with the same regtype,name,regcode.

If you do a

SELECT DISTINCT regtype,name,regcode

you will only get one row showing. But the ten rows all have different ID. So you can either show 10 rows, including the ID, or the MIN(ID), or something else.

Please explain what you want when you say "I need to return the ID of the distinct record"

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 04:06:14

Kristen,

OP has all these columns "ID, RegType, Name, Address, RegCode, City, State, Zip"
OP want to get a DISTINCT resultset over "RegType, Name, RegCode" only, and the rest of the columns associated with the distinct part, preferably the record with the lowest ID and unique "RegType, Name, RegCode" combination.

OP has asked for this several times for over a year and ahs always got a solution.
What I don't understand is how OP does not remember the solutions, or search for them.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-04 : 04:23:10

"preferably the record with the lowest ID and unique "RegType, Name, RegCode" combination"

I didn't see the Spec for that, yet ...

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-09-04 : 05:32:59

i apologize - thanks for your help

it was the min id that i did and the group by seems to be just as efficient..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 05:41:40

You did a


SELECT regtype,name,regcode, min(id)
FROM table1
group by regtype,name,regcode

???

but that violates what you wrote before..

quote:
Originally posted by esthera

i have in the table

id, regtype,name,address,regcode, city,state,zip

i want to return all the fields but only lookinga at distinct regtype,name,regcode as the id will never be distinct (but needs to be returned in the recordset)

If you have used

select t.id, t.regtype, t.name, t.address, t.regcode, t.city, t.state, t.zip
from table1 as t
inner join (SELECT min(id) as theid FROM table1 group by regtype,name,regcode 
) as d on d.theid = t.id

it will do exactly as wished for (as the sql serve 2005 approach),
but less efficient as the execution plans and Profiler tells you.

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-09-04 : 05:49:38

SELECT regtype,name,regcode, min(id)
FROM table1
group by regtype,name,regcode

this did exactly what i wanted - it gave me the first record for each distinct record by regtype,name,regcode

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 06:05:18

Yes, but you wrote

quote:
i want to return all the fields

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources