1

I want to display only the latest record from usersId.

How do I create a query that would give me the latest ordersId from usersId?

The table looks like this:
The table looks like this

this the query that I use but it displays all the ordersId:

SELECT * FROM `orders` WHERE usersId=?
Dharman
  • 30,962
  • 25
  • 85
  • 135
Jim Mendoza
  • 27
  • 3
  • How do you define the "latest" row? Please share the table structure, sample input data, and the expected output. It would be even better if you added your attempts to resolve the question – Nico Haase Jan 06 '21 at 14:18
  • @NicoHaase *"the latest ordersId"* – Dharman Jan 06 '21 at 14:19
  • 1
    @Dharman does this only refer to the largest ordersId value per usersId, or could this also be linked to the orderDate column in any way? Is it safe to assume that the IDs are assigned in strictly ascending order? – Nico Haase Jan 06 '21 at 14:20

3 Answers3

1

To get the latest one record, please

  1. set order by OrdersID in descending order, and then
  2. get only 1 record by using "limit 0, 1"

So please change the

SELECT * FROM `orders` WHERE usersId=?

to

SELECT * FROM `orders` WHERE usersId=? order by ordersId desc limit 0,1
Dharman
  • 30,962
  • 25
  • 85
  • 135
Ken Lee
  • 6,985
  • 3
  • 10
  • 29
1

You can use the LIMIT clause as follows:

SELECT * FROM `orders` WHERE usersId=? 
 order by orderdate desc limit 1;

OR you can use analytical function row_number as follows:

select * from
(SELECT t.*, 
       row_number() over (partition by usersId order by orderdate desc) as rn 
  FROM `orders` t WHERE usersId=?) t
where rn = 1

row_number solution is useful when you want the latest data for multiple usersid.

Popeye
  • 35,427
  • 4
  • 10
  • 31
0

You are binding $uid before setting it to the user_id from session you should assign first & then bind.

$uid=$_SESSION['userid'];
$stmt->bind_param("s",$uid);
ajitpawarink
  • 436
  • 6
  • 14