1

I have a picture table which consists for users pictures

Table Name:picture
Columns: picture_id primary key,auto increment
         user_id   INT  foreign key
         path VARCHAR(75)

user_id 1consists of 50 images, I want first 20 image path according to picture_id and count of all images of user_id 1, that is 20 Records and count = 50 with in a single query.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Rajneel Joshi
  • 131
  • 7
  • 17
  • Why not get all the records and have a running count in PHP while you process the resultset? – Chetter Hummin Mar 31 '12 at 15:30
  • I posted an answer mentioning `SQL_CALC_FOUND_ROWS` but it uses 2 queries so doesn't really answer your question. Have a look at [this question](http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count) for more information. – cmbuckley Mar 31 '12 at 15:34

1 Answers1

2

It's a little complicated, but you could do it via a subquery or a join.

One possibility would be:

select picture_id, 
  (select count(*) from pictures p1 where p1.user_id = pic.user_id) as num_pics
  from picture pic
  where pic.user_id = 1
  order by picture_id
  limit 20;
Brian Hoover
  • 7,861
  • 2
  • 28
  • 41