NEWID() By Group

[RJL1]

Hello again,

I been trying to get my query to return a NEWID for each group in my results. Here is what I have so far

SELECT 
NEWID()M
WAREHOUSE,
ITEM,
SUM(QTY) AS 'QTY'
FROM INVETORY
GROUP BY
WAREHOUSE,
ITEM

This returns something like this

NEWID                                      WAREHOUSE      ITEM      QTY
D5974B88-AEAF-4079-94E4-888840DBD8E5       001            0004-M    120
D5974B88-AEAF-4079-94E4-888840DBD8E5       001            02457     83
D5974B88-AEAF-4079-94E4-888840DBD8E5       001            30996     24
D5974B88-AEAF-4079-94E4-888840DBD8E5       002            0004-M    520
D5974B88-AEAF-4079-94E4-888840DBD8E5       002            0187-M    33
D5974B88-AEAF-4079-94E4-888840DBD8E5       002            05524     183

i need a way to get a NEWID for each warehouse something like this

NEWID                                      WAREHOUSE      ITEM      QTY
D5974B88-AEAF-4079-94E4-888840DBD8E5       001            0004-M    120
D5974B88-AEAF-4079-94E4-888840DBD8E5       001            02457     83
D5974B88-AEAF-4079-94E4-888840DBD8E5       001            30996     24

50CC04A4-1C73-4F7A-99C2-70C826B30AEB       002            0004-M    520
50CC04A4-1C73-4F7A-99C2-70C826B30AEB       002            0187-M    33
50CC04A4-1C73-4F7A-99C2-70C826B30AEB       002            05524     183

Any assistance is appreciated
Thanks

 

[fredericofonseca]

which sql server version are you using?

on 2008R2 your code would work

create table x 
( id int
,note char(10)
)

insert into x values (1,'a')
insert into x values (2,'a')
insert into x values (1,'a')
insert into x values (2,'a')
insert into x values (2,'a')
insert into x values (2,'a')
insert into x values (1,'a')

select newid()
     , id
     , count(*)
from x
group by id

returns 2 id's, one per group

(No column name)	id	(No column name)
C9CFEFFF-01B6-4A8B-BBFE-127F55A36FB4	1	3
AA19713A-34A6-401B-B23E-3685F4183C6D	2	4

which matches your desired result

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

The question is really about your SQL Server Version.

Newid() should work different, it's a solution to the Problem RAND() only generates one random number per query, NEWID() instead generates a different value per row. Quite the inverse of what you see.

Using your code unchanged I get a newid per record, per item, not only per warehouse. That's a Problem, which doesn't even solve with Fredericos code.

The simplest solution would be defining a warehouse uniqueidentifier as column of your warehouse table and using it instead of int keys in the first place.

Bye, Olaf.

 

[fredericofonseca]

My previous code would not work (as Olaf pointed out) so here is a version that does work on 2008R2

drop table INVETORY
create table INVETORY 
( warehouse int
,item int
,note char(10)
)

insert into INVETORY values (1,1,'a')
insert into INVETORY values (2,1,'a')
insert into INVETORY values (1,2,'a')
insert into INVETORY values (2,2,'a')
insert into INVETORY values (2,3,'a')
insert into INVETORY values (2,3,'a')
insert into INVETORY values (1,1,'a')


;with t as
(select warehouse
      , item
      , count(*) cnt
 from INVETORY
 group by warehouse, item
)
,ids as
(select warehouse
      , newid() as newid
 from (select distinct warehouse from t) t
[highlight #8AE234] union 
 select null
       , newid()[/highlight]
)
select ids.newid
     , t.warehouse
     , t.item
     , t.cnt

from  t t
inner join ids ids
on t.warehouse = ids.warehouse
order by t.warehouse
       , t.item

gives following output

newid	warehouse	item	cnt
B9C07752-EA26-4E78-A608-03198CBF1B04	1	1	2
B9C07752-EA26-4E78-A608-03198CBF1B04	1	2	1
B8FAF164-F63A-4D53-9865-FC540F85E5B7	2	1	1
B8FAF164-F63A-4D53-9865-FC540F85E5B7	2	2	1
B8FAF164-F63A-4D53-9865-FC540F85E5B7	2	3	2

Note that the code in green is required so that that subquery is materialized


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bborissov]

DECLARE @Inventory TABLE (WAREHOUSE char(3),Item varchar(30), Qty int)
INSERT INTO @Inventory VALUES ('001', '0004-M', 120)
INSERT INTO @Inventory VALUES ('001', '02457', 83)
INSERT INTO @Inventory VALUES ('001', '30996', 24)
INSERT INTO @Inventory VALUES ('002', '0004-M', 520)
INSERT INTO @Inventory VALUES ('002', '0187-M', 33)
INSERT INTO @Inventory VALUES ('002', '05524', 183)


DECLARE @WhareHouse TABLE (IdWare UNIQUEIDENTIFIER DEFAULT NEWID(), WareHouse char(3))
INSERT INTO  @WhareHouse (WareHouse)
SELECT DISTINCT WareHouse FROM @Inventory 



SELECT Cte.IdWare AS M,
       Inv.WAREHOUSE,
       Inv.ITEM,
       SUM(Inv.QTY) AS 'QTY'
FROM @Inventory inv
INNER JOIN @WhareHouse Cte ON Inv.WAREHOUSE = Cte.WAREHOUSE
GROUP BY Inv.WAREHOUSE,Cte.IdWare,Inv.ITEM

But it is better to have separate Warehouse table where you should keep all warehouses with their Ids and names.


Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

I second Borislav and also recommended that.
Generating newids on the fly will mean you get new ids with every query, so you still would only have a temporary id anyway.

Bye, Olaf.

 

[Olaf Doschke]

>SELECT Cte.IdWare AS M,

Boris, I think you have this because of the original query
>SELECT
>NEWID()M
>WAREHOUSE,

But M simply is the key right beside [,] - I assume a typo. RJL1 surely simply meant: NEWID(),WAREHOUSE,..., no alias M.

Bye, Olaf.

 

[bborissov]

Yes it is

Borislav Borissov
VFP9 SP2, SQL Server