How to display 5 records each

jemz · April 13, 2015, 5:49pm

I need some help please, I have this sql statement to display the latest records in each device_id,
but how do I display 5 records in each device_id ?
example 5 records for the 1010,5 records for 1020 and 5 records for 1030

putting LIMIT after where clause is not working.how do I change the sql satement. ?

 SELECT dv.device_id AS 'devid',dv.dateTime_created AS 'thetime',userdev.device_name AS 'devname',dv.id
       FROM user_devices userdev
       INNER JOIN
             (
                 SELECT device_id, MAX(id) AS 'latest'
                 FROM device_locations
                 GROUP BY device_id
              ) AS d ON d.device_id = userdev.device_id
       INNER JOIN
       device_locations dv ON dv.device_id = d.device_id AND dv.id = d.latest
       WHERE dv.device_id IN (1010,1020,1030) AND userdev.user_id='1'

Here is the results I want,…I apologize for not supplying the date in the thetime column,but I want to display the date in descening order.

 devid              thetime             devname   
  
  1010                                   sectorA         
  
  1010                                    sectorA 
  
  1010                                    sectorA 
  
  1010                                    sectorA 
  
  1010                                    sectorA 
  
  1020                                    sectorB 
  
  1020                                    sectorB
  
  1020                                    sectorB
  
  1020                                    sectorB
  
  1020                                    sectorB
  
  1030                                    sectorC
  
  1030                                      sectorC            
  
  1030                                    sectorC
  
  1030                                    sectorC
      
  1030                                    sectorC

here is my demo

Thank you in advance.

DaveMaxwell · April 13, 2015, 6:20pm

OK, I’m basing my response from this post: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

My syntax might be off, but should point you in the right direction:

SELECT dv.device_id AS 'devid'
     , dv.dateTime_created AS 'thetime'
     , userdev.device_name AS 'devname'
     , dv.id
  FROM user_devices userdev
 INNER JOIN (SELECT id
                  , device_id
                  , dateTime_created
               FROM device_locations l
              WHERE (SELECT COUNT(*)
		       FROM device_locations dl
		      WHERE dl.device_id = l.device_id
		        AND dl.id <= l.id) <= 5) dv ON dv.device_id = d.device_id
  WHERE dv.device_id IN (1010, 1020, 1030)
    AND userdev.user_id = '1'

r937 · April 13, 2015, 6:38pm

dave, your query produces an error “unknown column d.device_id”

here’s a query that does work (i tested it) –

SELECT userdev.device_name AS devname , dev_loc.id , dev_loc.device_id AS devid , dev_loc.dateTime_created AS thetime FROM user_devices AS userdev INNER JOIN device_locations AS dev_loc ON dev_loc.device_id = userdev.device_id WHERE userdev.device_id IN (1010,1020,1030) AND userdev.user_id = 1 AND 5 > ( SELECT COUNT(*) FROM device_locations WHERE device_id = dev_loc.device_id AND dateTime_created > dev_loc.dateTime_created )
the results are:

id devid thetime devname 1 1010 2015-02-04 19:14:53 sectorA 2 1010 2015-02-04 18:14:53 sectorA 3 1010 2015-02-04 17:14:53 sectorA 4 1020 2015-02-04 17:14:52 sectorB 5 1020 2015-02-04 17:14:51 sectorB 6 1020 2015-01-04 17:14:50 sectorB 8 1020 2015-01-04 17:14:50 sectorB 10 1020 2015-01-04 17:14:50 sectorB 7 1020 2015-01-04 17:14:50 sectorB 9 1020 2015-01-04 17:14:50 sectorB 17 1030 2015-03-04 21:14:51 sectorC 16 1030 2015-03-04 21:14:50 sectorC 15 1030 2015-03-04 20:14:50 sectorC 14 1030 2015-03-04 09:14:50 sectorC 13 1030 2015-02-04 17:14:50 sectorC 12 1030 2015-02-04 17:14:50 sectorC
note that there are more than 5 rows returned when there are ties

the test data contains several duplicate datetime values

DaveMaxwell · April 13, 2015, 6:41pm

Show-off

The d. should have been userdev.

r937 · April 13, 2015, 6:45pm

[quote=“DaveMaxwell, post:4, topic:120116”]The d. should have been userdev.
[/quote]

okay, i tried that, and i’m not happy with what your query produces –

devid thetime devname id 1010 2015-02-04 19:14:53 sectorA 1 1010 2015-02-04 18:14:53 sectorA 2 1010 2015-02-04 17:14:53 sectorA 3 1020 2015-01-04 17:14:50 sectorB 7 1020 2015-02-04 17:14:52 sectorB 4 1020 2015-01-04 17:14:50 sectorB 8 1020 2015-02-04 17:14:51 sectorB 5 1020 2015-01-04 17:14:50 sectorB 6 1030 2015-03-04 20:14:50 sectorC 15 1030 2015-02-04 17:14:50 sectorC 12 1030 2015-02-04 17:14:50 sectorC 13 1030 2015-03-04 09:14:50 sectorC 14 1030 2015-01-04 17:14:50 sectorC 11

you have somehow managed to select exactly 5 rows for each device_id, despite ties

it’s a subtle point, but i think your query is wrong

r937 · April 13, 2015, 6:49pm

okay, i figured out where your query went wrong, @DaveMaxwell

your criterion for ranking was based on id, not datetime

in this case, increasing id values also just happen to correspond to increasing datetime values, but this is never a safe assumption when dealing with auto_increments

jemz · April 17, 2015, 7:09pm

@r937, @DaveMaxwell,

Thank you for helping me…I tried both codes but I am having problem,slow in query maybe due to my indexing.

@DaveMaxwell, I have this query result
/* Affected rows: 0 Found rows: 15 Warnings: 0 Duration for 1 query: 39.250 sec. */

@r937,
/* Affected rows: 0 Found rows: 15 Warnings: 0 Duration for 1 query: 49.860 sec. */

Do I need to change my indexes with these table ?

Thank you in advance.

r937 · April 17, 2015, 7:48pm

[quote=“jemz, post:7, topic:120116, full:true”]Do I need to change my indexes with these table ?
[/quote]
probably

please do a SHOW CREATE TABLE for each table so we can see the indexes

jemz · April 18, 2015, 2:38pm

Here it is, by the way if we made changes on these indexes, what about my other queries that uses these table are they will going affected?

Thank you in advance.

CREATE TABLE `device_locations` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `device_id` varchar(20) CHARACTER SET latin1 NOT NULL,
  `latitude` varchar(15) CHARACTER SET latin1 NOT NULL,
  `longitude` varchar(15) CHARACTER SET latin1 NOT NULL,
  `dateTime_created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`,`dateTime_created`)
) ENGINE=InnoDB AUTO_INCREMENT=15450 DEFAULT CHARSET=utf8



CREATE TABLE `user_devices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `device_id` varchar(20) CHARACTER SET latin1 NOT NULL,
  `dateTime_added` datetime NOT NULL,
  `device_type` int(11) NOT NULL,
  `device_name` varchar(20) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `device_id` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8

r937 · April 18, 2015, 3:40pm

ALTER TABLE user_devices ADD INDEX user_device ( user_id, device_id )

your other queries will be fine

jemz · April 18, 2015, 4:21pm

After adding index

CREATE TABLE `user_devices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `device_id` varchar(20) CHARACTER SET latin1 NOT NULL,
  `dateTime_added` datetime NOT NULL,
  `device_type` int(11) NOT NULL,
  `device_name` varchar(20) CHARACTER SET latin1 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `device_id` (`device_id`),
  KEY `user_device` (`user_id`,`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8

/* Affected rows: 0 Found rows: 15 Warnings: 0 Duration for 1 query: 49.891 sec. */

Thank you in advance

r937 · April 18, 2015, 4:33pm

well, all i can suggest now is to do an EXPLAIN, although i’m not sure i’m going to be able to help you interpret it

just for laughs, remove the last WHERE condition (the one that restricts to 5 rows) and i’ll bet the query suddenly becomes lightening fast…

jemz · April 18, 2015, 4:51pm

Yeah,It lightening fast if I remove to restrict to 5 rows

"id",    "select_type",                                  "table",                     "type",        "possible_keys",                              "key",                  "key_len",            "ref",                       "rows",           "Extra"
"1",    "PRIMARY",                                     "userdev",                   "ref",        "user_id,device_id,user_device",        "user_id",                  "4",               "const",                          "3",            "Using where"
"1",    "PRIMARY",                                     "dev_loc",                   "ref",        "device_id",                                     "device_id",                "22",             "userdev.device_id",        "2568",        "Using where; Using index"
"2",    "DEPENDENT SUBQUERY",             "device_locations",       "ref",        "device_id",                                    "device_id",                "22",            "dev_loc.device_id",        "2568",        "Using where; Using index"

Maybe restricting 5 rows causes it too slow.
I apologize I don’t know how to format the csv.I tried to indent but still it will not follow.

r937 · April 18, 2015, 5:17pm

id select_type        table            type possible_keys                 key       key_len ref                rows  Extra
-- ------------------ ---------------- ---- ----------------------------- -------   ------- -----------------  ----  ------------------------
 1 PRIMARY            userdev          ref  user_id,device_id,user_device user_id       4   const                 3  Using where
 1 PRIMARY            dev_loc          ref  device_id                     device_id    22   userdev.device_id  2568  Using where; Using index
 2 DEPENDENT SUBQUERY device_locations ref  device_id                     device_id    22   dev_loc.device_id  2568  Using where; Using index

this looks okay to me… it’s using the indexes

jemz · April 18, 2015, 5:22pm

Thank you

system · July 19, 2015, 12:37am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.