March 11, 2008 at 5:37 pm
Hi All,
I have a table that contains purchase order lines where some lines relate to the same job number. I want to get the first record for each job number but I have got no idea how to return just the one record per job number. Some of the job numbers will only have one record, so I am not sure if this will make it any more difficult?
Unfortunately this data has been imported from another poorly written database so I don't have the luxury of parent and child tables which I think would make it easier.
Can anyone suggest how to retrieve one row per job number?
Cheers
ab
March 11, 2008 at 6:11 pm
Andy,
Post your table definition, with some sample data, and we can work this out.
Thanks.
March 11, 2008 at 6:17 pm
Select 'PurchaseOrder' As OH_DOC_TYPE,
SYSID As OH_DOC_NUM,
SYSID As OH_PO_NUM,
SUPPLIERNAME As OH_SUPP,
JOBNUMBER As OH_SRC_QJNUM,
TOTALCOST As OH_ORDER_VALUE,
Null As OH_ORDER_LINES
From JobOrd
Where SYSID >= 93175 and QUOTEREQUESTORDER = 'O'
Order by JobNumber
Sample Data would look something like:
PurchaseOrder9491194911Chilli Promotions 1510725.00NULL
PurchaseOrder9381193811Finsbury 151304180.00NULL
PurchaseOrder9381293812Finsbury 1513061588.00NULL
PurchaseOrder9318093180Finsbury 15138225.00NULL
PurchaseOrder9335293352Express Envelopes 15145271.60NULL
PurchaseOrder9335193351Express Envelopes 15147759.88NULL
PurchaseOrder9319193191Pageset 1514920.00NULL
PurchaseOrder9329093290Finsbury 15157705.00NULL
PurchaseOrder9329193291Print Bound Pty Ltd 15157516.00NULL
PurchaseOrder9333393333Print Bound Pty Ltd 15157488.00NULL
PurchaseOrder9336193361Finsbury 1515775.00NULL
PurchaseOrder9330193301Pageset 1515975.00NULL
March 11, 2008 at 6:59 pm
what do you mean by "first". first record encountered or first record when sorted by what column(s)?
the code below should get you started. it will only return the record with the first SYSID for any JOBNUMBER.
SELECT 'PurchaseOrder' As OH_DOC_TYPE,
SYSID As OH_DOC_NUM,
SYSID As OH_PO_NUM,
SUPPLIERNAME As OH_SUPP,
JOBNUMBER As OH_SRC_QJNUM,
TOTALCOST As OH_ORDER_VALUE,
Null As OH_ORDER_LINES
from ( SELECT SYSID, SUPPLIERNAME, JOBNUMBER, TOTALCOST,
ROW_NUMBER() OVER (partition by JOBNUMBER order by SYSID) as row_num
From JobOrd
Where SYSID >= 93175 and QUOTEREQUESTORDER = 'O') as X
where X.row_num = 1
March 11, 2008 at 7:02 pm
Is it true that the lower number sysids belong to earlier dates than higher number sysids so that 93291 Print Bound Pty Ltd is the row you want for job 15157?
March 11, 2008 at 7:08 pm
Based on the sample data, for job number 15157 I would return 'Finsbury' as the supplier - the lowest SYSID of 93290.
The 'First' record should always we the lowest SYSID for each job number.
March 11, 2008 at 8:34 pm
Based on the sample data and the description you provided. You should be able to mold your query like this.
declare @t table(
OH_DOC_TYPE varchar(25),
OH_DOC_NUM INT,
OH_PO_NUM INT,
OH_SUPP VARCHAR(25),
OH_SRC_QJNUM INT,
OH_ORDER_VALUE SMALLMONEY,
OH_ORDER_LINES VARCHAR(25)
)
INSERT INTO @t
SELECT 'PurchaseOrder',94911,94911,'Chilli Promotions',15107,25.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93811,93811,'Finsbury',15130,4180.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93812,93812,'Finsbury',15130,61588.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93180,93180,'Finsbury',15138,225.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93352,93352,'Express Envelopes',15145,271.60,NULL
UNION ALL
SELECT 'PurchaseOrder',93351,93351,'Express Envelopes',15147,759.88,NULL
UNION ALL
SELECT 'PurchaseOrder',93191,93191,'Pageset',15149,20.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93290,93290,'Finsbury',15157,705.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93291,93291,'Print Bound Pty Ltd',15157,516.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93333,93333,'Print Bound Pty Ltd',15157,488.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93361,93361,'Finsbury',15157,75.00,NULL
UNION ALL
SELECT 'PurchaseOrder',93301,93301,'Pageset',15159,75.00,NULL
SELECT a.OH_DOC_TYPE,
a.OH_DOC_NUM,
a.OH_PO_NUM,
a.OH_SUPP,
a.OH_SRC_QJNUM,
a.OH_ORDER_VALUE,
a.OH_ORDER_LINES
FROM @t a
INNER JOIN
(
SELECT OH_SRC_QJNUM,
MIN(OH_DOC_NUM) AS [FirstId]
FROM @t
Where OH_DOC_NUM >= 93175
GROUP BY OH_SRC_QJNUM
) AS b
ON a.OH_SRC_QJNUM = b.OH_SRC_QJNUM AND
a.OH_DOC_NUM = b.FirstId
WHERE a.OH_DOC_NUM >= 93175
ORDER BY a.OH_SRC_QJNUM
March 11, 2008 at 9:13 pm
Thanks. I've got just over 2000 rows to work through, so I'll try doing this generating this in Excel and pasting it back to sql and see how it goes.
ab
March 11, 2008 at 9:39 pm
Okay. Keep us posted.
March 12, 2008 at 5:53 am
Another variation on what everyone else has done would be this:
SELECT a.OH_DOC_TYPE,
a.OH_DOC_NUM,
a.OH_PO_NUM,
a.OH_SUPP,
a.OH_SRC_QJNUM,
a.OH_ORDER_VALUE,
a.OH_ORDER_LINES
FROM @t a
INNER JOIN
(
SELECT TOP (1) OH_SRC_QJNUM,
OH_DOC_NUM
FROM @t
Where OH_DOC_NUM >= 93175
ORDER BY OH_SRC_QJNUM
) AS b
ON a.OH_SRC_QJNUM = b.OH_SRC_QJNUM AND
a.OH_DOC_NUM = b.OH_DOC_NUM
WHERE a.OH_DOC_NUM >= 93175
ORDER BY a.OH_SRC_QJNUM
Depending on the indexes, we found that an ORDER BY and TOP worked better than MIN or MAX with a GROUP BY. Although, as usual, your mileage may vary.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply