How to retrieve a distinct row using one column as criteria?
in [Programming SQL]
|
From: "Edgard L. Riba" elriba at rimith dot on 24 Jun 2010 12:11 Hi, I have a table like the following columns: Warehouse INTEGER NOT NULL, sku INTEGER NOT NULL, qtyOnHand DECIMAL(9,4), DateLastPurchase DATE, SupplierLastPurchase INTEGER Primary key is Warehouse,Sku It actually has more columns, but these serve for the purpose of an example. I want to select the LAST purchase date for each SKU. In other words, one row for each SKU, selecting the MAX(DateLastPurchase) So if the table has Warehouse Sku DateLastPuchase SupplierLastPurchase 1 1 '2010-01-15' 5 2 1 '2010-01-12' 4 3 1 '2010-01-10' 5 1 2 '2010-01-10' 4 2 2 '2010-01-10' 4 3 2 '2010-01-12' 3 Would return Warehouse Sku DateLastPuchase SupplierLastPurchase 1 1 '2010-01-15' 5 3 2 '2010-01-12' 3 In other words, what was the last purchase for each item irrespective of the warehouse. How would I do this on MSSQL 2000? Thanks, Edgard
From: Dan on 24 Jun 2010 12:33 "Edgard L. Riba" <elriba at rimith dot com> wrote in message news:#RPKff7ELHA.5668(a)TK2MSFTNGP04.phx.gbl... > Hi, > > I have a table like the following columns: > > Warehouse INTEGER NOT NULL, > sku INTEGER NOT NULL, > qtyOnHand DECIMAL(9,4), > DateLastPurchase DATE, > SupplierLastPurchase INTEGER > > Primary key is Warehouse,Sku > > It actually has more columns, but these serve for the purpose of an > example. > > I want to select the LAST purchase date for each SKU. In other words, > one row for each SKU, selecting the MAX(DateLastPurchase) > > So if the table has > > Warehouse Sku DateLastPuchase SupplierLastPurchase > 1 1 '2010-01-15' 5 > 2 1 '2010-01-12' 4 > 3 1 '2010-01-10' 5 > 1 2 '2010-01-10' 4 > 2 2 '2010-01-10' 4 > 3 2 '2010-01-12' 3 > > > Would return > Warehouse Sku DateLastPuchase SupplierLastPurchase > 1 1 '2010-01-15' 5 > 3 2 '2010-01-12' 3 > > > In other words, what was the last purchase for each item irrespective of > the > warehouse. > > How would I do this on MSSQL 2000? > > > Thanks, > Edgard > > > And what happens if you have 2 different SupplierLastPurchase values on the same latest date? Which one do you want to see? Highest? Lowest? Average? Sum? Something else? -- Dan
From: --CELKO-- on 24 Jun 2010 14:00 CREATE TABLE Inventory_Report (warehouse_nbr INTEGER NOT NULL, sku INTEGER NOT NULL, PRIMARY KEY (warehouse_nbr, sku), onhand_qty DECIMAL(9,4) NOT NULL, last_purchase_date DATE DEFAULR CURRENT_DATE NOT NULL, last_purchase_supplier_id INTEGER NOT NULL); Since SKU is a "Stock Keeping Unit", how can you have decimal places for the quantity? Can I assume that you do not want NULLs? >> I want to select the LAST purchase date for each SKU. In other words, one row for each SKU, selecting the MAX(DateLastPurchase) << Here is one way: WITH Max_SKU_Dates AS (SELECT sku, MAX(last_purchase_date) FROM Inventory_Report GROUP BY sku) SELECT R.warehouse_nbr, R.sku, R.onhand_qty, R.last_purchase_date, R.last_purchase_supplier_id FROM Inventory_Report AS R, Max_SKU_Dates AS L WHERE R.sku = L.sku AND R.last_purchase_date = L.last_purchase_date; This will preserve ties.
From: "Edgard L. Riba" elriba at rimith dot on 24 Jun 2010 14:02 Hi Dan, Thanks for looking into this. In this particular case it doesn't matter. I just need to select at least one supplier on the last date. Edgard > > "Edgard L. Riba" <elriba at rimith dot com> wrote in message > news:#RPKff7ELHA.5668(a)TK2MSFTNGP04.phx.gbl... >> Hi, >> >> I have a table like the following columns: >> >> Warehouse INTEGER NOT NULL, >> sku INTEGER NOT NULL, >> qtyOnHand DECIMAL(9,4), >> DateLastPurchase DATE, >> SupplierLastPurchase INTEGER >> >> Primary key is Warehouse,Sku >> >> It actually has more columns, but these serve for the purpose of an >> example. >> >> I want to select the LAST purchase date for each SKU. In other words, >> one row for each SKU, selecting the MAX(DateLastPurchase) >> >> So if the table has >> >> Warehouse Sku DateLastPuchase SupplierLastPurchase >> 1 1 '2010-01-15' 5 >> 2 1 '2010-01-12' 4 >> 3 1 '2010-01-10' 5 >> 1 2 '2010-01-10' 4 >> 2 2 '2010-01-10' 4 >> 3 2 '2010-01-12' 3 >> >> >> Would return >> Warehouse Sku DateLastPuchase SupplierLastPurchase >> 1 1 '2010-01-15' 5 >> 3 2 '2010-01-12' 3 >> >> >> In other words, what was the last purchase for each item irrespective of >> the >> warehouse. >> >> How would I do this on MSSQL 2000? >> >> >> Thanks, >> Edgard >> >> >> > > And what happens if you have 2 different SupplierLastPurchase values on > the same latest date? Which one do you want to see? Highest? Lowest? > Average? Sum? Something else? > > -- > Dan
From: Erland Sommarskog on 25 Jun 2010 08:19 Edgard L. Riba (elriba at rimith dot com) writes: > Thanks for looking into this. > > In this particular case it doesn't matter. I just need to select at > least one supplier on the last date. But why then bother at all? I always get an uneasy feeling when someone says "one, but it does not matter which". Anyway, this could do it. SELECT Warehouse, Sku, LastDate, (SELECT TOP 1 SupplierLastPurchase FROM tbl b WHERE b.Warehouse = a.Warehouse AND b.Sku = b.Sku AND b.Lastdate = b.Lastdate ORDER BY newid()) AS SupplierLastPurchase FROM (SELECT Warehouse, Sku, LastDate = MAX(DateLastPurcahse) FROM tbl GROUP BY Warehouse, Sku) AS d On SQL 2005/2008 you can use the row_number function which makes this a lot easier, but you are not there unfortunately. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: Mnipulating temp table output in sql Next: XML - Eliminate Namespace in Elements |