CODEWITHSUNDEEP
sqlpostgresqlinner-joinrow-number
Léonard de Clevermate
Léonard de Clevermate

Reputation: 3

Unexpected results with ROW_NUMBER doing Inner Join

I built this SQL request,

SELECT 
      distinct c_users.id,
      c_photos.user_id,
      c_photos.pic_filename,
      c_users.user_first_name,
      c_users.user_last_name,
      c_users.description,
      c_users.curriculum_diploma_1,
      ROW_NUMBER() OVER(ORDER BY c_users.id ASC) AS rank
FROM
    c_users
INNER JOIN
    c_photos
ON
    c_users.id=c_photos.user_id
WHERE 
    lower(location_town) LIKE '%lille%' 
    AND
    (c_photos.pic_type='profile' OR c_photos.pic_type='photo')    
LIMIT
   9 
;

This is the output,

output

The rank column has some unexpected results, I want it to be 1, 2, 3, etc and not 94, 84, 25, etc. I can't figure out how to do it.

Thanks,

Leonard

Upvotes: 0

Views: 98

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you want results in a particular order, you need an order by. I would recommend:

order by c_users.id

or

order by rank

EDIT:

I'm not sure how row_number() works with distinct. I'm more comfortable with group by. In your case, though, I doubt that duplicate elimination is needed (unless a user has two photos with the same name).

SELECT u.id, p.user_id, p.pic_filename, u.user_first_name, u.user_last_name,
       u.description, u.curriculum_diploma_1,
       ROW_NUMBER() OVER (ORDER BY u.id ASC) AS rank
FROM c_users u INNER JOIN
     c_photos p
     ON u.id = p.user_id
WHERE lower(location_town) LIKE '%lille%' AND
      p.pic_type in ('profile', 'photo')
LIMIT 9 ;

You can add:

GROUP BY u.id, p.user_id, p.pic_filename, u.user_first_name, u.user_last_name,
         u.description, u.curriculum_diploma_1

If necessary.

Upvotes: 1

Sabrina Fernandez Vazzino
Sabrina Fernandez Vazzino

Reputation: 156

The distinct statement is causing this. You could try this:

SELECT *, ROW_NUMBER() OVER(ORDER BY c_users.id ASC) AS rank from (
SELECT 
      distinct c_users.id,
      c_photos.user_id,
      c_photos.pic_filename,
      c_users.user_first_name,
      c_users.user_last_name,
      c_users.description,
      c_users.curriculum_diploma_1

FROM
    c_users
INNER JOIN
    c_photos
ON
    c_users.id=c_photos.user_id
WHERE 
    lower(location_town) LIKE '%lille%' 
    AND
    (c_photos.pic_type='profile' OR c_photos.pic_type='photo')    
    ) as data
LIMIT
   9

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

with CTE as
(
SELECT 
      distinct c_users.id,
      c_photos.user_id,
      c_photos.pic_filename,
      c_users.user_first_name,
      c_users.user_last_name,
      c_users.description,
      c_users.curriculum_diploma_1,
      ROW_NUMBER() OVER(ORDER BY c_users.id ASC) AS rank
FROM
    c_users
INNER JOIN
    c_photos
ON
    c_users.id=c_photos.user_id
WHERE 
    lower(location_town) LIKE '%lille%' 
    AND
    (c_photos.pic_type='profile' OR c_photos.pic_type='photo')    
)
select *
from CTE
where rank <=9

Either package it inside a CTE/query and use the row_number, or order by your new column (as per Gordon's answer) for the limit to work as expected.

Upvotes: 0

Related Questions