Sql Query Advice - Most Recent Item

I have a table where I store customer sales (on periodicals, like newspaper) data. The product is stored by issue. Example custid prodid issue qty datesold 1 123

Solution 1:

Assuming that "latest" is determined by date (rather than by issue number), this method is usually pretty fast, assuming decent indexes:

SELECT
     T1.prodid,
     T1.issue
FROM
     Sales T1
LEFTOUTERJOIN dbo.Sales T2 ON
     T2.custid = T1.custid AND
     T2.prodid = T1.prodid AND
     T2.datesold > T1.datesold
WHERE
     T1.custid =@custidAND
     T2.custid ISNULL

Handling 500k rows is something that a laptop can probably handle without trouble, let alone a real server, so I'd stay clear of denormalizing your database for "performance". Don't add extra maintenance, inaccuracy, and most of all headaches by tracking a "last sold" somewhere else.

EDIT: I forgot to mention... this doesn't specifically handle cases where two issues have the same exact datesold. You might need to tweak it based on your business rules for that situation.

Solution 2:

Is this a new project? If so, I would be wary of setting up your database like this and read up a bit on normalization, so that you might end up with something like this:

CustID LastName FirstName
------ -------- ---------
1      Woman    Test
2      Man      Test

ProdID ProdName
------ --------
123    NY Times
234    Boston Globe

ProdID IssueID PublishDate
------ ------- -----------
123    1       12/05/2008
123    2       12/06/2008

CustID OrderID OrderDate
------ ------- ---------
1      1       12/04/2008

OrderID ProdID IssueID Quantity
------- ------ ------- --------
1       123    1       5
2       123    2       12

I'd have to know your database better to come up with a better schema, but it sound like you're building too many things into a flat table, which will cause lots of issues down the road.

Solution 3:

If you're looking for most recent sale by date maybe that's what you need:

SELECT prodid, issue
  FROM Sales 
WHERE custid =@custidAND datesold =SELECTMAX(datesold) 
                       FROM Sales s 
                      WHERE s.prodid = Sales.prodid
                         AND s.issue = Sales.issue
                        AND s.custid =@custid

Solution 4:

To query on existing growing historical table is way too slow!

Strongly suggest you create a new table tblCustomerSalesLatest which stores the last issue data of each customer. and select from there.