Select 4 Distinct Suppliers for each stock number in Single Query

Question

Post reply

Select 4 Distinct Suppliers for each stock number in Single Query

waheed71

Old Hand

Points: 362
More actions
January 14, 2014 at 3:32 am

#281099

Hello, I am trying to filter results based on stock numbers and intend to display minimum 3-4 suppliers for against each stock number. Below are two queries and there outputs. Seeking advice to improve query.
Query-1
Select (Select Top 4 GR_SUPPLIERCODE) as Supplier,GL_GRN_NO,GL_LOCALPOLICYNO,CT_STOCKNO_P,CT_STOCKSUMMARY,CT_CLASSCODE,CT_GROUPCODE,CT_QUANTITY,PY_AVERAGEUNITCOST,(CT_QUANTITY*PY_AVERAGEUNITCOST) AS TOTAL
From ST_COMMODITYTYPE
Inner Join ST_LOCALPOLICY ON ST_LOCALPOLICY.PY_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P
Inner Join PO_GRN_LINE ON PO_GRN_LINE.GL_LOCALPOLICYNO=ST_LOCALPOLICY.PY_LOCALPOLICYNO
Inner Join PO_GRN ON PO_GRN_LINE.GL_GRN_NO=PO_GRN.GR_GRN_NO_P
Order by CT_STOCKNO_P
Query-2
Select Top 4 GR_SUPPLIERCODE,GL_GRN_NO,GL_LOCALPOLICYNO,CT_STOCKNO_P,CT_STOCKSUMMARY,CT_CLASSCODE,CT_GROUPCODE,CT_QUANTITY,PY_AVERAGEUNITCOST,(CT_QUANTITY*PY_AVERAGEUNITCOST) AS TOTAL
From ST_COMMODITYTYPE
Left Join ST_LOCALPOLICY ON ST_LOCALPOLICY.PY_STOCKNO=ST_COMMODITYTYPE.CT_STOCKNO_P
Left Join PO_GRN_LINE ON PO_GRN_LINE.GL_LOCALPOLICYNO=ST_LOCALPOLICY.PY_LOCALPOLICYNO
Left Join PO_GRN ON PO_GRN_LINE.GL_GRN_NO=PO_GRN.GR_GRN_NO_P
Using Query-2 I am able to get results but it is not providing for all stock numbers and not distinct.
Appreciate your efforts in advance!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

probably the easiest way is to stick the query into a subquery, featuring row number, and then filter the results.

SELECT

*

FROM (SELECT

ROW_NUMBER()

OVER(

PARTITION BY GR_SUPPLIERCODE,CT_STOCKNO_P

ORDER BY GR_SUPPLIERCODE,CT_STOCKNO_P) AS RW,

GR_SUPPLIERCODE,

GL_GRN_NO,

GL_LOCALPOLICYNO,

CT_STOCKNO_P,

CT_STOCKSUMMARY,

CT_CLASSCODE,

CT_GROUPCODE,

CT_QUANTITY,

PY_AVERAGEUNITCOST,

( CT_QUANTITY * PY_AVERAGEUNITCOST ) AS TOTAL

FROM ST_COMMODITYTYPE

LEFT JOIN ST_LOCALPOLICY

ON ST_LOCALPOLICY.PY_STOCKNO = ST_COMMODITYTYPE.CT_STOCKNO_P

LEFT JOIN PO_GRN_LINE

ON PO_GRN_LINE.GL_LOCALPOLICYNO = ST_LOCALPOLICY.PY_LOCALPOLICYNO

LEFT JOIN PO_GRN

ON PO_GRN_LINE.GL_GRN_NO = PO_GRN.GR_GRN_NO_P

)MySubQuery

WHERE RW = 4

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

waheed71 Old Hand Points: 362 More actions · Answer 2

Dear Lowell,

Thank you so much. This gives me good idea and after changing some Parameters, I am able to extract exact report.

Once again, Thank you!

Lowell SSC Guru Points: 323518 More actions · Answer 3

glad i could help a little bit!

let us know how it goes or if oyu need another pair of eyes to look at the issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!