How to write query using GROUP BY ? RRS feed

  • Question

  • Hi all,

    I wanna write a sql query like this

    select * from table_name group by number

    but it shows me an error ... I wanna prevent to show dupliucate rows so where is a same number in one column, it should shows only one row ... but this doesnt work ... no idea what to do

    or if this is wrong ... what else? ...


    Wednesday, June 1, 2016 6:40 AM


All replies

  • In Access to avoid duplicates you would use

    SELECT DISTINCT[ROW] * FROM table_name;

    The GROUP BY clauses requires a "fixed" column list in the SELECT, which may only consist of the columns in the GROUP BY and aggregated columns (AVG, MIN, MAX, etc.):

    SELECT group_by_columns, aggregated_columns FROM table_name GROUP by group_by_columns;

    So using the asterisk in the SELECT when having a GROUP BY is an error.

    But: Having duplicates is either result of a data model error or a data error. Both should be corrected before using DISTINCT.

    Wednesday, June 1, 2016 7:42 AM
  • select number, field1 , field1 from table

    group by number, field1 , field1

    Wednesday, June 1, 2016 8:23 AM
  • I used

    Select DISTINCT(number), name from TABLE_NAME ORDER BY number DESC;

    It shows me result ... but with duplicate rows ... I mean two rows had same number ...

    Wednesday, June 1, 2016 8:49 AM
  • I don't have said anything about DISTINCT(column).


    FROM table_name;

    Wednesday, June 1, 2016 1:30 PM
  • Hi TakeshiKitano,

    Hope you have get your solution from suggestion given by the Stefan Hoffmann.

    Here I provided some links to you which will clear the idea regarding Distinct, Distinct Row, Gropup By Clause.

    GROUP BY Clause

    Difference Between "Unique Values" (SELECT DISTINCT) and "Unique Records" (SELECT DISTINCTROW) in Microsoft Access Queries

    GROUP BY Clause (Microsoft Access SQL)

    Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 2, 2016 12:30 AM