Clever Geek Handbook

SQL Query Advice - Last Element

I have a spreadsheet where I store customer sales (in periodicals such as a newspaper). The product is stored by release. 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 get (which is a faster way) get the latest issue for all products, for a specific customer? Can I have samples for SQL Server 2000 and 2005? Please note that the table contains more than 500K rows.

thank

+1


source to share


5 answers


Assuming "last" is specified by date (not number), this method is usually pretty quick, assuming decent indices:

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 a laptop could possibly handle without issue, let alone a real server, so I would avoid denormalizing the database for "performance". Don't add extra service, inaccuracy and most headaches by tracking "last sold" elsewhere.

EDIT: I forgot to mention ... this doesn't address specific cases where two questions have the same exact ending. You may need to customize it based on your business rules for this situation.

+3


source


Generic SQL; The SQL Server syntax shouldn't be very different:



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


source


Is this a new project? If so, I would be wary of setting up your database like that and reading a little about normalization, so 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 would need to get to know your database better in order to come up with a better schema, but it looks like you are creating too many things in a flat table that will cause a lot of problems down the road.

+1


source


If you're looking for the most recent sale by date, you might want to:

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
+1


source


Query for an existing growing history table is too slow!

We strongly recommend that you create a new table, tblCustomerSalesLatest, which stores the latest issue data for each customer. and select from there.

0


source






More articles:


All Articles