getting first rows from duplicates depending on one column MariaDB

user_10970202 0
Viewed 120

I am using MariaDB version 10.2.21

There are multiple rows with duplicate user_id(one of the column).

I want to get first occurrence of each user_id but not just user_id but all rows that is associated with first occurrence of user_id.

select 
  distinct user_id,
from 
  user_table
where 
  user_id in (a,b,c,d,e,f,....) and 
  date = '2020-02-25';

this correctly gives me all distinct, now when I try to add associated columns

EXPLAIN
select 
  distinct user_id,
  ST_X(location) as lng
from 
  user_table
where 
  user_id in (a,b,c,d,e,f,....) and 
  date = '2020-02-25';

it tells me that I will get 2+ million rows... I only have 3000 unique user_id I want to get only first occurring user_id with its associated location.

Since my database is very large I need to take full advantage of indexes otherwise it will break.

my index is [user_id, date] where user_id is 1st index and date is 2nd index.

So My question is how to get unique(first occurring = most upper row) user information by using indexes therefore my db does not break?

so if there is 

  user_id   location
    1       (123.22, 22.33)
    1       (111.22, 22.12)
    2       (155.33, 41.23)
    2       (160.41, 12.31)

I would get 

user_id   location
   1       (123.22, 22.33)
   2       (155.33, 41.23)
mariadb mysql sql
1 Answers

Use aggregation if you only one one location:

select user_id, ST_X(min(location)) as lng
from user_table
where user_id in (a,b,c,d,e,f,....) and 
      date = '2020-02-25'
group by user_id;
user_11440350
Related