GROUP BY to return entire row

Question

I want to return the most recent ten (complete) rows from a table, but ensuring they all have unique 'alternate_id's. If there are two in the top ten with the same alternate_id I don't care which I take, but I want one of them! I thought I'd use group by as follows:

select * 
from track_history_items 
where type='FooType' 
group by alternate_id order by created_at desc limit 10;

but this seems to be causing problems (failing to return rows with alternate_ids that are definitely in the top 10). Any ideas how I should do this properly?

* SOLUTION * (I can't post an answer as I'm a new user)

here's what I've ended up doing:

SELECT field1,
...,
max(created_at),
...,
fieldN
FROM track_history_items
where type='FooType'
group by alternate_id
order by created_at desc
limit 10

This seems to do the trick. Thought I'd post it here in case it's of use to others, or there are any mistakes with it that someone might spot!

Julio: but I need to select the whole row, not just the distinct alternate_id's — tiswas, Apr 27 '11 at 11:25
BTW adding the `max` to the select is documented to work on SQLite as mentioned at: https://stackoverflow.com/questions/17277152/sqlite-select-distinct-of-one-column-and-get-the-others/71924314#71924314 — Ciro Santilli Путлер Капут 六四事, Apr 19 '22 at 11:46

Julio Guerra · Accepted Answer · 2011-04-27T11:39:33.143

0

GROUP BY must be used with aggregate functions (like sum, avg, ...).

You can use:

SELECT
  DISTINCT alternate_id,
  field1, -- one value per alternate_id
  ...,    -- one value per alternate_id
  fieldn  -- one value per alternate_id
FROM
  track_history_items
WHERE
  type = 'FooType'
ORDER BY
  created_at DESC
LIMIT 10

This is standard SQL.

It does not mean you will necessarily unique value in your alternat_id column. You will have every combinations of {alternate_id, fieldi}.

edited Apr 27 '11 at 11:39

answered Apr 27 '11 at 11:13

Julio Guerra

5,227
7
49
69

if you put distinct first it will check for distinct _combinations_ of the columns, not just distinct alternate_id. – tiswas Apr 27 '11 at 11:31
Yes, now you have to delete the fields associated to alternate_id with more than one value and join it to another select to get additional data. – Julio Guerra Apr 27 '11 at 11:37

GROUP BY to return entire row

1 Answers1