DISTINCT on one column only

I have a table I'm running a query on:
SELECT DISTINCT guid, Department FROM table
I only want rows with unique guid's to be returned (there are a couple rows
with identical guids, and I can't fix the real problem of having multiple
guids)
This returns rows with distinct guids and departments obviously. I tried to
modify the query to:
SELECT DISTINCT(guid), Department FROM table
Trying to get it to run the distinct on just the guid column. Still didn't
do it.
What do I need to do to get just the unique guids?
PS The query:
SELECT DISTINCT guid, FROM table
works perfectly.Based on your narrative, you seem to be struggling with a poorly chosen
identifier namely guid. In any case, DISTINCT return distinct rows from a
table, to extract distinct values from a column you will have to use an
aggregate function with a GROUP BY clause like:
SELECT MAX( guid ), department
FROM tbl
GROUP BY department ;
Anith|||SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
David Portas
SQL Server MVP
--|||The unit of work in a SELECT statement is a entire **row**, not a
**column**. The SELECT DISTINCT is for a whole row. You still think
this is "left to right, one field at a time" file system. No wonder
you would have such a poor choice of keys -- you are mimicing a record
number in a file system. You need to stop programming and get a book
on RDBMS basics.
Actually, you need to get rid of that GUID column and get a valid
relational key.
SELECT silly_guid
FROM Foobar
GROUP BY silly_guid
HAVING COUNT(*) = 1;|||- Steve - wrote:
> I have a table I'm running a query on:
> SELECT DISTINCT guid, Department FROM table
> I only want rows with unique guid's to be returned (there are a
> couple rows with identical guids, and I can't fix the real problem of
> having multiple guids)
> This returns rows with distinct guids and departments obviously. I
> tried to modify the query to:
> SELECT DISTINCT(guid), Department FROM table
> Trying to get it to run the distinct on just the guid column. Still
> didn't do it.
> What do I need to do to get just the unique guids?
> PS The query:
> SELECT DISTINCT guid, FROM table
> works perfectly.
You need to figure out which duplicate qualifies as the row you want
returned and then implement the technique that David and Anith describe.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is really close to what I want.
The only problem is that the two rows with the same guid but different
departments, isn't showing at all. I'd like one line of it to show up. (I
don't care which one)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121876475.045441.292920@.g47g2000cwa.googlegroups.com...
> SELECT guid, MIN(department)
> FROM tbl
> GROUP BY guid
> --
> David Portas
> SQL Server MVP
> --
>|||> Actually, you need to get rid of that GUID column and get a valid
> relational key.
I only get to use the data. I have no saying whatsoever in how the data is
managed. It's completley out of my control.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121877566.139416.161400@.g47g2000cwa.googlegroups.com...
> The unit of work in a SELECT statement is a entire **row**, not a
> **column**. The SELECT DISTINCT is for a whole row. You still think
> this is "left to right, one field at a time" file system. No wonder
> you would have such a poor choice of keys -- you are mimicing a record
> number in a file system. You need to stop programming and get a book
> on RDBMS basics.
>
> Actually, you need to get rid of that GUID column and get a valid
> relational key.
> SELECT silly_guid
> FROM Foobar
> GROUP BY silly_guid
> HAVING COUNT(*) = 1;
>|||What you've described isn't what I'd expect. Try the following, which
works for me:
CREATE TABLE tbl (guid UNIQUEIDENTIFIER NOT NULL, department
VARCHAR(10) NOT NULL /* PRIMARY KEY not specified */)
INSERT INTO tbl SELECT '9EF7940E-B5A9-4E81-8959-244A7CF31E5F','A'
INSERT INTO tbl SELECT '9EF7940E-B5A9-4E81-8959-244A7CF31E5F','B'
INSERT INTO tbl SELECT 'C1864626-28CE-4BE3-8171-F4E989DDF114','C'
SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
Result:
guid
-- --
9EF7940E-B5A9-4E81-8959-244A7CF31E5F A
C1864626-28CE-4BE3-8171-F4E989DDF114 C
(2 row(s) affected)
Did you do something different? Post some code to reproduce it (like
I've done) if you need more help.
Note that you could also show just the duplicated rows:
SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||>> I only get to use the data. I have no saying whatsoever in how the data
is managed. It's completley out of my control. <<
Sorry about that.
I do not drive the train
I cannot ring the bell
but let the damn thing jump the track
and see who catches Hell.