Tags: column, database, department, distinct, guid, microsoft, mysql, onselect, oracle, query, returned, rows, running, server, sql, table, tablei, unique

DISTINCT on one column only

On Database » Microsoft SQL Server

5,710 words with 8 Comments; publish: Tue, 06 May 2008 16:36:00 GMT; (250203.13, « »)

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.

All Comments

Leave a comment...

  • 8 Comments
    • 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

      #1; Tue, 06 May 2008 16:38:00 GMT
    • SELECT guid, MIN(department)

      FROM tbl

      GROUP BY guid

      David Portas

      SQL Server MVP

      --

      #2; Tue, 06 May 2008 16:39:00 GMT
    • 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;

      #3; Tue, 06 May 2008 16:40:00 GMT
    • - 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

      #4; Tue, 06 May 2008 16:41:00 GMT
    • 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.sqlserver.todaysummary.com.acm.org> wrote in message

      news:1121876475.045441.292920.sqlserver.todaysummary.com.g47g2000cwa.googlegroups.com...

      > SELECT guid, MIN(department)

      > FROM tbl

      > GROUP BY guid

      > --

      > David Portas

      > SQL Server MVP

      > --

      >

      #5; Tue, 06 May 2008 16:42:00 GMT
    • > 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.sqlserver.todaysummary.com.earthlink.net> wrote in message

      news:1121877566.139416.161400.sqlserver.todaysummary.com.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;

      >

      #6; Tue, 06 May 2008 16:43:00 GMT
    • 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

      --

      #7; Tue, 06 May 2008 16:44:00 GMT
    • >> 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.

      #8; Tue, 06 May 2008 16:45:00 GMT