14,972,750 members
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
Gerry Schmitz 4-Mar-21 0:01am

https://www.w3resource.com/sql/aggregate-functions/Max-having.php

## Solution 1

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