Select max within Select? - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Select max within Select?

Author

Topic

sql_dan
Starting Member

43 Posts

Posted - 2010-07-01 : 05:33:15

Hi there,

I have a table with that contains stock items. Each item has its own Stock_Code and Updated_Date.
Within the table there are multpile entries of the same stock code with a different free text description.

What I would like to do is only select the stock code with the latest updated date.

I've been playing with MAX and TOP 1 with selects in selects but all I get is one value for all rows.

Please help!!

Thanks
Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 05:43:32

[code]
Select
Max(Updated_Date),
Stock_Code from yourtable
group by Stock_Code
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-01 : 05:50:16

miniwave for idera ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 06:08:59

select * from
(
select row_number() over (partition by Stock_Code order by Updated_Date desc) as sno,* from table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

sql_dan
Starting Member

43 Posts

Posted - 2010-07-01 : 06:30:55

quote:
Originally posted by madhivanan

select * from
(
select row_number() over (partition by Stock_Code order by Updated_Date desc) as sno,* from table
) as t
where sno=1

Wow thanks Madhivanan works a treat!
Out of interest how does the partition by parameter work??

Thanks
Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 06:55:38

quote:
Originally posted by sql_dan

quote:
Originally posted by madhivanan

select * from
(
select row_number() over (partition by Stock_Code order by Updated_Date desc) as sno,* from table
) as t
where sno=1

Wow thanks Madhivanan works a treat!
Out of interest how does the partition by parameter work??

Thanks
Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

The row number will be generated for Stock_Code with descending order of the Updated_Date and is reset whenever Stock_code changes

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 06:59:43

quote:
Originally posted by madhivanan

select * from
(
select row_number() over (partition by Stock_Code order by Updated_Date desc) as sno,* from table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

Is there a need for using row_number because OP just wanted stock code with the latest updated date in his resultset.

quote:

What I would like to do is only select the stock code with the latest updated date.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-07-01 : 08:44:40

lol he replied that answer to my question too, just a day ago or so :D

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 09:54:25

quote:
Originally posted by doubleotwo

lol he replied that answer to my question too, just a day ago or so :D

If you mean it was me then please let me know where I did that.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 10:40:46

quote:
Originally posted by Idera

quote:
Originally posted by madhivanan

select * from
(
select row_number() over (partition by Stock_Code order by Updated_Date desc) as sno,* from table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

Is there a need for using row_number because OP just wanted stock code with the latest updated date in his resultset.
quote:

What I would like to do is only select the stock code with the latest updated date.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

It is just another answer which you can get the required columns you want

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-01 : 10:46:34

quote:
Originally posted by Idera

quote:
Originally posted by doubleotwo

lol he replied that answer to my question too, just a day ago or so :D

If you mean it was me then please let me know where I did that.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

He replied to me

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 13:04:56

quote:
Originally posted by madhivanan

quote:
Originally posted by Idera

quote:
Originally posted by doubleotwo

lol he replied that answer to my question too, just a day ago or so :D

If you mean it was me then please let me know where I did that.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

He replied to me

Madhivanan

Failing to plan is Planning to fail

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sql_dan
Starting Member

43 Posts

Posted - 2010-07-03 : 04:09:01

quote:
Originally posted by madhivanan

select * from
(
select row_number() over (partition by Stock_Code order by Updated_Date desc) as sno,* from table
) as t
where sno=1

Madhivanan

Hi Madhivnan,

I am stuck!!! I have used you code here wonderfully to pick out the newest items in the list but I have since discovered that there is a second table with prices also.

I have tried to amend the code to suit the needs but cant figure it out. I have tried UNIONS etc but I still get multiple entries of the same items based on Stock_Code.

The fields in the second table I would like to sort by are Stock_Code & Updated_Date again.

If you can help at all I would be so grateful, I feel like im going bald over this issue!!!!!!

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-05 : 03:04:00

Post the code you used

Madhivanan

Failing to plan is Planning to fail

sql_dan
Starting Member

43 Posts

Posted - 2010-07-05 : 03:38:50

quote:
Originally posted by madhivanan

Post the code you used

Hi Madhivanan,

I have tried so many different methods here without success, this is the latest version which again doesn't work but should give you an idea of what I am trying to do!

Basically - 2 tables same database one list of latest prices with unique stock codes!!!!

SELECT si.ID,
si.SupplierItemCode AS INT,
si.SupplierItemDescription,
Cost,
CASE
WHEN si.OrderingMeasureDescription LIKE '%[^0-9]%' THEN CAST(1 AS FLOAT)
ELSE CAST(si.OrderingMeasureDescription AS FLOAT)
END AS Measure
FROM (
SELECT ROW_NUMBER() OVER
(PARTITION BY SupplierItemCode ORDER BY CreatedDate DESC) AS sno,
si2.ID,
si2.SupplierItemCode,
si2.SupplierItemDescription,
si2.OrderingMeasureDescription,
si2.OrderingMeasureCode,
CAST(si2.PurchasePrice AS FLOAT) AS Cost,
CASE
WHEN si2.OrderingMeasureDescription LIKE '%[^0-9]%' THEN CAST(1 AS FLOAT)
ELSE CAST(si2.OrderingMeasureDescription AS FLOAT)
END AS Measure
FROM PowerGate.dbo.SupplierItems si2
UNION ALL
SELECT ROW_NUMBER() OVER
(PARTITION BY SupplierItemID ORDER BY ChangedDate DESC) AS sno,
lp2.ID,
lp2.SupplierItemID,
'','','',
CAST(lp2.ListPrice AS FLOAT) AS Cost,
'' AS Measure
FROM dbo.ListPrices lp2
) AS si
LEFT JOIN dbo.ListPrices AS lp ON lp.SupplierItemID = si.ID
WHERE sno=1
GROUP BY SupplierItemCode,SupplierItemDescription,si.ID,lp.ChangedDate,si.OrderingMeasureCode,si.OrderingMeasureDescription,si.Cost

Thanks so much for helping!!!

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 04:43:13

This?

SELECT	ID,
	SupplierItemCode,
	SupplierItemDescription,
	Cost,
	Measure
FROM	(
		SELECT		ROW_NUMBER() OVER (PARTITION BY si.SupplierItemCode ORDER BY si.CreatedDate DESC) AS sno,
				si.ID,
				si.SupplierItemCode,
				si.SupplierItemDescription,
				si.OrderingMeasureDescription,
				si.OrderingMeasureCode,
				CAST(ISNULL(lp.ListPrice, si.PurchasePrice) AS FLOAT) AS Cost,
				CASE
					WHEN si.OrderingMeasureDescription LIKE '%[^0-9]%' THEN CAST(1 AS FLOAT)
					ELSE CAST(si.OrderingMeasureDescription AS FLOAT)
				END AS Measure
		FROM		PowerGate.dbo.SupplierItems AS si
		LEFT JOIN	dbo.ListPrices AS lp ON lp.SupplierItemID = si.ID
	) AS d
WHERE	sno = 1

N 56°04'39.26"
E 12°55'05.63"

sql_dan
Starting Member

43 Posts

Posted - 2010-07-05 : 06:40:35

quote:

Originally posted by Peso

This?

SELECT	ID,
	SupplierItemCode,
	SupplierItemDescription,
	Cost,
	Measure
FROM	(
		SELECT		ROW_NUMBER() OVER (PARTITION BY si.SupplierItemCode ORDER BY si.CreatedDate DESC) AS sno,
				si.ID,
				si.SupplierItemCode,
				si.SupplierItemDescription,
				si.OrderingMeasureDescription,
				si.OrderingMeasureCode,
				CAST(ISNULL(lp.ListPrice, si.PurchasePrice) AS FLOAT) AS Cost,
				CASE
					WHEN si.OrderingMeasureDescription LIKE '%[^0-9]%' THEN CAST(1 AS FLOAT)
					ELSE CAST(si.OrderingMeasureDescription AS FLOAT)
				END AS Measure
		FROM		PowerGate.dbo.SupplierItems AS si
		LEFT JOIN	dbo.ListPrices AS lp ON lp.SupplierItemID = si.ID
	) AS d
WHERE	sno = 1

N 56°04'39.26"
E 12°55'05.63"

Hi Peso,

This is great, but is the code also picking out the lp.ChangedDate and picking the top one from the two tables???

When I did a quick de-dupe I had 72 duplicate stock codes!!!!

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-05 : 06:54:28

ORDER BY CASE WHEN lp.ChangedDate > si.CreatedDate THEN lp.ChangedDate ELSE si.CreatedDate END

N 56°04'39.26"
E 12°55'05.63"

sql_dan
Starting Member

43 Posts

Posted - 2010-07-05 : 07:23:18

quote:
Originally posted by Peso

ORDER BY CASE WHEN lp.ChangedDate > si.CreatedDate THEN lp.ChangedDate ELSE si.CreatedDate END

Thats super thanks Peso for your help on this!!!!

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

sql_dan
Starting Member

43 Posts

Posted - 2010-07-05 : 07:55:27

quote:
Originally posted by Peso

ORDER BY CASE WHEN lp.ChangedDate > si.CreatedDate THEN lp.ChangedDate ELSE si.CreatedDate END

N 56°04'39.26"
E 12°55'05.63"

Ok, newish issue I have another table to add in to this group with some more stock codes I have just been told about.

How would I go about this? It is in a separate database with similar fields, STKCODE, STKNAME, STKDATEEDITIED.

I apologies for all the tooing and frooing but I can not get my head around this one!

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

sql_dan
Starting Member

43 Posts

Posted - 2010-07-05 : 08:05:51

Mini breakthrough?????

Will this work?

quote:

Select DISTINCT SupplierItemCode,SupplierItemDescription,[Cost Per Item],[Data],[Product]
FROM (
SELECT SupplierItemCode,
SupplierItemDescription,
CASE WHEN
ROUND(Cost / NULLIF(CASE WHEN Measure = '0' THEN '1' ELSE Measure END, 0),2) IS NULL
THEN '0'
ELSE ROUND(Cost / NULLIF(CASE WHEN Measure = '0' THEN '1' ELSE Measure END, 0),2)
END AS [Cost Per Item],
'P' AS [Data],
'' AS [Product]
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY si.SupplierItemCode ORDER BY CASE WHEN lp.ChangedDate > si.CreatedDate THEN lp.ChangedDate ELSE si.CreatedDate END DESC) AS sno,
si.ID,
si.SupplierItemCode,
si.SupplierItemDescription,
si.OrderingMeasureDescription,
si.OrderingMeasureCode,
CAST(ISNULL(lp.ListPrice, si.PurchasePrice) AS FLOAT) AS Cost,
CASE WHEN si.OrderingMeasureDescription LIKE '%[^0-9]%' THEN
CASE WHEN LEFT(si.OrderingMeasureDescription,2) NOT LIKE '%[^0-9]%' THEN
LEFT(si.OrderingMeasureDescription,2) ELSE
CAST(1 AS FLOAT)
END
ELSE CAST(si.OrderingMeasureDescription AS FLOAT)
END AS [Measure]
FROM PowerGate.dbo.SupplierItems AS si
LEFT JOIN PowerGate.dbo.ListPrices AS lp ON lp.SupplierItemID = si.ID
) AS d
WHERE sno = 1 --and SupplierItemCode = @stk
UNION ALL
--INSERT INTO HMT_Theatre.dbo.Cons
SELECT DISTINCT STKCODE AS [Stock_Code],
STKNAME AS [Stock_Name],
STK_COSTPRICE,
'A' AS [Data],
STK_SORT_KEY1 AS [Product]
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY STKCODE ORDER BY STK_DATE_EDITED DESC) AS sno,
STKCODE,
STKNAME,
STK_COSTPRICE,
STK_SORT_KEY,
STK_SORT_KEY1
FROM StockDB.dbo.STK_STOCK
) AS e
--WHERE STKCODE = @stk
) AS f

Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources