CODEWITHSUNDEEP
sqlsql-server
Saif Khan
Saif Khan

Reputation: 18792

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     2       12      01052008
2       234     1       5       01022008
1       123     1       5       01012008
2       444     2       3       02052008

How can I retrieve (whats a faster way) the get last issue for all products, for a specific customer? Can I have samples for both SQL Server 2000 and 2005? Please note, the table is over 500k rows.

Thanks

Upvotes: 1

Views: 978

Answers (5)

Tom H
Tom H

Reputation: 47464

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
LEFT OUTER JOIN dbo.Sales T2 ON
     T2.custid = T1.custid AND
     T2.prodid = T1.prodid AND
     T2.datesold > T1.datesold
WHERE
     T1.custid = @custid AND
     T2.custid IS NULL

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.

Upvotes: 3

Rockcoder
Rockcoder

Reputation: 8479

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

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

Upvotes: 1

Patrick Harrington
Patrick Harrington

Reputation: 47924

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.

Upvotes: 1

Ray Lu
Ray Lu

Reputation: 26658

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.

Upvotes: 0

user42092
user42092

Reputation:

Generic SQL; SQL Server's syntax shouldn't be much different:

SELECT prodid, max(issue) FROM sales WHERE custid = ? GROUP BY prodid;

Upvotes: 3

Related Questions