Displaying the most recent checkout date only once when multiple records exist

Question

0.00/5 (No votes)

See more:

Here is my data:

BookISBN	LastCheckOutDate	PatronNumber
100004	11/14/2011	950101
100005	12/18/2013	950102
100005	1/18/2013	950103
100005	4/10/2013	950104
100005	10/22/2013	950105
100005	9/17/2014	950106
100005	1/11/2016	950107
100005	12/5/2011	950108

I need the BookISBN to display only once for the most recent last checkout date.

All these fields are in one table.

Even when I query the MAX(LastCheckOutDate), I still get the multiple entries for the BookISBN.

How can I get just one entry per BookISBN>

My query is:

<pre> SELECT  INV.BookISBN,MAX(LastCheckoutDate) AS LastDate,PatronNumber, BookDesc, BookPrice, QtyCheckedOut

 FROM LibraryInventory INV LEFT JOIN Books BKS ON INV.BookISBN = B.BookISBN
 
WHERE B.BookISBN IS NULL
 AND INV.BookISBN IS NOT NULL
 
AND LastCheckoutDate < DATEADD("d", -365, CONVERT(VARCHAR(10),Getdate(),101))
 
GROUP BY INV.BookISBN,PatronNumber, BookDesc, BookPrice, QtyCheckedOut


ORDER BY INV.BookISBN

What I have tried:

SQL MSDN, Google search, basic sql query MAX()

Posted 1-Mar-17 3:51am

Member 10379103

Updated 2-Mar-17 18:09pm

Add a Solution

Comments

Peter Leow 1-Mar-17 10:01am

That LastCheckOutDate is a varchar type?

Member 10379103 1-Mar-17 10:23am

No - it is a datetime type.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Graeme_Grant · Answer 1 · 2017-03-01T03:55:00

Solution 1

Here is the keyword that you require: SQL FIRST() Function[^]

Posted 1-Mar-17 3:55am

Graeme_Grant

Comments

Member 10379103 1-Mar-17 10:25am

This is only recognized in Access - got an error that FIRST is not recognized function in SQL server.

Graeme_Grant 1-Mar-17 11:01am

If you continued reading the page, they show you alternative for other database systems. Here is an extract from that page:

SQL Server Syntax -

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;

Bryian Tan · Answer 2 · 2017-03-01T05:32:00

Can you use table common expression CTE?

SQL

;WITH temp AS (
SELECT ROW_NUMBER() OVER (PARTITION BY BookISBN
                          ORDER BY LastCheckOutDate DESC
                         ) AS myrownum
       ,*    
from book )
SELECT * FROM temp WHERE myrownum = 1

Output:

myrownum	BookISBN	LastCheckOutDate	PatronNumber
1	        100004	        2011-11-14	        950101
1	        100005	        2016-01-11	        950107

Now you can update the query to join LibraryInventory table, assuming all record in LibraryInventory are unique

Example:

SQL

;WITH temp AS (
SELECT ROW_NUMBER() OVER (PARTITION BY BookISBN
                          ORDER BY LastCheckOutDate DESC
                         ) AS myrownum
       ,*    
from book )
SELECT * FROM temp B JOIN LibraryInventory INV 
ON B.BookISBN = INV.BookISBN
WHERE myrownum = 1

RAMASWAMY EKAMBARAM · Answer 3 · 2017-03-02T18:09:00

Solution 4

select bookisbn, to_date(substr(tempcol, 1, 8), 'yyyymmdd') lastcheckoutdate, substr(tempcol, 9) patronnumber
from
(
select bookisbn, max(to_char(lastcheckoutdate, 'yyyymmdd') || patronnumber) tempcol
from libraryinventory
group by bookisbn
)
order by bookisbn

-- this will work in Oracle

Posted 2-Mar-17 18:09pm

RAMASWAMY EKAMBARAM