Click here to Skip to main content
14,925,975 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Team,

I am trying to retrieve a last 'Purchase date' of Students for specific Books.

Table 1:
Stud_No        Purchase_Date
1               2010-12-01
2               2021-01-05
3               2020-02-04
4               2021-02-05
8               2019-02-04



Table 2:
Stud_No        Book_Name
1               MATHS
2               MATHS
3               MATHS
4               SCIENCE
5               SCIENCE
6               SCIENCE
7               SCIENCE
8               SCIENCE


I would like to retrieve, last or recent Purchase_Date of those Stud_no with any Book_Name between tables.

Outcome:
Stud_No        Purchase_Date     Book_Name
2               2021-01-05       MATHS
4               2021-02-05       SCIENCE


In above outcome, '2021-01-05' & '2021-02-05' are the latest Purchase_Dates.

What I have tried:

I have tried query below but its showing all the combinations instead of last or recent Purchase_Date.
SQL
SELECT A.SNO, A.PDATE, B.SNO, B.BOOK
FROM TAB1 A, TAB2 B
WHERE A.SNO = B.SNO AND B.BOOK IN ('MATHS', 'SCIENCE')
Posted
Updated 3-Mar-21 20:42pm
v2
Comments
Gerry Schmitz 4-Mar-21 0:01am
   
https://www.w3resource.com/sql/aggregate-functions/Max-having.php

1 solution

Quote:
I have tried query below but its showing all the combinations instead of last or recent Purchase_Date.

SQL
SELECT A.SNO, A.PDATE, B.SNO, B.BOOK
FROM TAB1 A, TAB2 B
WHERE A.SNO = B.SNO


This is very old way to join data. Use joins[^] instead!
For further details about joins, please see: Visual Representation of SQL Joins[^]

Quote:
In above outcome, '2021-01-05' & '2021-02-05' are the latest Purchase_Dates.

No!
Latest purchase date for each student is:
Stud_No        Purchase_Date
1               2010-12-01
2               2021-01-05
3               2020-02-04
4               2021-02-05
8               2019-02-04

Unless you want to get only those student's purchases with the latest date, then you need to use MAX()[^].


So...

using variable:
SQL
DECLARE @d DATE

SELECT @d = MAX(PDate) PDATE
FROM TAB1;

SELECT A.SNO, A.PDATE, B.BOOK
FROM TAB1 A INNER JOIN TAB2 B ON A.SNO = B.SNO
WHERE A.PDATE = @d;


or

using subquery:
SQL
SELECT A.SNO, A.PDATE, B.BOOK
FROM TAB1 A INNER JOIN TAB2 B ON A.SNO = B.SNO
WHERE A.PDATE = (SELECT MAX(PDate) PDATE FROM TAB1);


or

using ranking function:
SQL
SELECT F.SNO, F.PDATE, F.BOOK
FROM
(
  SELECT A.SNO, A.PDATE, B.BOOK, ROW_NUMBER() OVER(ORDER BY A.PDATE DESC) RN
  FROM TAB1 A INNER JOIN TAB2 B ON A.SNO = B.SNO
) F
WHERE F.RN = 1;
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900