Select distinct values from 1 column

I want to select distinct values from only one column (the BoekingPlaatsId column) with this query:

SELECT MAX(BoekingPlaatsId), BewonerId, Naam, VoorNaam
FROM table
GROUP BY BewonerId, Naam, VoorNaam

How do I do that in SQL Server?

  • Error occurred while connecting to SQL Server 2012: Cannot open user database. Login failed. (Microsoft SQL Server, Error: 4064)
  • How Clear, Deselect or Uncheck Dynamic Radio Buttons List?(ASP.NET, C#)
  • SQL complicated recursive CTE
  • Comma separated list in SQL
  • Spit graphical query plans as jpegs?
  • replacing one character with another when not next to a certain other character
  • 9 Solutions collect form web for “Select distinct values from 1 column”

    DISTINCT should work if you just want the user names:

    SELECT DISTINCT BewonerId, Naam, Voornaam
    FROM TBL
    

    but if you need the minimum ID values, group by the names…

    SELECT MIN(BoekingPlaatsId), MIN(BewonerId), Naam, Voornaam
    FROM TBL
    GROUP BY Naam, Voornaam
    

    I think you should be able to use

    SELECT DISTINCT BewonerId, Naam, VoorNaam
    

    You can’t add BoekingPlaatsId, because:

    • DISTINCT looks for unique rows
    • You need to specify what BoekingPlaatsId value you want
      (In case of Jan Janssens, do you want BoekingPlaatsId 1 or 2?)

    What also works is this:

    SELECT MAX(BoekingPlaatsId), BewonerId, Naam, VoorNaam
    FROM ...
    GROUP BY BewonerId, Naam, VoorNaam
    

    I don’t do alot of this so i’m not 100% certain of the syntax so you may need to tweak it slightly, google rank over and partition. Try this…

    SELECT 
        *,
        RANK() OVER(PARTITION BY Naam  order by Naam ) as Rank
    FROM
        TABLE
    WHERE 
        Rank = 1
    

    This is overkill for a 4 column table, but if you have a fairly denormalised table with alot of columns, this approach is invaluable for select distinct on 1 column.

    I think what you’re looking for is something like this:

    select distinct column1 from table1 where column2 = (select distinct column2 from table1)
    
    select Naam, Voornaam, min(BewonerId), min(BoekingPlaatsId) from tableName
    group by Naam, Voornaam
    

    just group By those 2 columns

      Select Min(BoekingPlaatsId), Min(bewonerId), naam, voornaam
      from table
      group By naam, voornaam
    

    This is how you can select from a table with only unique values for your column:

    CREATE VIEW [yourSchema].[v_ViewOfYourTable] AS
    WITH DistinctBoekingPlaats AS
    (
        SELECT [BewonerId], 
               [Naam],
               [VoorNaam],
               [BoekingPlaatsId],
               ROW_NUMBER() OVER(PARTITION BY [BoekingPlaatsId] ORDER BY DESC) AS 'RowNum'
        FROM [yourSchema].[v_ViewOfYourTable]
    )
    SELECT * 
    FROM DistinctProfileNames
    WHERE RowNum = 1
    --if you would like to apply group by you can do it in this bottom select clause but you don't need it to gather distinct values 
    

    You don’t need group by to accomplish this.

    I ran into a similar problem and for me the solution was using the GROUP BY clause.
    So basically, I grouped all the blogs with same title as one group.

    Syntax:

    SELECT post_title, post_link
    FROM blogs
    WHERE [ conditions ]
    GROUP BY post_title
    ORDER BY post_title;
    

    Maybe you are grouping multiple columns

    Sounds like you want something like

    select distinct(BewonerId), Naam, Voornaam from table_name
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.