CODEWITHSUNDEEP
sqlite
Jon
Jon

Reputation: 345

Using DISTINCT on the second select variable

I have the following code that works:

   select distinct count(b), a from test group by a order by count(b) DESC limit 10;

But I'd like the output to actually display a, count(b) instead (switching the order). Unfortunately 

   select a, distinct count(b) from test group by a order by count(b) DESC limit 10;

does not work. Small question I know, it's just a pain to have to keep changing the order in another program.

Upvotes: 2

Views: 585

Answers (1)

Dany Y
Dany Y

Reputation: 7031

Actually you have to understand that select distinct count(b), a means DISTINCT of count(b), a and not just count(b)

That is why the second one doesn't work, you can't have distinct on a subset of columns.

To understand it more : DISTINCT gives one row when all the values of the rows are the same, if you want to do it on just count(b), and 2 rows have the same value for count(b), which value of a should be shown ??

This is why it is impossible to have distinct on a subset of columns

To get one value for each count(b) and any value of a I'm not sure but you can try this :

select max(a) from (select distinct count(b) from test group by a order by count(b) DESC ) GROUP BY count(b)

Upvotes: 4

Related Questions