CODEWITHSUNDEEP
phpmysqlsql
Muhammad Husnain Tahir
Muhammad Husnain Tahir

Reputation: 1039

SQL not Returning Desired Results

I am trying to generate result from a SQL, but i am not getting desired results. this is the result of following query

above image is the result of following query

SELECT DISTINCT 
        U.USERID, U.shopName, U.image, P.PID 
        FROM users U, products P 
        WHERE P.USERID=U.USERID 
        GROUP BY U.USERID 
        ORDER BY P.PID DESC

Now what i want to do is that there is another record for USERID 2 and 3 with PID 3 and 5 respectively, let me show you the image

Full Table

I want to retrieve latest record entered in the table but with distinct USERID

e-g i want to retrive this

userid | shopname | image | PID

3 | shop name | image | 5

4 | shop name | image | 4

2 | shop name | image | 3

any help will be appreciable.

Upvotes: 1

Views: 69

Answers (3)

Muhammad Bilal
Muhammad Bilal

Reputation: 65

select * from "table" grou by ID and order by Desc

Upvotes: 1

peschü
peschü

Reputation: 1349

I am not sure if I understood your problem, but perhaps the following query solves your problem:

SELECT U.USERID, U.shopName, U.image, P.PID FROM users U, products P WHERE P.USERID=U.USERID AND P.PID in (select min(p2.pid) from products p2 where u.userid = p2.userid)

A word of warning: it might be more efficient to use the ORDER BY trick and filter the results externally.

Upvotes: 0

Nikul
Nikul

Reputation: 1025

Use it like below, Do group by with PID.

SELECT DISTINCT U.USERID, U.shopName, U.image, P.PID FROM users U, products P WHERE P.USERID=U.USERID GROUP BY P.PID ORDER BY P.PID DESC

Upvotes: 0

Related Questions