Details and max() in nested query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Details and max() in nested query?

Author

Topic

Cartesius
Starting Member

4 Posts

Posted - 2008-02-12 : 05:04:51

Hi:

In a regular table structure like this (--< is for 1:M):

customer --< invoice --< invoiceitem >-- product

I want to get a list of each customers ordered products, but only the latest order of product. Not the latest order/invoice date per se, but the latest order/invoice for each particular invoiceitem i.e product, with details so that I can get that IncoiceItem record. Ineffect the invoiceitem_PK .

like this:

Customer Product DateLastPurchased InvoiceItem_pk InvoiceItem_quantity

custA prodX 2006-12-02 1000 000 001 10
custA prodY 2006-11-24 1000 000 102 12

custB prodX 2006-10-29 1000 000 203 100
custB prodZ 2007-08-13 1000 000 302 3

etc.

I can easily get a list of the three first columns by using a GROUP BY clause - and in my data the first three columns actually completely defines an invoiceItem row - so I have tried to use this output but when I try to join this table to a query I never get it right.

Any help would be much appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 05:08:38

You can form a dervived table out of this query and use it to join to other queries.
like.
SELECT * FROM
(your current group by query) d
join other queries q1
on d.field=q1.field
.....
if you want exact solution, please post your other queries and also sample data from tables.

Cartesius
Starting Member

4 Posts

Posted - 2008-02-12 : 08:02:50

Thanks for the swift reply. I have tried that in several combinations, but not gotten it to work, unfortunately. I am doing something wrong somewere. The only thing that gets the right rows, is the below, but then I get no details, of course.

Here is the detailed info you requested:

The grouped query is:

select cu.customer_pk, ii.product_fk, max(i.invoicedatetime) from customer cu, invoice i, invoiceitem ii, product p where cu.customer_pk = i.customer_fk AND i.invoice_pk = ii.invoice_fk and ii.product_fk = p.product_pk group by cu.customer_pk, ii.product_fk;

Tables are:

create table "DBADMIN"."customer"
(
"Customer_pk" int,
"ContactPerson" varchar(100),
"OrgName" varchar(50) not null collate CASE_INSENSITIVE,
"ModDateTime" timestamp not null,
_C0000001241 primary key ("Customer_pk"),
);

create table "DBADMIN"."invoice"
(
"Invoice_pk" int,
"Customer_fk" int not null,
"InvoiceDateTime" timestamp,
"InvoiceNumber" int,
"OurContact" varchar(100),
"ModDateTime" timestamp not null,
_C0000001284 primary key ("Invoice_pk"),
_C0000001285 foreign key ("Customer_fk") references "customer" ("Customer_pk") deferrable,
);
create table "DBADMIN"."invoiceitem"
(
"Invoiceitem_pk" int,
"invoice_fk" int not null,
"product_fk" int not null,
"NumberOfItems" int,
"RebateOnPrice" int,
"ModDateTime" timestamp not null,
_C0000001302 primary key ("Invoiceitem_pk"),
_C0000001303 foreign key ("invoice_fk") references "invoice" ("Invoice_pk") deferrable,
);

create table "DBADMIN"."product"
(
"Product_pk" int,
"ProductName" varchar(100),
"ProductGroup" varchar(100),
"ProductPrice" int,
"ModDateTime" timestamp not null,
_C0000001269 primary key ("Product_pk"),
);

Sample data:

Customer
Customer_pk contactperson orgname moddatetime
1000001 John Doe Widgets, Inc 10 feb 2008 22.26
1000002 John Smith ACME, inc. 11 feb 2008 02.41

Product
product_pk productnamen productgroup productprice moddatetime
1000003 Pencils Office Supplies 10 11 feb 2008 01.48
1000005 Paper, Copier, A4, White, 500 pcs Office Supplies 50 11 feb 2008 01.49
1000006 Computer Mouse Computer Equiptment 256 11 feb 2008 01.50

Invoice
invoice_pk customer_fk invoicedatetime invoicenumber ourcontact moddatetime
1000001 1000001 2 feb 2008 00.00 1 OC 11 feb 2008 01.13
1000003 1000001 6 feb 2008 00.00 2 OC 11 feb 2008 01.36
1000004 1000001 7 feb 2008 00.00 3 FV 11 feb 2008 02.38
1000005 1000002 10 feb 2008 00.00 5 gg 11 feb 2008 15.39
1000006 1000001 11 feb 2008 00.00 6 oc 11 feb 2008 15.41
1000007 1000001 11 feb 2008 00.00 7 OC 11 feb 2008 15.43

InvoiceItem
invoiceitem_pk invoice_fk product_fk numberofitems rebateonprice moddatetime
1000007 1000001 1000001 10 0 11 feb 2008 01.35
1000008 1000001 1000001 12 0 11 feb 2008 01.35
1000010 1000001 1000001 12 0 11 feb 2008 01.13
1000012 1000001 1000003 12 0 11 feb 2008 02.37
1000013 1000003 1000005 3 0 11 feb 2008 02.38
1000014 1000004 1000006 6 0 11 feb 2008 02.39
1000015 1000005 1000003 100 20 11 feb 2008 15.39
1000016 1000006 1000003 150 30 11 feb 2008 15.40
1000017 1000007 1000006 2 0 11 feb 2008 15.43

TIA!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 09:31:43

Try like this. remember to put only reqd fields in place of *

SELECT *
FROM customer c
INNER JOIN invoice i
ON i.Customer_fk=c.Customer_pk
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY invoice_fk,product_fk ORDER BY moddatetime DESC)AS RowNo,
invoiceitem_pk,invoice_fk,
product_fk,numberofitems,rebateonprice,moddatetime
FROM invoiceitem)it
ON it.invoice_fk=i.invoice_pk
AND it.RowNo=1
INNER JOIN product p
ON it.product_fk=p.product_pk

Cartesius
Starting Member

4 Posts

Posted - 2008-02-12 : 10:20:34

Hello, visakh:

I am very new to SQL Server, so my understanding of the T-SQL is limited, and I will also need to use other SQL databases, so is there a way to express this in more regular (SQL92) fashion, without the T-SQL specific commands (row-number(), partition by)?

Also: My main objective here is to refer to the InvoiceDateTime as the indicator of last order made of a product. This complicates things since there is no direct relation between product and InvoiceDateTime.

Best regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-12 : 11:08:55

Sorry i'm not much familiar with SQL92 syntax. I started my career with T-SQL and never had a chance to move from it.This is all what i could alter:-

SELECT c.ContactPerson AS Customer, p.ProductGroup AS product,
it.InvoiceDateTime AS DateLastPurchased,
it.InvoiceItem_pk,it.numberofitems AS InvoiceItem_quantity
FROM customer c
INNER JOIN invoice i
ON i.Customer_fk=c.Customer_pk
INNER JOIN invoiceitem it
ON it.invoice_fk=i.invoice_pk
INNER JOIN (SELECT MAX(invoiceitem_pk) AS           maxinvoice,invoice_fk,product_fk
            FROM invoiceitem
            GROUP BY invoice_fk product_fk)MaxProd
ON MaxProd.maxinvoice=it.invoiceitem_pk 
INNER JOIN product p
ON it.product_fk=p.product_pk

Cartesius
Starting Member

4 Posts

Posted - 2008-02-12 : 11:19:59

Thanks, I will try this!

Best regards and thanks a million.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources