Write Query ----

Question

Write Query ----

Nona Mohammed 41

how to write a query to get this result,, that shown in below---

EchoLiu-MSFT 14,616 Reputation points

2021-09-14T02:01:34.17+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

2 answers

Your answer

EchoLiu-MSFT 14,616 Reputation points

2021-09-14T02:01:34.17+00:00

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Answer 1

Viorel 121.2K

Try a known method of getting random rows:

select 
    pbt.ProductID, 
    ( select top(1) ProductNum from BcodsTable where ProductID = pbt.ProductID order by newid()) as productNum,
    pt.Name, 
    pt.Describe, 
    pbt.Quantity, 
    pbt.LOWQ
from ProductTable pt
inner join ProdBcodeTable pbt on pbt.ProductID = pt.ID

Nona Mohammed 41 Reputation points

2021-09-10T18:32:52.883+00:00

I have executed ... but not get the same result that I want
Viorel 121.2K Reputation points

2021-09-10T18:35:10.223+00:00

Maybe your different result is good too, because productNum is selected randomly.
Nona Mohammed 41 Reputation points

2021-09-10T18:51:42.827+00:00
sure.... but I want to choose one ProductNum randomly ..... and thanks a lot
Viorel 121.2K Reputation points

2021-09-14T07:57:25.087+00:00

The previous query gets the ProductNum randomly, so that the result does not always correspond to the picture. Do you mean something else?

Answer 2

EchoLiu-MSFT 14,616

Hi @Nona Mohammed ,

Please also check:

CREATE TABLE #Bcodstable(ID int,Productid int,ProductNum int)  
INSERT INTO #Bcodstable VALUES(1,12,120),(2,12,122),(3,12,150)  
,(1005,16,250),(1006,16,270),(1007,16,280)  
  
CREATE TABLE #Producttable(ID int,[Name] VARCHAR(25),[Describe] VARCHAR(25))  
INSERT INTO #Producttable VALUES(12,'water','pure'),  
(16,'CocaCola','BC')  
  
CREATE TABLE #Prodbcodetable(Productid int,Quantity int,LOWQ int)  
INSERT INTO #Prodbcodetable VALUES(12,350,50),(16,450,85)  
  
SELECT DISTINCT b.Productid,MIN(b.ProductNum)   
OVER(PARTITION BY b.Productid) ProductNum  
,p.[Name],p.[Describe]  
,pc.Quantity,pc.LOWQ  
FROM #Bcodstable b  
JOIN #Producttable p ON b.Productid=p.ID  
JOIN #Prodbcodetable pc ON b.Productid=pc.Productid

Output:

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Nona Mohammed 41 Reputation points

2021-09-16T06:16:53.507+00:00

Hi, thank you for this answer... it was correct if I need min (prouductNum) ...
but I want to use random selection to select (prouductNum)

EchoLiu-MSFT 14,616

Use the TOP statement without ORDER BY to return the random result you want, and the TOP statement will return the value randomly accessed by SQL Server.
Please try:

;WITH cte
as(SELECT b.Productid,b.ProductNum
 ,p.[Name],p.[Describe]
 ,pc.Quantity,pc.LOWQ
 FROM #Bcodstable b
 JOIN #Producttable p ON b.Productid=p.ID
 JOIN #Prodbcodetable pc ON b.Productid=pc.Productid)

 SELECT TOP(1)*
 FROM cte
 WHERE Productid=12
 UNION ALL
 SELECT TOP(1)*
 FROM cte
 WHERE Productid=16

Share via

Write Query ----

2 answers

Your answer