locked
Unique Records RRS feed

  • Question

  • Hi:

    I have the below 11 records in a table
    Date              User   Place1 Place2
    2014/01/13 AACVQM BOM BRU
    2014/01/13 AACVQM BOM BRU
    2014/01/13 AACVQM BOM BRU
    2014/01/13 AACVQM BOM BRU
    2014/01/13 AACVQM EWR BRU
    2014/01/13 AACVQM EWR BRU
    2014/01/13 AACVQM EWR BRU
    2014/01/13 AACVQM EWR BRU
    2014/01/13 AACVQM BRU EWR
    2014/01/13 AACVQM BRU EWR
    2014/01/13 AACVQM BRU EWR

    I want to have distinct vales ie only 3 records
    2014/01/13 AACVQM BOM BRU
    2014/01/13 AACVQM EWR BRU
    2014/01/13 AACVQM BRU EWR

    How can I do this, please help

    Thursday, March 20, 2014 9:41 AM

Answers

All replies

  • sorry cannot test it right now but simple DISTINCT clause does not work?

    SELECT DISTINCT Date,User,Place1, Place2

    FROM tbl


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by SQLZealots Thursday, March 20, 2014 9:45 AM
    • Marked as answer by Elvis Long Monday, March 31, 2014 8:34 AM
    Thursday, March 20, 2014 9:44 AM
    Answerer
  • May be just another method:

    ;With cte as (

    SELECT Date,User,Place1, Place2, Row_Number()Over(partition by  Date,User,Place1, Place2 Order by date asc) Rn

    From Tablename)

    Select * From cte where rn=1

    • Marked as answer by Elvis Long Monday, March 31, 2014 8:34 AM
    Thursday, March 20, 2014 9:47 AM
  • Refer this,

    declare  @temp table (Date datetime, [User] varchar(10),Place1 char(3) , Place2 char(3))
    insert into @temp values ('2014/01/13','AACVQM','BOM','BRU')
    insert into @temp values ('2014/01/13','AACVQM','BOM','BRU')
    insert into @temp values ('2014/01/13','AACVQM','BOM','BRU')
    insert into @temp values ('2014/01/13','AACVQM','BOM','BRU')
    insert into @temp values ('2014/01/13','AACVQM','EWR','BRU')
    insert into @temp values ('2014/01/13','AACVQM','EWR','BRU')
    insert into @temp values ('2014/01/13','AACVQM','EWR','BRU')
    insert into @temp values ('2014/01/13','AACVQM','EWR','BRU')
    insert into @temp values ('2014/01/13','AACVQM','BRU','EWR')
    insert into @temp values ('2014/01/13','AACVQM','BRU','EWR')
    insert into @temp values ('2014/01/13','AACVQM','BRU','EWR')
    select Date, [User],Place1 , place2 from (
    select *,row_number() over (partition by Place1,[user],Place2,Date order by Place1,[user],Place2,Date) rn from @temp
    ) x
    where rn = 1


    Regards, RSingh

    • Marked as answer by Elvis Long Monday, March 31, 2014 8:34 AM
    Thursday, March 20, 2014 9:48 AM
  • With the given data, you can use either distinct or group by:

    SELECT DISTINCT Date, User, Place1, Place2 FROM [TABLE]

    OR

    SELECT * FROM [TABLE] GROUP BY Date, User, Place1, Place2

    • Marked as answer by Elvis Long Monday, March 31, 2014 8:34 AM
    Sunday, March 23, 2014 12:50 PM
  • It should be a simple usage of distinct in your select unless you want to select any more columns in output with unqiue values within the group.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, March 23, 2014 6:33 PM
  • Select * from table group by...

    it's the most correct way

    Sunday, March 23, 2014 9:01 PM
    • Marked as answer by Elvis Long Monday, March 31, 2014 8:34 AM
    Monday, March 24, 2014 2:52 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. You got that wrong, too!  Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Rows are not records. Table with keys have no dups. Your place_1 and place_2 look like a repeated group. Why do you think this is how to write SQL? What class or book gave you this idea? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, March 24, 2014 3:44 AM