select top 100 no dupes based on one col

[thedrones]

Hi all, I hope you are well. I have a very quick question for you please. I have a table with x rows and y columns. I need to select the top 100 rows ordered by col2 for eg. Further, I need to select only one row for a particular column value. for example, I need to select top 100 rows, order by col2 but grab only the first row where col1 is duplicated(in that order). Iam no SQL pro so is there any advice or pointers you guys could give? Thanks.

[mwasif]

For this you need ORDER BY (to order the rows) and GROUP BY (to get the distinct record). e.g.

SELECT * FROM table_name 
GROUP BY col1
ORDER BY col2 DESC
LIMIT 100

Choose one of the following in your query according to your requirement. In query I have used DESC.
ORDER BY col2 DESC = Descending order of the rows depending on the col2 value
ORDER BY col2 ASC = Ascending order of the rows depending on the col2 value

[thedrones]

hi, thanks for the advice there. I am receiveing the good old "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." with ref to col2, any thoughts on that?

[d5e5]

What database are you using? I ran something similar to the above in MySQL and didn't get the error. When I used to work with Oracle and MS-Access databases I remember getting errors like that.

If you are using MySQL as your database, do you by any chance have ONLY_FULL_GROUP_BY SQL mode enabled? See this link for the reason you shouldn't be getting this error in MySQL: http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

[teedoff]

I thought you could also use "DISTINCT" to specify no duplicates in a records.

[mwasif]

I thought you could also use "DISTINCT" to specify no duplicates in a records.

DISTINCT only works when you are SELECTing only one column and needed that column DISTINCT. But if you have multiple columns in SELECT statement but want only one of them to be distinct then you must have to rely on GROUP BY.

[teedoff]

hey thanks for explaining that mwasif. I'm just starting to learn SQL, so that helps!

[1stDAN]

Let me suggest that when querying

SELECT * FROM table_name 
GROUP BY col1
ORDER BY col2 DESC
LIMIT 100

and the * is alias for more than one column, for example the * stands for three columns col1, col2 and col3

SELECT col1, col2, col3 FROM table_name 
GROUP BY col1

then the other columns must be in group by too. If not, the grouped result is wrong. One can carefully read the manual in mysql, it says that:

When using this feature, all rows in each group should have the same values for the
columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

Oracle and Sqlserver for example do not allow to omit the other columns from the group by.

[1stDAN]

sorry, cannot extend my old post. Here is the correct query if three columns selected

SELECT col1, col2, col3 FROM table_name GROUP BY col1, col2, col3