How to write query using GROUP BY ?

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? ...

thanks

Answer 1

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.

Answer 2

select number, field1 , field1 from table

group by number, field1 , field1

Answer 3

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 ...

Answer 4

I don't have said anything about DISTINCT(column).

It's

SELECT DISTINCT[ROW]
    column_list
FROM table_name;

Answer 5

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.

Regards

Deepak 

---

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.