Hello. There is a database with the structure:

  1. id
  2. name
  3. data_id

where data_id can be the same value. We have data:

  1. 1, record 1, 1
  2. 2, record 2, 5
  3. 3, record 3, 1
  4. 4, record 4, 8
  5. 5, record 5, 1
  6. 6, record 6, 1

You need to make a request to get such data:

  1. 1, record 1, 1
  2. 2, record 2, 5
  3. 4, record 4, 8

How to make such a request?

  • and disable duplicate data_id from the sample? - pavlofff
  • SELECT * FROM t1 group by data_id - vp_arth

2 answers 2

If it doesn’t matter exactly which string to get with each data_id, the following query will output the last one for each:

 SELECT * FROM t1 group by data_id

You can add a postcondition HAVING to determine this:

 SELECT * FROM t1 group by data_id having min(id)

Feeddle

  • The original work having max(id) in sqlite however ... is this feature described somewhere in the dock? - Mike
  • I think this is a shorthand for having id = min(id) , which no longer looks so original) I did not see it in the dock) - vp_arth
  • Of all the select DBMS I know if I had group by, I would give an error. MySQL and SQLite exceptions. And in MySQL having id = max (id) does not work. So this is a very beautiful feature of SQLIte - Mike
 select * from myTable where id in (select min(id) from myTable group by data_id)

sqlfiddle example

  • This answer is more scalable. It is better to use it if you ever plan to change the repository. - vp_arth