how to write a query to get this result,, that shown in below---
how to write a query to get this result,, that shown in below---
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!
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
Maybe your different result is good too, because productNum is selected randomly.
sure.... but I want to choose one ProductNum randomly ..... and thanks a lot
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.
Hi, thank you for this answer... it was correct if I need min (prouductNum) ...
but I want to use random selection to select (prouductNum)
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
14 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index