question

NonaMohammed-6597 avatar image
0 Votes"
NonaMohammed-6597 asked EchoLiu-msft edited

Write Query ----

how to write a query to get this result,, that shown in below---131211-55.png


sql-server-generalsql-server-transact-sql
55.png (23.0 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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!

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have executed ... but not get the same result that I want

0 Votes 0 ·

Maybe your different result is good too, because productNum is selected randomly.

0 Votes 0 ·
  sure....  but I want to choose one ProductNum randomly    ..... and thanks a lot 
0 Votes 0 ·
Show more comments
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @NonaMohammed-6597,

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:
131393-image.png


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.




image.png (4.8 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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


0 Votes 0 ·